Can I make a statement now, after reading this, that the restrict references pragma is not important at all in 8i,
if no where does it really needs to be used?

that answers it all. thanxs tom.

Tom, could you help-me?

I´m problem in to utility pragma restrict_references.
I need to do alteration in Pl/SQl program where.

create or replace package pg_test as

function fn_test (pi_cod_city pg_type.cod_city)
return pg_types.name_city;

pragma restrict_references (fn_test,wnds,wnps);

end;

create or replace package body pg_test as
FUNCTION fn_se_sector_name
(pi_cod_city IN pg_types.tp_cod_city)
RETURN pg_types.tp_name_city IS
v_CITY_NAME pg_types.tp_nom_setor;
BEGIN
SELECT cesp_name_city
INTO v_name_city
FROM vw_city
WHERE cod_city = pi_cod_city;

RETURN V_CITY_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return NULL;
END;

im try update vw_city to vw_city_test,exception error,

Compilation errors for PACKAGE BODY SCPF_PROC.PG_TEST

Error: PLS-00452: Subprogram 'FN_city_NAME' violates its associated pragma
Line: 243
Text: FUNCTION fn_CITY_name

Error: PL/SQL: Compilation unit analysis terminated
Line: 0
Text: CREATE OR REPLACE PACKAGE BODY pg_TEST AS

tks a lot

Hi Tom.

I've got a package that includes one function. Ordinarilly, we've always had to restrict_references on the package and the function. This morning, while demonstrating this to a colleague, I commented out the pragmas, compiled the package and the package body, and then ran the package. But Oracle did NOT give me an ORA-06571. We're running 8.0.6. And the procedure updates tables (the function does not), but those tables are on a remote database. Any reason why this is allowed without the pragma?

Package:

as
procedure set_reprice;
procedure start_proc( p_procedure_name in varchar2, p_process_id out number );
procedure end_proc( p_procedure_name in varchar2 );
procedure set_step(p_procedure_name in varchar2, p_step in number);
procedure start_step(p_procedure_name in varchar2, p_step in number,
p_process_id in number);
procedure end_step(p_proc_id in number, p_module_step in varchar2);
procedure exec_sql( p_sql_string in varchar2 );
procedure write_transaction( p_process_id in number, p_object_name in varchar2,
p_action in varchar2, p_data_char in varchar2, p_data_numeric in number,
p_object_module in varchar2);
function recal_reprice (p_as_of_date in date, p_reprice_freq in number,
p_next_reprice_date in date) return date;

procedure update_last_reprice;

--pragma restrict_references( scrubs, wnds, rnds, wnps, rnps );
--pragma restrict_references( recal_reprice, wnds, rnds, wnps );


end;

Hi Tom,

Can you tell me whether we can use pragma RESTRICT_REFERENCES while creating stand alone functions or stand alone procedures.

Thanks in Advance.

Hi Tom

Does this work with Oracle 8 - I fear not? I get the violates message with the following package but all I am doing is querying the database. I have to use dynamic queries as the process can cover many accounts (p_storage parameter)

CREATE OR REPLACE PACKAGE ds_fn
AS


FUNCTION causality (
p_storage IN VARCHAR2,
p_cev_key IN sn_int_co.aes_clinical_event_data.aes$cev_key%type,
p_event_id IN sn_int_co.aes_clinical_event_data.aes$event_id%type
) RETURN VARCHAR2;

pragma restrict_references( causality, wnds, wnps);

END ds_fn;
/


CREATE OR REPLACE PACKAGE BODY ds_fn
AS


FUNCTION causality (
p_storage IN VARCHAR2,
p_cev_key IN sn_int_co.aes_clinical_event_data.aes$cev_key%type,
p_event_id IN sn_int_co.aes_clinical_event_data.aes$event_id%type
) RETURN VARCHAR2 IS

-- Forced to use dynamic SQL becase EXECUTE IMMEDIATE not available and so
-- that all storage areas may be accessed using the same single procedure.


