Sub-blocks and PRAGMA RESTRICT REFERNCES

[Natik]

What is a practical application of PRAGMA RESTICT REFERENCES? Where could it be used practically?

What are the benifits of using a sub-block(nested block) in a procedure or a function. What would be a senario where this could be used?

[jmodic]

Originally posted by Natik
What is a practical application of PRAGMA RESTICT REFERENCES? Where could it be used practically?

Nowadays that pragma has lost its meaning. But if you still using Oracle 8.0 or 7.3 then you must use that pragma for *packaged* functions, if you want to cal those functions from inside SQL statements. That pragma actually tells SQL parser the "purity level" of the called function. For example purity level "WNDS" guaraties to the parser that this function does not make any changes to the database when called. You don't need this pragma specified for standalone functions and you don't need it for packaged functions if they are to be called only in PL/SQL expressions. And you don't need it at all in 8i and above.

What are the benifits of using a sub-block(nested block) in a procedure or a function. What would be a senario where this could be used?

The most common use of sub-blocks would be when you want to handle exceptions for that particular piece of code explicitely, rather than handeling them for the whole main block.

[Shestakov]

Some addition notes about pragma restrict references:

Oracle 7,8 use static resolution of pragma resriction (on compilation stage).
In this case u can't use dynamic sql statmetns in package if u going to use this
function (for example) in select statments.

Oracle 8i and 9i use dynamic resolution of pragmas (on execution stage) and u
can use dynamic sql statmetns in packages and use its in sql statments (not only pl/sql)
but u should be absolutly sure, that ur package(function) don't make any
changes to the database when called.