| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: User defined Function used in a select
How about sharing the error codes/messages with us?
A copy of this was sent to bmennell_at_my-dejanews.com (if that email address didn't require changing) On Thu, 16 Jul 1998 16:10:10 GMT, you wrote:
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jul 16 1998 - 12:36:10 CDT
 |
 |