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

From: Jim Ley <jim_at_jibbering.com>
Date: 2000/07/24
Message-ID: <397c7366.29492300@news-east.usenetserver.com>#1/1

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