Put Column Name in Variable and use it in output statement

Question

Here is What i actually wanted to do, Fetch Data From a Table without knowing any columns but i.j.Column_Name gives an error, so i wanted to put it in a variable. After reading the comments i think it's not possible

  DECLARE
     CURSOR C1 IS SELECT * FROM Table_Name;
     CURSOR C2 IS SELECT Table_Name,Column_Name FROM  user_tab_columns 
                   WHERE data_type='VARCHAR2';
     v_table Varchar2(256);
     v_Col varchar2(200);
  BEGIN
      FOR i in C1 LOOP
        FOR j in (SELECT Column_Name FROM user_tab_columns WHERE 
                  Table_Name='Table_Name') LOOP
             dbms_output.put_line(i.j.Column_Name);
    END LOOP;
  END LOOP;
 END;
/

No, There is no Column Named v_Col

It would help if you explained the *requirement*. What is v_Col exactly? A column name? What do you need it for; all columns from the Table_Name are in "i" cursor variable? Shortly, I don't understand what you are trying to do. — Littlefoot, Aug 23 '18 at 09:42

Alex Poole · Accepted Answer · 2018-08-23T10:03:19.383

You can't refer to a field in a record (which is what the cursor loop is giving you) dynamically. If you need to do flexibly then you can use dbms_sql (possibly adapting this approach), but in the scenario you've shown you could use dynamic SQl to only get the column you want in the cursor:

-- dummy data
create table table_name (id number, column_name varchar2(10), other_col date);
insert into table_name values (1, 'Test 1', sysdate);
insert into table_name values (2, 'Test 2', sysdate);

DECLARE
    CURSOR C1 IS SELECT * FROM Table_Name;
    v_Cur sys_refcursor;
    v_Col varchar2(200);
    v_Val varchar2(4000);
BEGIN
    v_Col:= 'Column_Name';
    OPEN v_Cur for 'SELECT ' || v_Col || ' FROM Table_Name';
    LOOP
        FETCH v_Cur INTO v_Val;
        EXIT WHEN v_Cur%notfound;
        dbms_output.put_line(v_val);
    END LOOP;
END;
/

Test 1
Test 2

PL/SQL procedure successfully completed.

The downside of this is that whatever the data type of the target column is, you have to implicitly convert it to a string; but you would be doing that in the dbms_output call anyway. So if you change the column you want to print:

    v_Col:= 'Other_Col';

then the output from my dummy data would be:

2018-08-23
2018-08-23

PL/SQL procedure successfully completed.

where the date value is being implicitly formatted as a string using my current NLS session settings.

You could get more advanced by checking the data type in user_tab_columns and changing the dynamic query and/or the fetch and handling, but it isn't clear what you really need to do.

Put Column Name in Variable and use it in output statement

1 Answers1