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

There is a procedure in the dbms_sql-package which is called dbms_sql.describe_columns. You can use this procedure to get information about the select-list eg. number of columns and their datatype. Use the datatype information to decide which dbms_sql.define_column you have to use.

Hope you can use that - good luck

"Allen Gibbons" <agibbons_at_ec.rr.com> wrote:
>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
> )
>IS
>
> 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 Wed Mar 05 2003 - 22:44:24 CET