Re: Problem with DBMS_SQL.PARSE when called from Repots2.5

> I am trying to run the following PL/SQL code from the Reports2.5 :
> function BeforeReport return boolean is
> cursor_handle INTEGER;
> exec_var INTEGER;
> begin
> cursor_handle := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE
> (cursor_handle, 'SELECT 2+2 from DUAL' , DBMS_SQL.V7);
> exec_var := DBMS_SQL.EXECUTE (cursor_handle);
> DBMS_SQL.CLOSE_CURSOR(cursor_handle);
> return (TRUE);
> end;
> ==================================================
> and I am getting the following error message:
> "component V7 must be declare".
> (cursor stops at DBMS_SQL.V7 as marked "^^^") and fails to compile.
> But when I compile/run a very similar code from Procedure Builder or
> SQL> prompt it works just fine. Any remedies or hints?

Have you ever successfully called the DBMS_SQL routines from Reports 2.5? I wrote an article sometime last year for the Cobb Group's "Exploring Oracle Developer/2000" (something along those lines) describing a scenario like yours. I have not been using the Developer 2000 tools for that last year so the situation may have changed, if it has not read on.

The Developer/2000 tools are compiled with PL/SQL version 1. The Oracle supplied database packages are not available until PL/SQL version 2.x. Hence you cannot call them from within Developer/2000. What you can do however is write your own package or stored procedure which calls DBMS_SQL to do what you want to do. This is the so-calle workaround. If this is your problem and you need some more help I'd be happy to help and I could also get you a copy of the article.

Chris Halioris
Tactics, Inc.
hali_at_tacticsus.com Received on Thu Feb 20 1997 - 00:00:00 CST