DBMS_SQL.DEFINE_COLUMN problem

[Dyfed]

I am using a dynamic SQL cursor to perform a SELECT where table_name is
being passed as a parameter.

When I use DBMS_SQL.DEFINE_COLUMN for the return value when I am only
selecting one column, it works fine. BUT when I use it to select 2
columns(see below) I get an error message as follows:

DECLARE /*** MAIN DECLARE SECTION ***/
*
ERROR at line 1:
ORA-06550: line 34, column 5:
PLS-00307: too many declarations of 'DEFINE_COLUMN' match this call
ORA-06550: line 34, column 5:
PL/SQL: Statement ignored

****************************************************************************
*************
(Extract of Code....)

v_cursor := DBMS_SQL.OPEN_CURSOR;
v_select := 'SELECT tb.flow_set_id, tb.status
FROM '||p_table_name||' ...............';
DBMS_SQL.PARSE( v_cursor, v_select, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 1 , v_col_fs_id);
DBMS_SQL.DEFINE_COLUMN(v_cursor, 2 , v_col_status);
v_return := DBMS_SQL.EXECUTE(v_cursor);
LOOP
v_return := DBMS_SQL.FETCH_ROWS(v_cursor);
IF v_return = 0 THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_flow_set_id);
DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_status);
{........additional processing.........}
END LOOP;

Can anyone please help ???

[Sybrand Bakker]

Ran into this myself recently. The procedure is overloaded and in several cases
pl/sql can't make out which one to use. For me it helped (because the column
were strings) to append the length of the string as the fourth parameter to the
define_column call.
Hth,
Sybrand Bakker, Oracle DBA

[Dyfed]

Cheers !!!

That did the job. Not sure what would have happened had I been trying to
retrieve 2 numeric fields instead of 1 varchar and 1 numeric.

Regards,

Dyfed.
---------
Sybrand Bakker wrote in message <36F29688...@sybrandb.demon.nl>...