Re: HELP: pb with PRAGMA RESTRICT_REFERENCES...

A copy of this was sent to monsri_at_my-deja.com (if that email address didn't require changing) On Thu, 21 Oct 1999 12:51:44 GMT, you wrote:

>It looks like it's impossible to create a function containing DML
>statements. When I can compile, I get the following message when
>executing:
>
># select list_cu_lst_tmp(cell_id) from cu_lst_tmp
># *
># ERROR at line 1:
># ORA-06571: Function LIST_CU_LST_TMP does not guarantee not to
># update database
>
>or the following at compiling (when I remove the proper PRAGMA option):
>
># Errors for PACKAGE BODY CALC_CU_LST:
># LINE/COL ERROR
># -------- ---------------------------------------------------
># 0/0 PL/SQL: Compilation unit analysis terminated
># 3/1 PLS-00452: Subprogram 'LIST_CU_LST_TMP' violates its
># associated pragma
>
>I feel like I'm a in a deadly embrace (either I add the PRAGMA
>stuff, and cannot execute -messsage 6571- whatever options I try
>anyway, or I can't compile...)
>

the primary restriction on calling a function from SQL in versions 8.0 and before is that "the function CANNOT modify the database (WNDS)".

Your function list_cu_lst_tmp violates this rule in 2 places

1. it calls COMMIT. that writes to the database state
2. it calls del_from_cu_lst_tmp. del_from_cu_lst_tmp DELETES. That writes to the database state.

In Oracle8i, release 8.1, there is a feature called an autonomous transaction (see the URL in my signature for a paper on that feature) that allows in some proper cases a function to write the database state and still be callable from SQL. As it stands -- your function violates the pragma it is asserting (WNDS) and there is no way to call this function from SQL as you are.

Do you need to call it from SQL? Cannot you just submit:

begin for x in ( select * from cu_lst_tmp ) loop list_cu_lst_tmp(x.cell_id); end loop; end;

as easily?

>Did anybody succeed in creating once a function that works with
>PRAGMAs ?
>
>Thanks a lot !
>
>(below the entire text of my function: as you'll see even when I
> removed the DELETE statement from the function body to put it in
> the procedure "del_from_cu_lst_tmp(ci_p NUMBER)", I still had
> exactly the same behaviour)
>
> 1 CREATE OR REPLACE PACKAGE calc_cu_lst AS
> 2 FUNCTION list_cu_lst_tmp(ci_p NUMBER) RETURN VARCHAR2;
> 3 PRAGMA RESTRICT_REFERENCES (list_cu_lst_tmp, WNDS, WNPS,RNDS);
> 4 PROCEDURE del_from_cu_lst_tmp(ci_p NUMBER);
> 5 END calc_cu_lst;
> 6 /
> 7
> 8 CREATE OR REPLACE PACKAGE BODY calc_cu_lst AS
> 9
> 10 function list_cu_lst_tmp (ci_p number) return varchar2 as
> 11 -- declare
> 12 -- to be used this way: 'select cell_id,
> -- list_cu_lst_tmp(cell_id)
> 13 -- from cu_lst_tmp
> 14 -- group by cell_id;'
> 15 --
> 16 res varchar2(2000) := null;
> 17 first boolean := true;
> 18 begin
> 19 select initial_frequency||' / ' into res
> 20 from bcch_tmp
> 21 where cell_id = ci_p;
> 22 --
> 23 calc_cu_lst.del_from_cu_lst_tmp(ci_p);
> 24 --delete from cu_lst_tmp a
> 25 --where a.cell_id = ci_p;
> 29 [...]
> 30 commit;
> 31 --
> 32 for curs in (select initial_frequency
> 33 from cu_lst_tmp
> 34 where cell_id = ci_p
> 35 order by initial_frequency) loop
> 36 if not first then
> 37 res := res || ' / ';
> 38 else
> 39 first := false;
> 40 end if;
> 41 res := res || curs.initial_frequency;
> 42 end loop;
> 43 return res;
> 44 end list_cu_lst_tmp;
> 45
> 46 PROCEDURE del_from_cu_lst_tmp(ci_p NUMBER) as
> 47 begin
> 48 delete from cu_lst_tmp a
> 49 where a.cell_id = ci_p;
> 50 end del_from_cu_lst_tmp;
> 51
> 52 end calc_cu_lst;
> 53 /
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Oct 21 1999 - 09:57:43 CDT