Re: Table columns and User defined functions...

Thanks Simon,

I am still very new to this and I guess I just didn't know where to look, I will read up on the PRAGMA RESTRICT_REFERENCES so I have a better understanding of what I need to do and how it effects our functions.

Thank you for your quick response, it is very appreciated.

Regards,
Bill

"Simon Hall" <Simon.Hall_at_logicacmg.com> wrote in message news:3E5F81CC.33F98DF4_at_logicacmg.com...
>
> 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:46:30 CST