l_cursor PLS_INTEGER DEFAULT dbms_sql.open_cursor;

l_delimiter CONSTANT VARCHAR2(1) := '/';
l_unknown CONSTANT VARCHAR2(7) := 'UNKNOWN';
l_count PLS_INTEGER;
l_return VARCHAR2(500);

l_rep VARCHAR2(20);
l_local VARCHAR2(20);
l_comp VARCHAR2(20);

l_sql VARCHAR2(500);
l_status INTEGER;

BEGIN

l_sql:='SELECT t2.aes$cvd_rep_causal,
t2.aes$cvd_local_causal,
t2.aes$cvd_comp_causal
FROM '
||p_storage||'.aes_exposed_drug_data t1, '
||p_storage||'.aes_cev_drug_data t2
WHERE
t2.aes$cev_key = '''||p_cev_key||'''
AND t1.aes$drug_key = t2.aes$drug_key
AND t2.aes$event_id = '''||p_event_id||'''
AND t1.aes$event_id = t2.aes$event_id
AND t1.aes$is_suspect = ''YES''
AND t1.aes$key_ingredient IS NOT NULL
AND T1.aes$importance = 1';

dbms_sql.parse(l_cursor,l_sql,dbms_sql.v7);
dbms_sql.define_column(l_cursor,1,l_rep,20);
dbms_sql.define_column(l_cursor,1,l_local,20);
dbms_sql.define_column(l_cursor,1,l_comp,20);
l_status := dbms_sql.execute (l_cursor);

IF (dbms_sql.FETCH_ROWS (l_cursor) > 0) THEN
dbms_sql.column_value (l_cursor, 1, l_rep);
dbms_sql.column_value (l_cursor, 1, l_rep);
dbms_sql.column_value (l_cursor, 1, l_rep);
dbms_sql.close_cursor (l_cursor);
ELSE
dbms_sql.close_cursor (l_cursor);
l_sql := 'SELECT count(*)
FROM '
||p_storage||'.aes_clinical_event_data t1
WHERE
t1.aes$event_id = '''||p_event_id||'''';
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor,l_sql,dbms_sql.v7);
dbms_sql.define_column(l_cursor,1,l_count);
l_status := dbms_sql.execute (l_cursor);
dbms_sql.column_value (l_cursor, 1, l_count);
dbms_sql.close_cursor (l_cursor);
IF l_count=1 THEN

l_sql := ' SELECT
aes$reporter_causal,
aes$local_causal,
aes$company_causal
FROM '||p_storage||'.aes_event_data
WHERE
aes$event_id = '''||p_event_id||'''';

l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor,l_sql,dbms_sql.v7);
dbms_sql.define_column(l_cursor,1,l_rep,20);
dbms_sql.define_column(l_cursor,1,l_local,20);
dbms_sql.define_column(l_cursor,1,l_comp,20);
l_status := dbms_sql.execute (l_cursor);

IF (dbms_sql.FETCH_ROWS (l_cursor) > 0) THEN
dbms_sql.column_value (l_cursor, 1, l_rep);
dbms_sql.column_value (l_cursor, 1, l_rep);
dbms_sql.column_value (l_cursor, 1, l_rep);
END IF;
dbms_sql.close_cursor (l_cursor);

END IF;

END IF;

l_return := NVL(l_rep,l_unknown)||l_delimiter||
NVL(l_local,l_unknown)||l_delimiter||
NVL(l_comp,l_unknown);

RETURN l_return;

END;

END ds_fn;

I have a legacy package (very old) that has a bunch of PRAGMA RESTRICT_REFERENCES stuff all over the place.

The database has just been upgraded to 9.2.0.4, it has been running fine for last couple months.

One of the functions in this package defined as

PRAGMA RESTRICT_REFERENCES (get_id, WNDS, WNPS, RNPS);

This function uses EXECUTE IMMEDIATE to do some dynamic stuff.

