| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: parameterized view
A copy of this was sent to suisum_at_ecn.ab.ca ()
(if that email address didn't require changing)
On 3 Jan 99 20:54:16 GMT, you wrote:
>Hi Thomas:
>
>I followed your example for parameterized view in url:
> http://www.dejanews.com/getdoc.xp?AN=363868651
>
>But I have few questions, please help.
>
>1. I don't know the purpose of the pragma restrict_refereneces. Please
>explain.
it allows functions in packages to be called from SQL. here is a short blurb from the PL/SQL manual:
<quote>
To be callable from SQL expressions, a stored function must obey certain
rules meant to control side effects. For standalone functions, Oracle can
enforce these rules by checking the function body. However, the body of
a packaged function is hidden. So, for packaged functions, you must use
the pragma RESTRICT_REFERENCES to enforce the rules. For more
information, see “Calling Stored Functions from SQL Expressions” in
Oracle7 Server Application Developer’s Guide.
</quote>
>
>2. I don't know how the 'exec my_params.g_param1 := 7369' statement can
>affect the query for the demo view. Will this parameter will last for the
>entire session?
>
exec my_params.g_param1 := 7369 , when run in sqlplus, is the same as executing:
begin
my_params.g_param1 := 7369;
end;
I simply ran a small anonymous block of pl/sql to set a package variable to a value. Yes, package variables (those not defined inside of a procedure or function) retain their value for the duration of your session. PL/SQL packages in the database are very useful this way -- they retain their state between calls ( cursors stay open, counters retain values and so on)
>3. Please post the table definition for the sample table T used in url:
>
> http://www.dejanews.com/getdoc.xp?AN=373620126
>
its the emp table you find in the scott/tiger schema. Everyone has it. You can find the definition of it in $ORACLE_HOME/sqlplus/demo/demobld.sql.
>Thanks a lot,
[snip]
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Jan 03 1999 - 17:09:36 CST
 |
 |