Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Function does not guarantee... using DBMS_SQL package.
hi,
you cannot use it in a sql statement (select, update, delete, insert).
if you will call it in a sql-statement you need to define
the purity level or your function. Think the whole thing as your
function
(f+dbms_sql) and this does not guarantee WNDS. The following is not
possible
unless oracle changes this behaviour.
select f(...) from dual
insert into ..... where .... = f(...)
etc...
however you can call it from sql plus, any other package, etc. you don't need to assert the purity level this time.
for example the following is ok.
declare
tmp varchar2(100);
begin
tmp := f('a', 'temp');
end;
/
test run:
SQLWKS> create or replace function f(x in varchar2, 2> y in varchar2) 3> return varchar2 4> is 5> cursor_handle integer := DBMS_SQL.OPEN_CURSOR; 6> dbms_sql_feedback integer; 7> return_value varchar2(100) :=NULL; 8> begin 9> DBMS_SQL.PARSE (cursor_handle, 10> 'select distinct ' || x || 11> ' from ' || y || 12> ' where type = ''CRE_DEL''',DBMS_SQL.NATIVE); 13> DBMS_SQL.DEFINE_COLUMN (cursor_handle,1,x,100); 14> dbms_sql_feedback := DBMS_SQL.EXECUTE (cursor_handle); 15> dbms_sql_feedback := DBMS_SQL.FETCH_ROWS (cursor_handle); 16> if dbms_sql_feedback > 0 17> then 18> DBMS_SQL.COLUMN_VALUE (cursor_handle,1,return_value); 19> end if; 20> DBMS_SQL.CLOSE_CURSOR (cursor_handle); 21> return_value := 7; 22> return return_value; 23> end; 24> /
Server Output ON SQLWKS> create table temp 2> (type varchar2(10)) 3> /
2> /
1 row processed.
SQLWKS> COMMIT
Statement processed.
SQLWKS> / SQLWKS> declare 2> tmp varchar2(100); 3> begin 4> tmp := f('type', 'temp'); 5> dbms_output.put_line(tmp); 6> end; 7> /
Regards
Hakan
Jim Ley wrote:
>
> On Mon, 24 Jul 2000 18:06:28 GMT, Hakan <heren_at_home.com> wrote:
>
> >hi,
> >
> >dbms_sql is a package that does not guarantee 'WNDS'. So does your fnc.
> >'f' .
> >That's the reason.
>
> Does that mean what I am attempting is impossible? according to
> http://oradoc.photo.net/ora8doc/DOC/server803/A54642_01/ch7b.htm
> I need to use a pragma restrict_references(f,WNDS) to enable me to
> call the packaged function, so why can't I assert that my function
> whilst it uses DBMS_SQL, will not ever do any writes I'm only using it
> to perform reads.
>
> What options do I have to achieve this?
>
> Jim.
Received on Mon Jul 24 2000 - 00:00:00 CDT
![]() |
![]() |