|
-
I tried the below procedure with if else clauses with the conditions 'i' and 'o' parameters,
Code:
SQL> CREATE OR REPLACE PROCEDURE auditingsp
2 ( pi_pid IN auditing.pid%TYPE
3 , pi_mand IN acfg.mand%TYPE
4 , pi_bcode IN acfg.bcode%TYPE
5 , pi_opp IN NUMBER
6 , pi_i_o IN acfg.i_o%TYPE
7 , pi_source IN auditing.source%TYPE )
8 AS
9 CURSOR aud_cur IS
10 SELECT *
11 FROM acfg
12 WHERE mand = pi_mand
13 AND bcode = pi_bcode /* added */
14 AND ((UPPER (pi_I_O) = 'I')
15 OR (UPPER (pi_I_O) = 'O'))
16 ORDER BY skey;
17 TYPE acfg_tab IS TABLE OF aud_cur%ROWTYPE;
18 acfg_array acfg_tab;
19 i_sql VARCHAR2 (32767);
20 o_sql VARCHAR2 (32767); /* added */
21 c1 SYS_REFCURSOR;
22 v_num NUMBER;
23 v_col VARCHAR2 (30);
24 BEGIN
25 OPEN aud_cur;
26 LOOP
27 FETCH aud_cur BULK COLLECT INTO acfg_array LIMIT 1000;
28 EXIT WHEN acfg_array.COUNT = 0;
29 If upper(acfg_array(i).v_yn) = 'Y' THEN /* added */
30 If UPPER (pi_i_o) = 'I' THEN /* added */
31 FOR i IN 1 .. acfg_array.COUNT LOOP
32 i_sql :=
33 'SELECT ' || acfg_array(i).func || '(*) num, '
34 || acfg_array(i).cname || ' col'
35 || ' FROM ' || acfg_array(i).tname
36 || ' WHERE mand = :pi_mand'
37 || ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
38 || ' GROUP BY ' || acfg_array(i).cname
39 || ' ORDER BY num DESC';
40 OPEN c1 FOR i_sql USING pi_mand;
41 LOOP
42 FETCH c1 INTO v_num, v_col;
43 EXIT WHEN c1%NOTFOUND;
44 INSERT INTO auditing
45 (id, cdate, ccode, pid, mand, skey, source, message)
46 SELECT audseq.NEXTVAL, SYSDATE, acfg_array(i).bcode, pi_pid,
47 acfg_array(i).mand, acfg_array(i).skey, pi_source,
48 REPLACE (REPLACE (acfg_array(i).message, '%1', v_num), '%2', v_col)
49 FROM DUAL;
50 END LOOP;
51 CLOSE c1;
52 END LOOP;
53 ELSIF UPPER (pi_i_o) = 'O' THEN /* added */
54 FOR i IN 1 .. acfg_array.COUNT LOOP /* added */
55 o_sql :=
56 'SELECT ' || acfg_array(i).func || '(*) num, '
57 || acfg_array(i).cname || ' col'
58 || ' FROM ' || acfg_array(i).tname
59 || ' WHERE mand = :pi_mand'
60 || ' AND ' || 'pid = :pi_pid' /* added */
62 || ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
63 || ' GROUP BY ' || acfg_array(i).cname
64 || ' ORDER BY num DESC';
65 OPEN c1 FOR o_sql USING pi_mand, pi_pid;
66 LOOP
67 FETCH c1 INTO v_num, v_col;
68 EXIT WHEN c1%NOTFOUND;
69 INSERT INTO auditing
70 (id, cdate, ccode, pid, mand, skey, source, message)
71 SELECT audseq.NEXTVAL, SYSDATE, acfg_array(i).bcode, pi_pid,
72 acfg_array(i).mand, acfg_array(i).skey, pi_source,
73 REPLACE (REPLACE (acfg_array(i).message, '%1', v_num), '%2', v_col)
74 FROM DUAL;
75 END LOOP;
76 CLOSE c1;
77 END LOOP;
78 ELSIF upper(acfg_array(i).v_yn) = 'N' THEN
79 dbms_output.put_line('Data is ignored');
80 END if;
81 END if;
82 END LOOP;
83 CLOSE aud_cur;
84 END auditingsp;
/
I'm getting this error: PROCEDURE AUDITINGSP
On line: 30
PLS-00201: identifier 'I' must be declared
Line 30 if pi_i_o = 'I' is from the argument passed to the procedure, do I still have to declare it?
Please help me, thanks very much for your support.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|