Re: Question: Views and Use of Stored Procedures

Many thanks but please dismiss the above message. I have worked it out using:

PRAGMA AUTONOMOUS_TRANSACTION Thanks again for the help.

Ray-

Ray Vonhollen wrote:
>
> 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 Mon Apr 16 2001 - 01:05:41 CDT