Error PLS-00307: too many declarations of 'DEFINE_COLUMN' match this call when executing DBMS_SQL.DEFINE_COLUMN

Question

I am currently migrating some SQL SERVER T-SQL SPs to Oracle and need assistance;

Current T-SQL process

1. In an SP, create a dynamic SQL statement and execute that statement at the end of the SP, thus returning the entire result set from the dynamic SQL.
2. In a main SP, call the above SP with a dynamic BCP EXEC statement, outputting the result set from the first SP to a .CSV

My approach in Oracle, was to try to port the code as close to T-SQL as possible. Note: This has a very limited lifetime and just needs to run a couple times as the data is being migrated to MS SQL via these .CSVs.

1. I created a SP with a OUT PARM of type sys_refcursor
2. I dynamically build the query and open a cursor to it assigning the cursor to my sys_refcursor PARM above
3. In a anonymous block script (might turn into an SP later, but really don't see a reason to), I execute the SP above
4. I had hoped to call UTL_FILE.PUT_LINE with a dynamically built second parameter, apparently that doesn't work. This would have specified all the columns from the cursor.
5. Since this didn't work, I found an example online of using the DBMS_SQL package to;
6. Call DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
7. Based on the COL_TYPE for example varchar2, DBMS_SQL.DEFINE_COLUMN(curid, i, varcharvar)
8. However, it appears I would need to have individual varcharvar variables for every varchar2 column in my cursor, which doesn't sound very dynamic? The second time it tries to DEFINE_COLUMN varcharvar I get the "Error PLS-00307: too many declarations of 'DEFINE_COLUMN' match this call when executing DBMS_SQL.DEFINE_COLUMN"

I just want to consume the ref_cursor, not knowing what columns are in it, and for each row, UTL_FILE.PUT_LINE(v_file,col1||','||col2||','||...) the row to a text file.

I do not work enough anymore in Oracle to know the best way to do this, any direction greatly appreciated.

Answer 1

Final SP solution was;

--**********************************************************************************************
-- Stored Procedure: spCreateCSV                                                           
-- Parms           : p_sql          varchar2    Valid SQL statement that creates the CSV
--                 : p_dir          varchar2    Valid Oracle DB directory name, PWSTAGING
--                 : p_data_file    varchar2    Valid csv file name, e.g. TABLE.CSV
-- Purpose         : Given a valid SQL statement, create a dynamic cursor and loop through all values of the cursor generating an output row.
--                   Write this row to a file on the given Oracle DB directory in the given file name.
--                   Format of the CSV is unique for the requirements of the MS SQL Import scripts
--                                                                                              
--   Date      Job                     Emp  Comments                                            
-- ********  ************************  ***  ****************************************************
-- 07/09/21  [BEHHLTH-29235|10348212]  KH   Initial development
-- 08/10/21  [BEHHLTH-30300|10348212]  KH   Add CLOB data type, reinit variables in loop due to transient issue
--**********************************************************************************************

CREATE OR REPLACE PROCEDURE spCreateCSV (
    p_sql         IN   VARCHAR2,
    p_dir         IN   VARCHAR2,
    p_data_file   IN   VARCHAR2 := NULL
) AS

    v_finaltxt    VARCHAR2(32767);
    v_v_val       VARCHAR2(32767);
    v_n_val       NUMBER;
    v_d_val       DATE;
    v_c_val       CLOB;
    v_ret         NUMBER;
    c             NUMBER;
    d             NUMBER;
    col_cnt       INTEGER;
    rec_tab       dbms_sql.desc_tab;
    col_num       NUMBER;
    v_csv         utl_file.file_type;
    v_col_delim   VARCHAR2(10) := '!@!';
    v_row_delim   VARCHAR2(10) := '!!';
    v_delim       VARCHAR2(10);
    v_quote       VARCHAR2(1) := '"';
BEGIN

--*********************************************************************************************
-- Stored Procedure: spCreateCSV
-- Parms:   p_sql - valid sql statement
--          p_dir - Oracle database directory 
--          p_data_file - filename for .csv
-- Purpose:
-- Given a valid SQL statement, create a dynamic cursor and fetch through the cursor into
-- a .csv file written to the oracle directory, root dir is \\ipcbhcs01\systems$
--*********************************************************************************************

--*********************************************************************************************
-- Create a dynamic cursor using the passed in SQL statement
--*********************************************************************************************
    c := dbms_sql.open_cursor;                                  
    dbms_sql.parse(c, p_sql, dbms_sql.native);
    d := dbms_sql.execute(c);
--*********************************************************************************************
-- For each column in the cursor, create a variable that matches the data_type of the column
-- See: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_sql.htm
--when l_desc(i).col_type = 1 then 'VARCHAR2'
--when l_desc(i).col_type = 2 then 'NUMBER'
--when l_desc(i).col_type = 8 then 'LONG'
--when l_desc(i).col_type =  9 then 'VARCHAR'
--when l_desc(i).col_type =  12 then 'DATE'
--when l_desc(i).col_type =  23 then 'RAW'
--when l_desc(i).col_type =  69 then 'ROWID'
--when l_desc(i).col_type =  96 then 'CHAR'
--when l_desc(i).col_type =  100 then 'BINARY_FLOAT'
--when l_desc(i).col_type =  101 then 'BINARY_DOUBLE'
--when l_desc(i).col_type =  105 then 'MLSLABEL'
--when l_desc(i).col_type =  106 then 'MLSLABEL'
--when l_desc(i).col_type =  112 then  'CLOB'
--when l_desc(i).col_type =  113 then 'BLOB'
--when l_desc(i).col_type =  114 then 'BFILE'
--when l_desc(i).col_type =  115 then 'CFILE'
--when l_desc(i).col_type =  178 then 'TIME'
--when l_desc(i).col_type =  179 then 'TIME WITH TIME ZONE'
--when l_desc(i).col_type =  180 then 'TIMESTAMP'
--when l_desc(i).col_type =  181 then 'TIMESTAMP WITH TIME ZONE'
--when l_desc(i).col_type =  231 then 'TIMESTAMP WITH LOCAL TIME ZONE'
--when l_desc(i).col_type =  182 then 'INTERVAL YEAR TO MONTH'
--when l_desc(i).col_type =  183 then 'INTERVAL DAY TO SECOND'
--*********************************************************************************************    
    dbms_sql.describe_columns(c, col_cnt, rec_tab);
    FOR j IN 1..col_cnt LOOP CASE rec_tab(j).col_type
        WHEN 1 THEN
            dbms_sql.define_column(c, j, v_v_val, 4000);
        WHEN 2 THEN
            dbms_sql.define_column(c, j, v_n_val);
        WHEN 12 THEN
            dbms_sql.define_column(c, j, v_d_val);
        WHEN 112 THEN --clob
            dbms_sql.define_column(c, j, v_c_val); 
        ELSE
            dbms_sql.define_column(c, j, v_v_val, 4000);
    END CASE;
    --DBMS_OUTPUT.PUT_LINE('DATATYPE:'||TO_CHAR(rec_tab(j).col_type));
    END LOOP;

    v_csv := utl_file.fopen(upper(p_dir), p_data_file, 'w', 32767);

--*********************************************************************************************
-- Loop thru cursor and build the v_finaltxt string based on the data_type
--*********************************************************************************************
    LOOP
        v_v_val := null;
        v_n_val := null;
        v_d_val := null;
        v_c_val := null; 
        v_ret := dbms_sql.fetch_rows(c);
        EXIT WHEN v_ret = 0;
        v_finaltxt := v_row_delim; --Start with the row delim
        FOR j IN 1..col_cnt LOOP
            IF j = col_cnt THEN  --if the last column, set the delim to row else col
                v_delim := v_row_delim;
            ELSE
                v_delim := v_col_delim;
            END IF;

            CASE rec_tab(j).col_type
                WHEN 1 THEN
                    dbms_sql.column_value(c, j, v_v_val);
                    v_finaltxt := ltrim(v_finaltxt
                                        || v_quote
                                        || v_v_val
                                        || v_quote
                                        || v_delim);

                WHEN 2 THEN
                    dbms_sql.column_value(c, j, v_n_val);
                    v_finaltxt := ltrim(v_finaltxt
                                        || v_quote
                                        || v_n_val
                                        || v_quote
                                        || v_delim);

                WHEN 12 THEN
                    dbms_sql.column_value(c, j, v_d_val);
                    v_finaltxt := ltrim(v_finaltxt
                                        || v_quote
                                        || to_char(v_d_val, 'YYYY/MM/DD HH24:MI:SS')
                                        || v_quote
                                        || v_delim);
                WHEN 112 THEN
                    dbms_sql.column_value(c, j, v_c_val);
                    v_finaltxt := ltrim(v_finaltxt
                                        || v_quote
                                        || v_c_val
                                        || v_quote
                                        || v_delim);                                        

                ELSE
                    dbms_sql.column_value(c, j, v_v_val);
                    v_finaltxt := ltrim(v_finaltxt
                                        || v_quote
                                        || v_v_val
                                        || v_quote
                                        || v_delim);

            END CASE;
        END LOOP;
--*********************************************************************************************
-- Output the record to the csv
--*********************************************************************************************    
  --    DBMS_OUTPUT.PUT_LINE(v_finaltxt);
        utl_file.put_line(v_csv, v_finaltxt);
    END LOOP;

--*********************************************************************************************
-- Close the file, close the cursor
--*********************************************************************************************    
    utl_file.fclose(v_csv);
    dbms_sql.close_cursor(c);
END;

--*********************************************************************************************
-- Example call to run SP directly
--*********************************************************************************************    
--exec spCreateCSV('select * from AZACKNLG','PWSTAGING','AZACKNLG.csv');