PL/SQL Cursor Query

[clintonwhite]

Hi There - I wonder if anyone could help me with a query that I have with using PL/SQL cursors in a stored procedure. I am trying to remove SQL code from a VB program and put it into stored procedures and I am having a problem when using a parameter to the stored procedure, within the cursor statement.

I am passing a string to the stored procedure, which is the name of the column that I want to retrive from the database table and thus will form part of the CURSOR IS statement. My PL/SQL code thus reads :-

CURSOR stock is select p_column_name from stockchart;

v_stockrow number; -- p_column_name is a numeric value

OPEN stock;
FETCH stock into v_stockrow;
CLOSE stock;

The stored procedure compiles ok but has a type converstion error when run through SQL plus.
If I hardcode the name of the column to say "HAMMER", instead of using the variable p_column_name, it runs ok - I do not want to do this however as there are a lot of columns in the table.

Is there anyway I can code the CURSOR is statement to allow for the use of a parameter as the source column.

Thanks for any help / ideas.

 

[sem]

You should use REF CURSOR:

procedure myProcedure(p_column_name in varchar2)
..
TYPE RC IS REF CURSOR;
stock rc;
v_stockrow number;
...
BEGIN
open stock for 'select '||p_column_name||' from stockchart';

FETCH stock into v_stockrow;
CLOSE stock;
...
end;

Regards, Dima

 

[clintonwhite]

Great thanks very much for that - I must admit to seeing a section in a book called REF CURSORS but overlooking it somewhat - DOH !!