CODEWITHSUNDEEP
sqloracle-databaseplsql
sameh.q
sameh.q

Reputation: 1709

Dynamic Columns Count

Simple question:

I have a cursor that runs against a select statement provided in the parameter of a stored procedure, so the number of columns is not known.

The question is How can I print the columns ?

Example Code:

CREATE OR REPLACE Procedure MyExampleProcedure(P_QUERY VARCHAR2, P_dest VARCHAR2, P_file VARCHAR2)
AS
    --Declaring types to use it for variables declarations
    -----------------------------------------------
    TYPE cursor_ref IS REF CURSOR;
    TYPE typ_new_code IS TABLE OF VARCHAR2(50);
    TYPE typ_new_desc IS TABLE OF VARCHAR2(500);

    --Declaring variables based on previous types
    -----------------------------------------------
    c1 cursor_ref;
    new_code typ_new_code;
    new_desc typ_new_desc;

    -----------------------------------------------
    V_DEST_FILE   utl_file.file_type;
BEGIN

      V_DEST_FILE := utl_file.fopen(P_dest, P_file,'W');

      OPEN c1 for P_QUERY;

      FETCH C1 BULK COLLECT INTO new_code,new_desc;--This is the problematic area, not being able to iterate over the columns in the fetched row dynamically !
      FOR I IN new_code.first .. new_code.last LOOP

        utl_file.put(V_DEST_FILE, new_desc(i));

      END LOOP;

      utl_file.fclose(V_DEST_FILE);


EXCEPTION
      WHEN NO_DATA_FOUND THEN
                --Whatever
      WHEN OTHERS THEN
                --Whatever    
END;

As you can see, this will work only for select statements with two columns, code and desc regardless of the names

Upvotes: 0

Views: 676

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

Just for the sport, it can be done-

before opening the cursor add:

dbms_output.put_line(dbms_xmlgen.getXMLType(P_QUERY)
                       .extract('ROWSET/ROW/*[1]').getrootelement());

dbms_output.put_line(dbms_xmlgen.getXMLType(P_QUERY)
                       .extract('ROWSET/ROW/*[2]').getrootelement());

(I used dbms_output.put_line but you can also use utl_file)

Note that this means that you're running the whole query just for the columns names so this might not be the prefered solution...

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

Given that code and given that you're using 11g, you can't get the column names in the underlying result set. If you want to do that, you would probably need to use the DBMS_SQL package to open the cursor, describe the results, and fetch the data. I suppose you could also write a Java stored procedure and use the JDBC API to describe the query.

Assuming your intention is to produce some sort of file based on a SQL statement, I'd strongly suggest leveraging Tom Kyte's dump_csv procedure rather than trying to build your own.

Upvotes: 2

Related Questions