Re: Oracle7.1 - Functions in select columns

Add the following line to your package spec:

pragma restrict_references(get_more,wnds);

This PL/SQL compiler directive specifies that the function "get_more" will "Write No Database State". And yes, it's in the manual :^) (v7.1 of course).

Magnus

In article <1994Dec13.160849.12418_at_nosc.mil> vergara_at_nosc.mil (Michael P. Vergara) writes:
> In article <3chk9c$h19_at_due.uninett.no>,
> Erik Lie-Nielsen <Erik.Lie-Nielsen_at_mfr.uib.no> wrote:
> >
> >Hi,
> >We have just been upgrading our Oracle7 to ver. 7.1.3 and have been
> >looking forward to see the new features. The problem is I have no
> >manual yet and I need the feature to call PL/SQL functions in sql
> >select statements.
> >
> >I have tried a simple packaged function like this:
> >
> >function get_more (id in number) return char is
> > somedata varchar := '';
> > cursor more(fid number) is select data from child_table where id=fid;
> >begin
> > for childrec in more(id) loop
> > somedata:=somedata || childrec.data
> > end loop;
> >end get_more;
> >
> >(The contents of the function don't matter at all..)
> >
> >When I try to call my function (the way I guess it should be):
> >
> > select something,more_data(id) from main_table;
> >
> >I get the following runtime exception:
> >
> > ERROR at line 1:
> > ORA-06571: Function MORE_DATA does not guarantee not to update database.
> >
> Yes, the answer is RTM, but...even though I think I figured this out once
> before, I could not find the answer in the manual yesterday when I tried
> to tell a co-worker how to solve this very problem!
>
> I created a function to convert a time string (formated HH:MI:SS.decimal)
> into the number of elapsed seconds, but not limited to 24 hours. This
> function doesn't update the database, either, and I also received this
> very irritating message. Here is a skeletonized listing of the PL/SQL
> script I used to create the function, with just the important parts:
>
>
> ============================= Begin PL/SQL Example ============================
>
> create function ICONV (timex varchar2)
> return number is
> (PL/SQL declarations)
> .
> .
> bad_param exception;
> PRAGMA EXCEPTION_INIT(bad_param,-1722);
> -- The number specified in the EXCEPTION_INIT line is a standard
> -- ORA-prefixed error messgae number. Error message ORA-01722
> -- is for 'Invalid Number'
> begin
> .
> .
> (PL/SQL code here)
> .
> .
> if error_condition = TRUE then
> raise bad_param;
> end if;
> .
> .
> (more PL/SQL code, etc)
> .
> .
> --
> return(numsecs);
> --
> exception
> when bad_param then
> return(NULL);
> end ICONV;
> /
> ============================== End PL/SQL Example =============================
>
>
> HTH
>
> --
> ============================================================================
> Mike Vergara | Be good...and you will be lonesome
> vergara_at_nosc.mil | Jimmy Buffett
> Opinions expressed are not necessarily those of anyone else but me. So there.

--

Magnus Lonnroth			E-mail: mloennro_at_se.oracle.com
Product Manager	Europe		Visit : Molndalsvagen 91, Goteborg, Sweden
Internet/Web Products		Office: +46-31-830325 Mobile: +46-70-5916325
Network Products Division	Web   : http://ubik.se.oracle.com
Oracle Corp.			Sales, Oracle Sweden