Re: Function does not guarantee... using DBMS

From: Hakan <heren_at_home.com>
Date: 2000/07/24
Message-ID: <397C9E7C.FFC22536@home.com>#1/1

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> /

Statement processed.
SQLWKS> set serveroutput on

Server Output                   ON
SQLWKS> create table temp
     2> (type varchar2(10))
     3> /

Statement processed.
SQLWKS> insert into temp values ('CRE_DEL')

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> /

Statement processed.
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

Re: Function does not guarantee... using DBMS_SQL package.