0

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

  • I think you'll need to use dbms_sql.describe_columns to get the query column names, but it is hard to tell without details on what/how piece_of_query_with_dynamic_columns.is constructed – Gary Apr 11 '18 at 6:16
  • Have a look at this: stackoverflow.com/questions/26305406/… – Wernfried Domscheit Apr 11 '18 at 8:52
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.