Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP: pb with PRAGMA RESTRICT_REFERENCES...

Re: HELP: pb with PRAGMA RESTRICT_REFERENCES...

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 21 Oct 1999 16:35:39 +0200
Message-ID: <7un8fg$int$1@oceanite.cybercable.fr>


It seems to me that your problem is in your pragma statement. RNDS is not correct (Read No Database State) because you select. I think you would use RNPS (Read No Package State) instead.

--
Have a nice day
Michel

<monsri_at_my-deja.com> a écrit dans le message : 7un28s$umd$1_at_nnrp1.deja.com...
> 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 --
> 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.
Received on Thu Oct 21 1999 - 09:35:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US