Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamically spescifying a select clause for a cursor
In article <352004fd.2305611_at_news.okay.net>,
peter.schneider_at_okay.net (Peter Schneider) wrote:
>
> 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
>
Here is the code i am trying to get working. This is part of a package.
PROCEDURE generate_body IS hndl INTEGER; exec INTEGER; loc char(2); BEGIN loc :='1'; hndl := DBMS_SQL.OPEN_CURSOR; -- open cursor handle loc :='2'; DBMS_SQL.PARSE(hndl,v2_sql,DBMS_SQL.NATIVE); -- parse the statement loc :='3'; DBMS_SQL.DEFINE_COLUMN(hndl,1,v2_line,4000); -- Define line as input loc :='4'; exec := DBMS_SQL.EXECUTE(hndl); -- Execute the statement WHILE DBMS_SQL.FETCH_ROWS(hndl) <> 0 LOOP loc :='5'; DBMS_SQL.COLUMN_VALUE(hndl,1,v2_line); -- Some appp logics END LOOP; loc :='6'; DBMS_SQL.CLOSE_CURSOR(hndl); -- All done close cursor EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(hndl); raise_application_error(-20000,'Error ' || loc || NVL(TO_CHAR(hndl),'null') );
I get this back when i run the program
ORA-20000: Error 2 null
-- my error messsage
ORA-06512: at "DBA_USER.AREP", line 74
-- the line i called this function from
ORA-06512: at line 2
-- nothing
Any ideas?
Thanks for all your help.
Fred Grass
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Mar 31 1998 - 00:00:00 CST
![]() |
![]() |