This has been working fine for last couple months and suddenly this function has started to throw ORA-6538!

Why? I dont understand. It just does a

EXECUTE IMMEDIATE 'SELECT ...'

So, WNDS and WNPS are 100% ok.

It is most probably failing on the RNPS

But why? And more importantly, why all of a sudden?

Thanks

Um, I said ORA-6538?

function get_id (table_name in varchar2,table_no in varchar2)
return number
is
v_sql varchar2(1000);
v_id number;
begin
v_sql := 'select '||table_name||'_id from '||table_name||' where '||table_name||'_no='||table_no;
execute immediate v_sql into v_id;
return v_id;
end;

In 8i this was working fine, in 9i also, I am pretty sure this was working fine since we upgraded to 9i last month.

Why would 9i suddenly start to throw ORA-6358 for this function?

[Yes, the solution is to remove these ancient PRAGMA stuff, but I am concerned that the behaviour suddenly changed]

Thanks

I gotta say -- i truly hate that function with a passion.

not a bind variable in sight.  ugh.  

Why you cannot just code:

select ...., ( select XXXX_id from XXXX where XXXX_no = <whatever> )
  from your_table


it would perform lots better, scale.....


was it throwing it at runtime or compile time?  I cannot get it to reproduce the issue unless I stick some dynamic sql in there that actually updates stuff

