Re: Question: Views and Use of Stored Procedures

Frank,

Thanks for the wonderful example. It was what the doctor ordered! I read up on the Pragma Restrict_Refereces and the various switches one could pass to it.

To further extrapolate on the functionality of wrapping a Function/SP within a Package, is there a way in which a Function or Procedure can also (a) Update a Tables Column and then (b) return that columns updated result?

Again, your help is greatly appreciated. Wonderful example!

Frank wrote:
>
> Hi!
>
> Minor example;
> Look up "RESTRICT_REFERENCES ( a_function, WNDS, WNPS, RNDS, RNPS); " in
> documentation
>
> Frank
>
> CREATE OR REPLACE PACKAGE pa_package AS
> FUNCTION a_function (p_no IN NUMBER) RETURN VARCHAR2;
> PRAGMA RESTRICT_REFERENCES ( a_function, WNDS, WNPS, RNDS, RNPS);
> END pa_package;
> /
>
> CREATE OR REPLACE PACKAGE BODY pa_package AS
> FUNCTION a_function (p_no IN NUMBER) RETURN VARCHAR2
> IS
> sRet VARCHAR2(30);
> BEGIN
> IF p_no = 1
> THEN
> sRet := 'Varg i Veum';
> ELSE
> sRet := 'Something else';
> END IF;
> RETURN sRet;
> END a_function;
> END pa_package;
> /
>
> select pa_package.a_function(1)
> from dual;
>
> CREATE OR REPLACE VIEW v_a_view AS
> SELECT pa_package.a_function(1) the_first_columns
> FROM dual
> UNION
> SELECT pa_package.a_function(2) the_first_columns
> FROM dual;
>
> SELECT * FROM v_a_view;
Received on Sat Apr 14 2001 - 15:27:22 CDT