Home » SQL & PL/SQL » SQL & PL/SQL » RESTRICT_REFERENCE (Oracle 10g, 10.2.0.1.0, Windows XP)
RESTRICT_REFERENCE [message #448617] Wed, 24 March 2010 06:15 Go to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Hello everyone,

I am having a confusion in understanding a concept. Can anyone help me, here i goes

When i am writing a function (Stand alone or inside a Package) i know what i am writing,
i know weather i am changing a Package state or weather i am changing a Database State. So what is the use for giving a PRAGMA RESTRICT_REFERENCE?

as for the other PRAGMAs
EXECPTION_INIT is needed to Specify an error name to a specifiec error number so that i can use the error name to handle the exception. AUTONOMOUS_TRANSACTION is used to execute the SQL Operation inside a Block as a child Transaction.SERIALLY_REUSABLE states that a package variable doesnot persists throughout the session.

but

RESTRICT_REFERENCE states that the code should not do the following
RNPS : Read no package state,
WNPS : Write no package state
RNDS : Read no database state
WNDS : Write no database state

what is there to state as i know what my code is doing.

without specifing EXECPTION_INIT, AUTONOMOUS_TRANSACTION or SERIALLY_REUSABLE i cannot get a handler for an exception which doesnot have a handler, i cannot execute SQL Operations from inside the CODE autonomously or i cannot reuse the package variable, but without
the RESTRICT_REFERENCE is can ensure that my code in not doing (RNPS,WNPS,RNDS,WNDS).

[Updated on: Wed, 24 March 2010 06:50]

Report message to a moderator

Re: RESTRICT_REFERENCE [message #448620 is a reply to message #448617] Wed, 24 March 2010 06:51 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You might know that you haven't done those things, but another programmer who comes along to modify the function/procedure after you might not know that they shouldn't do those things.

Obvious example is WNDS. Any function that is used in SQL can't modify data or you'll get an error.
Say you've got a function that is called by both SQL and PL/SQL and someone modifies it to write data because they don't realise it's called from SQL.
If you add that pragma then they'll know not to do that, and the compiler will stop them from compiling a version of the function that does write data.
Re: RESTRICT_REFERENCE [message #448635 is a reply to message #448620] Wed, 24 March 2010 07:33 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
OK, but any programmer can change the whole package, the Specification as well as the Body

Programmer 1 is creating the Package with the Restriction.

create or replace package pkg_restrict
is
	n number(10);
	function ff1 return varchar2;
	pragma restrict_references(ff1, 'WNPS');
end pkg_restrict;
/
show err

create or replace package body pkg_restrict
is
	function ff1 return varchar2
	is
	begin
		return 'Hello...';
	end ff1;
end pkg_restrict;
/
show err


Programmer 2 trying to change the function is getting the following error

create or replace package body pkg_restrict
is
	function ff1 return varchar2
	is
	begin
		return 'Hello...';
		n:=10;
	end ff1;
end pkg_restrict;
/
show err

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY PKG_RESTRICT:

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


Programmer 2 is changing the Package as a whole makeing his work done.

create or replace package pkg_restrict
is
	n number(10);
	function ff1 return varchar2;
	--pragma restrict_references(ff1, 'WNPS');
end pkg_restrict;
/
show err

create or replace package body pkg_restrict
is
	function ff1 return varchar2
	is
	begin
		return 'Hello...';
		n:=10;
	end ff1;
end pkg_restrict;
/
show err


So, where is the Restriction.

Re: RESTRICT_REFERENCE [message #448638 is a reply to message #448635] Wed, 24 March 2010 07:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you've missed the point of PRAGMA instructions.

A PRAGMA isn't there for you, it's an instruction to the compiler.

In the case of the Restrict_References pragma, it is an instruction to the compiler about how the following code behaves.

If a function has WNDS ( I think) then you can call it from SQL.
If it has all four, then you can call it from a parallel SQL statement,

As of Oracle 9, I believe that they are largely obsolete, as the compiler now performs these checks automaticaly.

I think the only use for them is if you've got a function that calls C or Java code - the database has no way of assessing the purity of that functino, and so you need to explicitly provide that information with these pragmas.

[Updated on: Wed, 24 March 2010 07:43]

Report message to a moderator

Re: RESTRICT_REFERENCE [message #448647 is a reply to message #448617] Wed, 24 March 2010 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
@sen.suvro - of course someone can remove the pragma, but you'd like to hope that if they tried to add code that violated the pragma they'd stop and think about why the pragma is there instead of blindly removing it. If you go about commenting out code because you haven't bothered to understand why it's there you're always going to break stuff.

@JRowbottom - I still think there is a use for them in the way I've described, although I have to admit I don't generally bother using them. The example I gave was based on something that actually happened.
A function was called from half a dozen packages via PL/SQL and called in SQL from one oracle report. The person changing it didn't realise it was used by the report and so they broke it. And because no-one realised the report was affected it wasn't tested and went out to production like that. Made us look bad and we had to rush out an emergency patch.
If the function had had a pragma then the coder might have thought twice about making the change.

So basically while you no longer need them most of the time to be able to call functions from SQL they can still be useful to help protect code from unwise changes further down the line.
Re: RESTRICT_REFERENCE [message #448650 is a reply to message #448647] Wed, 24 March 2010 08:35 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
cookiemonster wrote on Wed, 24 March 2010 08:19
If the function had had a pragma then the coder might have thought twice about making the change.

So basically while you no longer need them most of the time to be able to call functions from SQL they can still be useful to help protect code from unwise changes further down the line.


Thanks cookiemonster

This is THE POINT why we need pragma ..and it would have prevented further modification by giving error if dev overlooks the pragma.
Re: RESTRICT_REFERENCE [message #448714 is a reply to message #448617] Wed, 24 March 2010 23:37 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Well, i can figure out this now, this PRAGMA is there to provide an instruction to the Compiler and to provide a guideline to the Coder
for changing the code.

Thanks for the explanation


Re: RESTRICT_REFERENCE [message #448716 is a reply to message #448650] Wed, 24 March 2010 23:39 Go to previous message
s4.ora
Messages: 63
Registered: March 2010
Member
Well, i can figure out this now, this PRAGMA is there to provide an instruction to the Compiler and to provide a guideline to the Coder
for changing the code.

Thanks for the explanation
Previous Topic: help on collections
Next Topic: column wise Data after wmsys.wm_concat
Goto Forum:
  


Current Time: Sun Dec 11 06:28:41 CST 2016

Total time taken to generate the page: 0.09406 seconds