Set columns dinamically in UTL_FILE

Question

I've been stuck with this problem, so I'm looking for help now.

I'm trying to get a .cvs file from a query with PL/SQL. I'm using a cursor and UTL_FILE to achieve that. The problem is that I have to pass all the column names to UTL_FILE.PUT_LINE(), and there are some columns that are set dynamically, so I don't know how to pass those columns to UTL_FILE.

I tried to build a string with those columns, but it doesn't work. Example:

    FOR pointer IN (SELECT column1, column2, **piece_of_query_with_dynamic_columns**, column3 FROM table1)

    LOOP
      UTL_FILE.PUT_LINE(v_file, pointer.column1|| ',' ||pointer.column2|| ',' ||**string_with_dynamic_columns**|| ',' ||pointer.column3);
    END LOOP; 
 UTL_FILE.FCLOSE(v_file);

The query works well and, except for those dynamic columns, I can get the .cvs file.

Any suggestions?

I hope i explained myself well

Answer 1

It would be something like this:

DECLARE
    cur SYS_FERCURSOR;
    curid NUMBER;
    desctab DBMS_SQL.DESC_TAB;
    colcnt NUMBER; -- total number of columns
    res NUMBER;

    namevar VARCHAR2(4000);
    numvar NUMBER;
    datevar DATE;
    ... more IF NEEDED
BEGIN

    OPEN cur FOR 'SELECT column1, column2, **piece_of_query_with_dynamic_columns**, column3 FROM table1';               
    curid := DBMS_SQL.TO_CURSOR_NUMBER (cur);

    DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
    FOR i IN 1..colcnt LOOP -- loop over all columns
        IF desctab(i).col_type = 1 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, namevar);
        ELSIF desctab(i).col_type = 2 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
        ELSIF desctab(i).col_type = 12 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
        ELSIF desctab(i).col_type = ... THEN            
            .......
        END IF;         
    END LOOP;
    -- Fetch Rows
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
        FOR i IN 1 .. colcnt LOOP
           IF (desctab(i).col_type = 1) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
              UTL_FILE.PUT(v_file, namevar|| ',');
           ELSIF (desctab(i).col_type = 2) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
              UTL_FILE.PUT(v_file, numvar|| ',');
           ELSIF (desctab(i).col_type = 12) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
              UTL_FILE.PUT(v_file, datevar|| ',');
           ELSIF ....
              ...
           END IF;          
       END LOOP;
       UTL_FILE.NEW_LINE;
    END LOOP;    
    DBMS_SQL.CLOSE_CURSOR(curid);

END;

In order to trim the last comma you may use

IF i < colcnt THEN
    UTL_FILE.PUT(v_file, namevar|| ',');
ELSE
    UTL_FILE.PUT(v_file, namevar);
END IF;