Sub-blocks and PRAGMA RESTRICT REFERNCES
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Sub-blocks and PRAGMA RESTRICT REFERNCES

  1. #1
    Join Date
    Nov 2001
    Posts
    110
    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?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width