| 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 4 Jan 99 00:06:16 GMT, you wrote:
>Hi Thomas:
>
>Thank you very very much for your detail explanation. I still have
>following questions.
>
>Thomas Kyte (tkyte_at_us.oracle.com) wrote:
>
>: 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.
>
>I am very sorry. This may be stupid. But I have never used
>RESTRICT_REFERENCES in any functions (stored functions) I wrote. I saw the
>the developer guide but I didn't understand. Does it mean that it stop the
>function to change the value???
>
it doesn't stop it, it (the compiler) verifies that the function OBEYS the pragma. Consider the following:
SQL> create or replace package my_pkg
2 as
3 function foo return number; 4 pragma restrict_references( foo, wnds, wnps, rnds, rnps );5 end;
Package created.
so, we are ASSERTING that foo will:
WNDS - write no database state (no insert, update, delete, etc) WNPS - write no package state (modifies no global variables) RNDS - read no database state (no queries) RNPS - read no package state (reads no global package variables)
When we create the body however:
SQL>
SQL> create or replace package body my_pkg
2 as
3
3 g_cnt number default 0;
4
4 function foo return number
5 is
6 begin
7 g_cnt := g_cnt + 1; 8 return g_cnt;
Warning: Package Body created with compilation errors.
SQL> show errors package body my_pkg
Errors for PACKAGE BODY MY_PKG:
LINE/COL ERROR
-------- ----------------------------------------------------------------- 0/0 PL/SQL: Compilation unit analysis terminated 4/1 PLS-00452: Subprogram 'FOO' violates its associated pragmaSQL> we violated the assertion. In particular we both READ and WRITE the package state. Writing to the package state will cause us to not be able to be called in the where clause for example. We can be 'selected' but not 'whered' on. We fix the pragma as follows:
SQL> create or replace package my_pkg
2 as
3 function foo return number; 4 pragma restrict_references( foo, wnds, rnds );5 end;
Package created.
and then we can:
SQL>
SQL> alter package my_pkg compile body;
Package body altered.
SQL> show errors package body my_pkg
No errors.
>: 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)
>
>If I store the value to the package variable, can I retrieve it in the
>same SQL/Plus session. I tried the following but the statement failed.
>
>SQL> select my_params.g_param1 from dual;
>select my_params.g_param1 from dual
> *
>ERROR at line 1:
>ORA-00904: invalid column name
>
thats cause you can only select FUNCTIONS in sql, not variables from a package spec. I had a function in that package called 'get_param1' that can be selected (thats what we used in the view)
>Is it possible pass the value of a variable back/forth to a anonymous
>block of pl/sql? That means - if any way to retain the value in the
>package variable. And retrieve it in SQL/Plus session?
>
Yes. You can use bind variables. Consider:
SQL> create or replace package my_pkg
2 as
3 g_number number default 0;
4 end;
5 /
Package created.
SQL> SQL> variable x number; SQL> SQL> SQL> begin my_pkg.g_number := 55; end;
PL/SQL procedure successfully completed.
SQL>
SQL> begin :x := my_pkg.g_number; end;
2 /
PL/SQL procedure successfully completed.
SQL>
SQL> print x
X
55
>: >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.
>
>Thank you for the info and sorry for the confusion. The table T for
>http://www.dejanews.com/getdoc.xp?AN=373620126 is to create the following
>view:
>
I think it was:
create table T ( custno number, descript varchar2, sale_amount number, sale_date );
>create or replace view T_View
>as
>
>select custno, descript, sum(ty_sales) ty_sales, sum(ly_sales) ly_sales,
> sum(ty_sales-ly_sales) diff
> from
> (select custno, descript, sale_amount ty_sales, 0 ly_sales
> from T,
> ( select distinct query_parms.get_date('this_year_start') p_start,
> query_parms.get_date('this_year_stop') p_stop
> from dual )
> where sale_date between p_start and p_stop
>union all
> select custno, descript, 0, sale_amount
> from T,
> ( select distinct query_parms.get_date('last_year_start') p_start,
> query_parms.get_date('last_year_stop') p_stop
> from dual )
> where sale_date between p_start and p_stop
>)
>group by custno, descript
>/
>
>Once again, thank you very much for your help.
>
>Best wishes for 1999,
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 - 18:53:08 CST
 |
 |