Re: Table columns and User defined functions...

Yes, you can do this but when you define your package you need a PRAGMA statement in the package spec. Something along the lines of

PRAGMA RESTRICT_REFERENCES(GetLocalTime, WNDS);

immediately after the function definition.

IIRC this tells Oracle that your function won't change any data when it's called so Oracle can trust it not to screw up the select. If GetLocalTime calls any other functions these must also be defined in this way and so on.

Simon

Bill Lucas wrote:
>
> Hello all,
>
> Recently we have been noticing an issue using user defined functions.
> Whenever we try to pass a table column in a select statement into the UDF we
> are getting an error. I think I remember reading something about this but
> it wasn't very informative. I tried googling for information but came up
> empty.
>
> So my question is. Can you use a User defined function and pass a column in
> a table in as a parameter?
>
> Here is an example.
>
> SELECT Package.GetLocalTime(LastModified) INTO dtLclLstMod FROM Table WHERE
> Foo = 'Bar'
>
> I can provide more detailed info about our particular function on request.
>
> Any help on this appreciated. If it makes a difference we are building XML
> from the results of select statements...
>
> Thanks for any information.
>
> Regards,
> Bill
Received on Fri Feb 28 2003 - 09:35:40 CST