Re: Dynamically spescifying a select clause for a cursor

On Tue, 31 Mar 1998 09:55:07 -0600, fkgrass_at_hotmail.com wrote:

[...]

>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

Hi Fred,

I'd say that the cause for the error is the DBMS_SQL.PARSE call (it got past the loc:=2, but not past loc:=3). Most likely there is an error or typo in your statement (in v2_sql variable). What exactly is the statement you're calling it with ?

In the exception handler, the following happens then: The cursor handle is an IN OUT parameter for DBMS_SQL.CLOSE_CURSOR, and after calling this procedure your handle will be nullified. The problem is that your exception code does not show the actual Oracle error message, so you should perhaps replace it with some code like this:

...

   EXCEPTION
   WHEN others THEN

      IF DBMS_SQL.IS_OPEN(hndl) THEN
         DBMS_SQL.CLOSE_CURSOR(hndl);
      END IF;
      RAISE;   -- you will see the Oracle error from your parse call

Or, instead of RAISE, include at least sqlerrm like this:

    raise_application_error(-20000, 'SQL error occured: ' || sqlerrm);

When working with dynamic sql, I found it always very useful to have a kind of 'debug' mode (e.g. controlled by a call parameter) which allows to have a look at the dynamic statements. One can use DBMS_OUTPUT, or better UTL_FILE for this (especially useful when you're dealing with programs that will generate and execute several hundred kB of SQL text).

HTH
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net