URGENT: Dynamically setting DBMS_SQL.DEFINE_COLUMN when column type unknown?

Help!

I have the following dynamic pl/sql procedure which takes several arguments and is run from within a Form when-button-pressed trigger. It opens the cursor fine, but I get an error as soon as I add in the line:

DBMS_SQL.COLUMN_VALUE(v_cursor_id, 1, v_data);

Seems like the COLUMN_VALUE doesn't match the DEFINE_COLUMN value.

So basically, how can I dynamically set the definition of the column? DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 1, v_data, 1000);

Any help would be much appreciated.

PROCEDURE data_value

            (
             p_owner            IN VARCHAR2 DEFAULT NULL,
             p_table_name       IN VARCHAR2 DEFAULT NULL,
             p_column_name      IN VARCHAR2 DEFAULT NULL,
             p_select_statement IN VARCHAR2 DEFAULT NULL,
             p_write_append     IN VARCHAR2 DEFAULT NULL
            )

 out_file_1 TEXT_IO.FILE_TYPE;

 v_cursor_id      INTEGER;
 v_data           VARCHAR2(1000) := NULL;
 v_dummy          INTEGER;
 v_loop           NUMBER := 0;

BEGIN  out_file_1 := TEXT_IO.FOPEN('D:\file_io\export.csv', p_write_append);

 TEXT_IO.PUT(out_file_1, p_column_name);  TEXT_IO.NEW_LINE(out_file_1);

 v_cursor_id := DBMS_SQL.OPEN_CURSOR;

 DBMS_SQL.PARSE(v_cursor_id, p_select_statement, 2);

• how can I dynamically set the definition of the column? DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 1, v_data, 1000);

 v_dummy := DBMS_SQL.EXECUTE(v_cursor_id);

  LOOP
   IF DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0 THEN     EXIT;
   END IF;   DBMS_SQL.COLUMN_VALUE(v_cursor_id, 1, v_data);
-- PROBLEM HERE ?
   v_loop := v_loop + 1;

  END LOOP;    TEXT_IO.PUT(out_file_1, 'Number of records is :'||v_loop);    TEXT_IO.NEW_LINE(out_file_1);

 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

 TEXT_IO.FCLOSE(out_file_1);

END; Received on Mon Mar 03 2003 - 22:45:16 CST