Re: parameterized view

From: <suisum_at_ecn.ab.ca>
Date: 4 Jan 99 00:06:16 GMT
Message-ID: <369005f8.0@ecn.ab.ca>

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???

: 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

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?

: >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:

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, Received on Sun Jan 03 1999 - 18:06:16 CST