Re: Function does not guarantee... using DBMS_SQL package.

hi,

dbms_sql is a package that does not guarantee 'WNDS'. So does your fnc. 'f' .
That's the reason.

Hakan

Jim Ley wrote:
>
> On Mon, 24 Jul 2000 14:44:35 +0200, "Frank van Bortel"
> <f.van.bortel_at_vnl.nl> wrote:
>
> >Somewhere along the line, purity is set for (one of the elements of)
> >packages.
> >You'll have to add pragma's, i.e:
> >pragma restrict_references(YourFunction, WNDS, WNPS);
> >in the specification of the package.
> >There are 4 pragma's possible:
> >WNDS: Write No Database State
> >WNPS: Write No Package State
> >RNDS: Read No Database State
> >RNPS: Read No Package State
>
> Thankyou for the helpful reply to my last question. However, on
> creating the following package and function (with pragma) the error
> below is given;
>
> CREATE OR REPLACE PACKAGE jim AS
> FUNCTION f(x in varchar2,
> y in varchar2)
> return varchar2;
> PRAGMA RESTRICT_REFERENCES (f, WNDS);
> end jim;
> /
>
> create or replace package body jim as
> function f(x in varchar2,
> y in varchar2)
> return varchar2
> is
> cursor_handle integer := DBMS_SQL.OPEN_CURSOR;
> dbms_sql_feedback integer;
> return_value varchar2(100) :=NULL;
> begin
> DBMS_SQL.PARSE (cursor_handle,
> 'select distinct ' || x ||
> ' from ' || y ||
> ' where type = ''CRE_DEL''',DBMS_SQL.NATIVE);
> DBMS_SQL.DEFINE_COLUMN (cursor_handle,1,x,100);
> dbms_sql_feedback := DBMS_SQL.EXECUTE (cursor_handle);
> dbms_sql_feedback := DBMS_SQL.FETCH_ROWS (cursor_handle);
> if dbms_sql_feedback > 0
> then
> DBMS_SQL.COLUMN_VALUE (cursor_handle,1,return_value);
> end if;
> DBMS_SQL.CLOSE_CURSOR (cursor_handle);
> return_value := 7;
> return return_value;
> end f;
> end jim;
> /
>
> On compiling the function f the error;
> PLS-00452: Subprogram 'F' violates its associated pragma
> is given. Why?
>
> Jim.
Received on Mon Jul 24 2000 - 00:00:00 CDT