Re: User defined Function used in a select

A stand-alone or packaged function called from SQL has these restrictions:

• The function must not write to the database (Insert, Update, Delete, Commit, Rollback), either directly or indirectly by calling another routine. Any routine called by the function must also assert an appropriate purity level.
• Only functions referenced in a SELECT, VALUES, or SET clause can write to packaged variables. If a function reads or writes the values of packaged variables, it can't be executed in parallel, or remotely.
• The function must not call another routine, or reference a view, which violates any of these purity rules.
• The function must return a valid SQL datatype. Some datatypes that are valid in PL/SQL are not valid in SQL; for example, BOOLEAN.

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.

bmennell_at_my-dejanews.com wrote in article <6ol8l3$sdc$1_at_nnrp1.dejanews.com>...
> Problem in using a function in a SELECT clause.
>
> I have implemented a packaged function that I want to use in a query
select
> and must therefore have pragma restrictions WNPS and RNDS, which I think
are
> sufficient.
>
> The function works fine when the query is executed in SQL*Plus with the
query
> output echoed to the screen. But it does not work if an attempt is made
to
> assign the query to a host variable.
>
> The same applies if the function is used directly to assign a host
variable.
>
> My code looks like:
>
> ---- package spec---------------------
> CREATE OR REPLACE PACKAGE CL
> AS
>
> FUNCTION get_level(
> in_hierarchy varchar2,
> in_class varchar2
> ) return integer;
>
> PRAGMA RESTRICT_REFERENCES(get_level, WNDS);
>
> END; -- Package spec
>
>
>
> Direct assignment:
> Result: error
>
> host_var := CL.get_level( 'FRED', 'JOHN');
>
>
> Assignment of query to host variable:
> Result: error
>
> SELECT CL.get_level(hierarchy, class) -- hierarchy, class are
> columns of TAB INTO host_var FROM TAB WHERE ....;
>
>
> However, this statement has no problems (but is useless)
>
> SELECT CL.get_level( 'FRED', 'JOHN')
> INTO host_var
> FROM dual;
>
>
> Has anyone encountered a similar problem and/or had a solution for this?
>
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
Received on Thu Jul 16 1998 - 23:36:20 CDT