Re: Question on DBMS_SQL package

Hi Sven-Olaf!

   Tried this too, the package body compiled with errors, namely:

SQL> CREATE OR REPLACE PACKAGE BERETTA AS   2 FUNCTION TCOUNT(TNAME VARCHAR2)
  3 RETURN NUMBER;
  4 PRAGMA RESTRICT_REFERENCES(TCOUNT,WNDS);   5 END BERETTA;
  6 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY BERETTA AS   2 FUNCTION TCOUNT(TNAME VARCHAR2)

  3      RETURN NUMBER IS OUTCOUNT NUMBER(10);
  4      v_select varchar2(100);
  5      v_cursor number;
  6      v_count  number(10);
  7    BEGIN
  8      v_cursor := DBMS_SQL.OPEN_CURSOR;
  9      DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_count);
 10      v_select := 'SELECT COUNT (*) FROM ' || TNAME || ';';
 11      DBMS_SQL.PARSE(v_cursor,v_select,DBMS_SQL.V7);
 12      DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_count);
 13      DBMS_SQL.CLOSE_CURSOR(v_cursor);
 14      outcount := v_count;
 15      RETURN outcount;

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY BERETTA:

LINE/COL ERROR

-------- ---------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/3      PLS-00452: Subprogram 'TCOUNT' violates its associated pragma

Thanks for taking the time to reply.
Regards,
Paul

In article <387EF7C3.C8304460_at_opitz-partner.de>,   Sven-Olaf Kelbert <kelbert_at_opitz-partner.de> wrote:
> This is a multi-part message in MIME format.
> --------------58C86A194C9D2F1989742511
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Hi Paul!
>
> This is, what Oracle says to your problem in the official online-help:
>
> > ORA-06571 function name does not guarantee not to update database
> >
> > Cause: There are two possible causes for this message:
> > - A SQL statement references a packaged PL/SQL function that does
not contain a pragma that > prevents the database from being updated.
> > - A SQL statement references a stand-alone, PL/SQL function that
contains an instruction to > update the database.
> >
> > Action:
> > - If the referenced function is a packaged PL/SQL function:
Recreate the PL/SQL function with > the required pragma; be certain to include the 'Write No Database State' (WNDS) argument in the > argument list of the pragma.
> > - If the referenced function is a stand-alone PL/SQL function: Do
not use the function.
>
> So, you have to put the function in a package and write in the
> Specification
>
> pragma restrict_references(tcount,'WNDS');
>
> Greetings,
> Sven-Olaf.
>
> pberetta_at_my-deja.com wrote:
> >
> > Tried to create and use what I thought was a rather simple function
> > using the DBMS_SQL package. All I'm attempting to do is create a
> > dynamic SQL statement that will return the count of records in any
> > table. Wrote the following:
> > CREATE OR REPLACE FUNCTION TCOUNT(TNAME VARCHAR2)
> > RETURN NUMBER IS OUTCOUNT NUMBER(10);
> > v_select varchar2(100);
> > v_cursor number;
> > v_count number(10);
> > BEGIN
> > v_cursor := DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_count);
> > v_select := 'SELECT COUNT (*) FROM ' || TNAME || ';';
> > DBMS_SQL.PARSE(v_cursor,v_select,DBMS_SQL.V7);
> > DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_count);
> > DBMS_SQL.CLOSE_CURSOR(v_cursor);
> > outcount := v_count;
> > RETURN outcount;
> > END;
> > /
> > The function compiles without errors, but when I attempt to invoke
it
> > in a SELECT statement, the result is:
> > SQL>
> > SQL> SELECT table_name, tcount(table_name) FROM user_tables;
> > SELECT table_name, tcount(table_name) FROM user_tables
> > *
> > ERROR at line 1:
> > ORA-06571: Function TCOUNT does not guarantee not to update database
> >
> > I'm at a loss as to how to correct this problem (or if it can be
> > corrected). Any help would be appreciated.
> > Thanks,
> > Paul
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> --------------58C86A194C9D2F1989742511
> Content-Type: text/x-vcard; charset=us-ascii;
> name="kelbert.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Sven-Olaf Kelbert
> Content-Disposition: attachment;
> filename="kelbert.vcf"
>
> begin:vcard
> n:Kelbert;Sven-Olaf
> tel;cell:0179-3941308
> tel;fax:02261-600161
> tel;home:0221-9840094
> tel;work:02261-600156
> x-mozilla-html:FALSE
> org:OPITZ & PARTNER
> adr:;;Kirchstr. 6;51647 Gummersbach;;;
> version:2.1
> email;internet:kelbert_at_opitz-partner.de
> title:Dipl.Math.
> x-mozilla-cpt:;0
> fn:Sven-Olaf Kelbert
> end:vcard
>
> --------------58C86A194C9D2F1989742511--
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jan 15 2000 - 02:52:12 CET