Re: Dynamically spescifying a select clause for a cursor

From: <fkgrass_at_hotmail.com>
Date: 1998/03/31
Message-ID: <6fr3es$q3h$1@nnrp1.dejanews.com>#1/1

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') );
END generate_body;

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