(but would suggest that instead of:

ops$tkyte@ORA9IR2> select demo_pkg.get_id( 'xxxx', '42' ) from dual;
 
DEMO_PKG.GET_ID('XXXX','42')
----------------------------
                          42
 
<b>you would more properly and efficiently code:</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select (select xxxx_id from xxxx where xxxx_no = '42') from dual;
 
(SELECTXXXX_IDFROMXXXXWHEREXXXX_NO='42')
----------------------------------------
                                      42

where '42' can even be "a bind" or a column referenced in the outer query (and hence a bind) 

Yes, I hate that package with a passion too. But it pre-dates me and I have to pick my battles!

"was it throwing it at runtime or compile time? I cannot get it to reproduce the issue unless I stick some dynamic sql in there that actually updates stuff"

At run-time. And like I said, it was working fine for the last few weeks in 9iR2 and suddenly started to throw this exception!

Since it is only a SELECT, the WNDS is definitely not violated.

Can you see any reason why it would violate the RNPS or WNPS? And that too, not consistently!

Thanks

another thing i have never understood about using bind variables with execute immediate

execute immediate, by definition, hard parses every time. one of the main reasons to use bind variables is to avoid hard parses. so why does it matter if you use bv's with execute immediate or not?

why is

v_sql := '... :b1 ...';
execute immediate v_sql using 'blah'

any better than

execute immediate replace(v_sql,':b1','blah)

Thanks

"nope, I cannot see any reason for it to throw that error "as it is""

But it is throwing that error and not consistently! It is really frustrating?

Open a TAR with Support then? But since it is not 100% reproducible, I foresee a difficult time explaining this to them!

Any ideas?

<quote>
execute immediate -- in 9ir2 and before -- PARSES everytime
it might be hard
it might be soft
it might be a softer soft
<quote>

Hm? I thought EXECUTE IMMEDIATE always caused a hard parse, everytime, no? Thats just the way it works?

<quote>
query not in shared pool is much more likely WITHOUT binds than with....
<quote>

what do binds have to do with query being in shared pool or not? Every query that is parsed goes into the shared pool. It is aged out depending on the demand on the shared pool, its size, activity, etc. Where do binds come in the picture?

Thanks

As tom has explained about bind variables, if you don't use them in an execute immediate the shared pool will be flooded with statements of the following form

select x from table where id=1;
select x from table where id=2;
select x from table where id=3;
etc.

whereas if you coded with bind variables you will only have one statement in the shared pool of the form

select x from table where id=:bindname

This means that existing sql does not get aged out of your shared pool because of the large number of "similar" sql statements being parsed from your execute immediate. It also means that once that statement has been parsed once it can be soft parsed the next time [whereas without bind variables it will hard parse the next time as well].

Hope I've summarised this correctly!

My mistake, yes, I understand the importance of using bv's and EXECUTE IMMEDIATE is no exception. Yes, my get_id function indeed does use bv's and the USING clause of EXECUTE IMMEDIATE.

Another thing I just remembered...this process that has the intermittent, inconsistent exceptions on the ORA-6538 (violates pragma RNPS or WNPS), it is a distributed transaction that uses Microsoft DTS and Oracle's XA support.

Given this, would there be any reason to throw the ORA-6538?

Thanks

Sorry again, but thats why I apologized earlier. I had posted the code from memory and when I went back and checked it was indeed using bv's and USING. I would never do this with support (sorry for doing this to you and sidetracking the discussion into bv's)

Regarding the XA/DTS stuff, this is part of a XML/.NET messaging infrastructure using MSMQ. All this was working fine in 8i, I am trying to figure out why the PRAGMA is suddenly causing the ORA-6538 in 9iR2

Oh well, I guess I can simply get rid of the silly PRAGMA RESTRICT_REFERENCES lines and move on!

Thanks for your time

Finally figured it out. 

Function based indexes are the issue here.

SQL>  create or replace package p as function f(i int) return int deterministic;
  2   function get_id(i in varchar2,j in varchar2) return int;
  3  pragma restrict_references(get_id,WNDS,WNPS,RNPS);
  4  end;
  5  /

Package created.

SQL> create or replace package body p as function f (i int) return int is begin return 1;end;
  2  function get_id(i in varchar2,j in varchar2) return int 
  3  as
  4  v_id number;
  5  begin execute immediate 'select '||i||'_no from '||i||' where '||i||'_no = :b1' into v_id using j;
  6  return v_id;
  7  end;
  8  end;
  9  /

Package body created.

SQL>  create table i(i_id number,i_no varchar2(10),j int);

Table created.

SQL> create index ii on i(p.f(j));

Index created.

SQL>  select p.get_id('i',1) from dual;
 select p.get_id('i',1) from dual
        *
ERROR at line 1:
ORA-06538: statement violates WNDS, WNPS, RNPS RESTRICT_REFERENCES pragma
ORA-06512: at "XXX.P", line 5

If I drop the index, it is fine

SQL> drop index ii;

Index dropped.

SQL> select p.get_id('i',1) from dual;

P.GET_ID('I',1)
---------------


Any idea why adding the FBI would violate the PRAGMA?

Thanks 

it (the query get_id) is implicitly running a function F, or at least has the ability to.

The purity of F has not been asserted.

Hence, the purity of GET_ID is in question (you are judged by your friends :)


if you assert the purity of the function f which COULD in theory be invoked, then all is well.

ops$tkyte@ORA9IR2> create or replace package p
  2  as
  3          function f(i int) return int deterministic;
  4          pragma restrict_references(f,WNDS,WNPS,RNPS);
  5          function get_id(i in varchar2,j in varchar2) return int;
  6          pragma restrict_references(get_id,WNDS,WNPS,RNPS);
  7  end;
  8  /
 
Package created.
 
 

"it (the query get_id) is implicitly running a function F, or at least has the ability to"

Um, dont get it. p.f() should be called only when doing DML on the table since table i has index ii which calls p.f.

Why would a query care about or have the ability to run the function f? The query (could) simply read the index structure and not actually call the function? Isnt that the whole point of creating the FBI so that queries read the index and not call the (potentially expensive) function?

What am I missing?

Hi Tom,

Why is it not possible to use a function defined inside a package body in a select statement written in the same package? Like..

CREATE OR REPLACE PACKAGE BODY pkgtest
AS
FUNCTION func_inselect
RETURN INTEGER;

-------------------------------------------------------------------------------
PROCEDURE proc_wrapper
IS
i INTEGER;
BEGIN
SELECT func_inselect
INTO i
FROM DUAL;

DBMS_OUTPUT.put_line ('Output:' || i);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END proc_wrapper;

-------------------------------------------------------------------------------
FUNCTION func_inselect
RETURN INTEGER
IS
BEGIN
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN -1;
END func_inselect;
-------------------------------------------------------------------------------
END pkgtest;

It's throwing a compile time errro "PLS-00231: function 'FUNC_INSELECT' may not be used in SQL".

Could you please point out where I went wrong?

Thanks and regards

Praveen

Suppose he did - SELECT pkgtest.func_inselect FROM dual;

Would this work inside the procedure...given that func_inselect is declared global inside the package.

Thanks.

Hi Tom,

Thanks, Tom, for the short, precise and upto-the-point answer. Your answer confirms that unless you declare the function in the specification or define a standalone function, you cannot "SELECT func_name()..." from within the package body and there is no other work around for this, even in 10g. Tom, can I come to this conclusion?

An 'YES' will clear the above reviewer's ("<pkg>.<function> would this work?") doubt also... :)

Thanks and regards.

Praveen

It seems that restrict_references( xxx, RNPS ) does not assert when a reference to a "private" package constant variable is made, but it does assert when the constant variable declaration is in the spec (public)... The following code compiles and runs fine, but if I move the declaration/initialization of INCREMENT_STEP to the pkg body, the compilation fails (unless I remove the pragma, of course). Assuming that constants are really restricted variables (since they still must be initialized at run-time), I would have expected that RNPS would mean that no references to constants (regardless of where they are defined) would be allowed, even though I would LIKE to have RNPS still allow references to "constants". The observed behavior doesn't seem consistent - the purity rules, and what constitutes "package state" appear to be nebulous here.

create or replace package PKG_MJY_TEST
as
pragma restrict_references( default, RNDS, WNDS, RNPS, WNPS ) ;

function INCREMENT ( X in integer )
return integer ;

end PKG_MJY_TEST ;
/

create or replace package body PKG_MJY_TEST
as
INCREMENT_STEP constant integer := 1 ;

function INCREMENT ( X in integer )
return integer
as
begin
return X + INCREMENT_STEP ;
end INCREMENT ;

end PKG_MJY_TEST ;
/

Tom,
There is a packaged function which has
pragma restrict_references(f_get_description,WNDS). This code is in our database for past 10 years. This packaged functions started showing compilation error "pls-00452 - Subprogram 'string' violates its associated pragma". Database is in 11g. I commented all the code and had
code like
--
function f_get_description return varchar2;
PRAGMA RESTRICT_REFERENCES(f_get_description,WNDS);
--
function f_get_description return varchar2 is
begin
return 'aaa';
end;
Error is still coming up, when I am not writing anything to database.
We have the same code in another database ( this database is also in 11g and used for testing which is a mirror database of development ) where the compilation error
is not coming up. Thanks for your help.

ops$tkyte%ORA11GR2> create table t ( x int );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3          function f return varchar2;
  4          pragma restrict_references(f,wnds);
  5  end;
  6  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3          function f return varchar2
  4          is
  5          begin
  6                  return 'x';
  7          end;
  8  
  9  begin
 10          insert into t values ( 1 );
 11  end;
 12  /

Warning: Package Body created with compilation errors.

ops$tkyte%ORA11GR2> show err
Errors for PACKAGE BODY MY_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/2      PLS-00452: Subprogram 'F' violates its associated pragma

Tom,
The actual function has some select's from the database. But the function does not have any inserts or updates to the database. This function is in database and working for past few years and all of sudden we are getting pragma restrict reference error which is Intriguing me. We have to remove the restrict reference and the error would go away. Database is Oracle 11g. Is there a new Oracle 11g feature which we are not aware of ?

Appreciate you help in this matter. Thanks.