CODEWITHSUNDEEP
sqloracle-database
SandPiper
SandPiper

Reputation: 2906

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?

Upvotes: 1

Views: 759

Answers (2)

Hogan
Hogan

Reputation: 70513

This is how your code should look:

SELECT F1 || ', ' || F2 || ', ' || ... || ', ' || FN
FROM TABLE

NO LOOPS

Here is how you can generate code that does not use loops. Note, if you want you can take out the where statement and generate the code for the whole database. Test with just one table first.

SELECT 'SELECT '|| LISTAGG(COLUMN_NAME, ' || '', '' || ') || ' FROM '||TABLE_NAME as sql_stm
FROM ALL_TAB_COLUMNS 
WHERE TABLE_NAME='tablename'
GROUP BY TABLE_NAME;

Upvotes: 0

Matthew McPeak
Matthew McPeak

Reputation: 17924

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;

Upvotes: 2

Related Questions