Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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);
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