SandPiper SandPiper - 3 months ago 7
SQL Question

How to Refer to a Column by ID or Index Number

In Oracle PL/SQL, I have run a query and am trying to read through each column for each row one by one so I can concatenate them together with a delimiter (hard format requirement). The script is used on multiple tables of varying sizes, so the number of columns is not known in advance. I used

SELECT COUNT(column_name) INTO NumColumns FROM all_tabs_cols
WHERE table_name = Table_Array(i);


where Table_Array has already been defined. This is in the middle of a for loop and has successfully gotten me a total number of columns. Table_Cursor is a SELECT * statement. After this I am trying to do something like

FOR j IN 0..NumColumns-1 LOOP
FETCH TABLE_CURSOR.column(j) INTO DataValue;
DBMS_OUTPUT.PUT(DataValue || '/');
END LOOP


The above is pseudo code. It illustrates the concept I am after. I do not know PL/SQL well enough to know how to get a value like this out of a row. I am also worried about accidentally advancing the cursor while doing this. How can I accomplish this task?

Answer

You must use some form of dynamic SQL. Here is a quick example:

It builds the SQL statement that will select the '/' separated columns from the table you want. Then it uses dynamic SQL to run that SQL statement.

DECLARE
  p_table_name VARCHAR2(30) := 'DBA_OBJECTS';
  l_sql VARCHAR2(32000);

  TYPE varchar2tab IS TABLE OF VARCHAR2(32000);
  l_array varchar2tab;

BEGIN
  SELECT 'SELECT ' || listagg(column_name,' ||''/''||') within group ( order by column_id ) || ' FROM ' || owner || '.' || table_name || ' WHERE ROWNUM <= 100'
  INTO l_sql
  FROM dba_tab_columns
  where table_Name = 'DBA_OBJECTS'
  group by owner, table_Name;

  EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_array;

  FOR i in l_array.first .. l_array.last LOOP
    dbms_output.put_line(l_array(i));
  END LOOP;
END;
Comments