HELP: pb with PRAGMA RESTRICT_REFERENCES...

From: <monsri_at_my-deja.com>
Date: Thu, 21 Oct 1999 12:53:59 GMT
Message-ID: <7un2d1$une$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 - 14:53:59 CEST