CODEWITHSUNDEEP
oracle-databaseplsqldbms-output
Omkar Shinde
Omkar Shinde

Reputation: 13

Put Column Name in Variable and use it in output statement

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

Upvotes: 1

Views: 1786

Answers (1)

Alex Poole
Alex Poole

Reputation: 191520

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.

Upvotes: 4

Related Questions