Re: Dynamically spescifying a select clause for a cursor

On Mon, 30 Mar 1998 09:02:31 -0600, fkgrass_at_hotmail.com wrote:

[code snipped]

>After I run the command
> hndl := DBMS_SQL.OPEN_CURSOR;
>hndl is null and any other dbms_sql commands error out.
>
>Any Suggestions?
>
>Thanks,
>Fred Grass

Fred,

what kind of error did you get ? Which Oracle version do you use ?

Please note that in my code example I just tried to illustrate how DBMS_SQL can generally be used, but the program isn't actually doing something with the retrieved data. If you would like to e.g. simply print it in SQL*Plus, make the following modifications:

...
BEGIN    DBMS_OUTPUT.ENABLE; -- new
...

   DBMS_SQL.DEFINE_COLUMN(hndl, 2, v_ename, 30); -- change this line

--                                        ^^^^ Sorry, forgot that one

...

   WHILE (DBMS_SQL.FETCH_ROWS(hndl) <> 0) -- fetch them...    LOOP

• and get the data DBMS_SQL.COLUMN_VALUE(hndl, 1, v_empno); DBMS_SQL.COLUMN_VALUE(hndl, 2, v_ename); DBMS_OUTPUT.PUT_LINE(v_empno || ', ' || v_ename); -- new

   END LOOP;
...

Prior to starting it from SQL*Plus, you need to: SET SERVEROUTPUT ON I have no idea why OPEN_CURSOR would return NULL; what it should do is return an integer number, which is your cursor handle.

HTH
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net