Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> HELP: pb with PRAGMA RESTRICT_REFERENCES...
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...)
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 --
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;
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Oct 21 1999 - 07:51:44 CDT