Creating a self-referencing stored function in Oracle8 ??

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. Received on Mon Feb 01 1999 - 19:41:20 CST