User defined Function used in a select

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 - 11:10:10 CDT