Re: Creating a self-referencing stored function in Oracle8 ??

David Pattinson schrieb:
>
> Hi all,
>
> We are migrating from SQL Anywhere to Oracle8 at the moment
> and I have several self-referencing stored functions to
> convert. Oracle won't let me create the function as it is
> self-referencing
> "f_getEntityDescription('company',COMPANYCODE)". I'm pretty
> sure I can just create a 'dummy' version and then ALTER it
> to the real thing, however this will be a bit of a pain to
> script. Does anlyone know if Oracle supports the creation of
> this sort of function in a more elegant way? In SQLA
> external references are not checked until they are called,
> so we didn't have this problem.
>
> I've inlined an example below of the SQLA script:
>
> create function "DBA".f_getEntityDescription(in
> @ENTITYTYPE char(10),in @ENTITYID integer)
> returns char(255)
> begin
> declare @VALUE char(255);
> case @ENTITYTYPE when 'company' then
> select
> COMPANYNAME+IFNULL(COMPANYDEPARTMENT,'','
> ('+COMPANYDEPARTMENT+')')+' ['
> +STRING(@ENTITYID)+']' into @VALUE
> from TCOMPANY
> where COMPANYCODE=@ENTITYID when 'order'
> then
> select STRING(ORDERNUMBER,':
> ',f_getEntityDescription('company',COMPANYCODE))
> into @VALUE
> from TORDER
>
> etc...
>
> Regards, David.

In order to use a function in a select-stat you must garantee that this function doesn't change any database values. This is done by declaring PRAGMA RESTRICT_REFERENCES, but this can only e done within a package. So, you've got to encapsualte your functon in a package first.

HTH
MAtthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Tue Feb 02 1999 - 02:04:51 CST