-
Plsql help..
Hi all,
I need some help from you experts on this stored procedure..
There are 4-5 batch procedures that will call this stored procedure. The batch will call the stored procedure with 'I' parameter in the beginning of the batch process. The batch will call the stored procedure with 'O' parameter at the end of the batch process. The stored procedure should write the details into the auditing table.
Example record in acfg table:
Code:
ID Mand Bcode Skey I_O Tname Cname Func Group_col1 WhereSQL Message
1 M1 P1 1 O Table1 COUNTRY COUNT COUNTRY %1 entries found for %2
2 M1 P1 2 O Table1 FIRSTNAME COUNT %1 total lines
3 M1 P2 1 I Table1 FIRSTNAME COUNT %1 lines found
The logic of the stored procedure for the example is:
*1*. If acfg.v_yn is 'Y'
Read the record in acfg table using mand=pi_mand and bcode = pi_bcode order by skey;
*a*. if I_O parameter is 'I' then:
Fetch the records from step 1).
Generate a dynamic SQL like select func(cname) from tname where mand= pi_mand; /* sql should also include acfg.wheresql in the
where clause, sql should also include acfg.group_col1,acfg.group_col2, acfg.group_col3 if there are any values. */
Executing this above sql calculates count(firstname) from table1 where mand='M1';
Fetch the output of the above query and using the acfg.message field for that row '%1 lines found' insert/update this message into auditing.message as '32 lines
found'
Assign skey for the auditing table, based on the auditing.ccode.
*b*. if I_O parameter is 'O' then:
Fetch the records from step 1).
Generate a dynamic SQL like select func(cname) from tname where mand= pi_mand and auditing.pid=pi_pid group by group_col1; /* pi_pid is the argument passed in
the SP. sql should also include acfg.wheresql in the where clause, sql should also include acfg.group_col2, acfg.group_col3 if there are any values. */
Executing this above sql calculates count(country) from table1 where mand='M1' and auditing.pid=12345 group by country;
Fetch the output of the above query and using the acfg.message field for that row '%1 entries found for %2' insert/update this message into auditing.message as
'20 entries found for Germany' '10 entries found for USA' '2 entries found for Denmark' /* can be more than 1 record */
Assign skey for the auditing table, based on the auditing.ccode for these 3 records.
The resulting record in the auditing table will look like:
Code:
ID Cdate Ccode Pid Mand Skey Message
1 01.04.2009 08:30:00 P1 12345 M1 1 20 entries found for Germany
2 01.04.2009 08:30:01 P1 12345 M1 2 10 entries found for USA
3 01.04.2009 08:30:02 P1 12345 M1 3 2 entries found for Denmark
4 01.04.2009 08:30:03 P1 12345 M1 4 32 total lines
5 01.04.2009 08:31:00 P2 12346 M1 1 32 lines found
I would appreciate any kind of help with this... Please help me.. Thanks a lot
-
I've created this below procedure... But, struggling on writing the message field..
Code:
create table auditing (
id number(5) not null,
cdate date,
ccode varchar2(10),
cterm varchar2(255),
pid number(20),
mand varchar2(10),
skey number(10),
message varchar2(2000),
source varchar2(255),
source_YN varchar2(1)
);
create table acfg (
id number(10) not null,
v_YN varchar2(1),
mand varchar2(10),
bcode varchar2(10),
skey number(10),
i_o varchar2(3),
tname varchar2(255),
cname varchar2(255),
func varchar2(64),
group_col1 varchar2(255),
group_col2 varchar2(255),
group_col3 varchar2(255),
message_id number(10),
wheresql varchar2(2000),
message varchar2(2000)
);
insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,group_col1,message)
values (1,'M1','P1',1,'o','table1','country','count','country','%1 entries found for %2');
insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
values (2,'M1','P1',2,'o','table1','firstname','count','%1 total lines');
insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
values (3,'M1','P2',1,'i','table1','firstname','count','%1 lines found');
insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
values (4,'M1','P2',2,'o','table2','firstandlastname','count','%1 lines written');
CREATE SEQUENCE audseq
MinVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
inCREMENT BY 1
CACHE 20;
Create OR Replace Procedure auditingsp( pi_pid IN Number
, pi_mand IN Varchar2
, pi_bcode IN Varchar2
, pi_opp IN Number
, pi_I_O IN Varchar2
, pi_Source IN Varchar2 )
As
Cursor aud_cur( pi_mand IN Varchar2
, pi_I_O IN Varchar2
, pi_bcode IN Number ) Is
SELECT v_yn
, message
FROM acfg
WHERE mand = pi_mand
AND (pi_I_O = 'I')
OR (pi_I_O = 'O' AND bcode=pi_bcode);
Type acfg_tab Is Table Of aud_cur%Rowtype;
acfg_array acfg_tab;
Begin
Open aud_cur( pi_mand
, pi_I_O
, pi_bcode);
Loop
Fetch aud_cur Bulk Collect
INTO acfg_array Limit 1000;
Exit When aud_cur%Notfound;
For i IN acfg_array.First .. acfg_array.Last Loop
If upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'I' THEN
INSERT INTO auditing
( ID
, cdate
, ccode
, pid
, mand
, skey
, Source
, message )
VALUES
( audseq.Nextval
, Sysdate
, pi_bcode
, pi_pid
, pi_mand
, ?????? /* skey needs to be assigned based on the auditing.ccode */
, pi_Source
, ?????? /* this should be step a) from my previous reply,Build a dynamic SQL like select
func(cname) from tname where mand= pi_mand; /* sql should also include acfg.wheresql in the where clause, sql should also
include acfg.group_col1,acfg.group_col2, acfg.group_col3 if there are any values. Execute the sql and using the
acfg.message for that row, insert into auditing.message field */
);
Elsif upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'O' THEN
INSERT INTO auditing
( ID
, cdate
, ccode
, pid
, mand
, skey
, Source
, message )
VALUES
( audseq.Nextval
, Sysdate
, pi_bcode
, pi_pid
, pi_mand
, ?????? /* skey needs to be assigned based on the auditing.ccode */
, pi_Source
, ?????? /* this should be step b) from my previous reply,build a dynamic SQL like select
func(cname) from tname where mand= pi_mand and auditing.pid= pi_pid group by group_col1; pi_pid is the argument passed in
the SP. sql should also include acfg.wheresql in the where clause, sql should also include acfg.group_col2, acfg.group_col3
if there are any values.Execute the sql and using the acfg.message for that row, insert into auditing.message field */
);
Elsif upper(acfg_array(i).v_yn) = 'N' THEN
dbms_output.put_line('Data is ignored');
End If;
End Loop;
End Loop;
Close aud_cur;
Commit;
Exception
When Others Then
Rollback;
dbms_output.put_line(Sqlerrm(Sqlcode));
End auditingsp;
I would appreciate any kind of help.. Please give me your thoughts.
Thank you very much
-
so... are you asking for somebody to do your job?
I'll gladly address specific questions, what do you have so far?
-
Student or what?
It seems you are either 1) a student with a big homework assignment (or project) or 2) you have deceived your employer into thinking you know PL/SQL but really have no clue.
If the case is "1) a student", then in order to learn something, you should actually try to do the work yourself and request assistance when you really need it.
If the case is "2) a deceiver", then you should resign out of moral/ethical (and maybe legal) grounds.
-
I've been trying to post this procedure, that I've tried but for some reason it is not getting added to my post...
I just need help in writing the message into the auditing table..
Code:
Create OR Replace Procedure auditingsp( pi_pid IN Number
, pi_mand IN Varchar2
, pi_bcode IN Varchar2
, pi_opp IN Number
, pi_I_O IN Varchar2
, pi_Source IN Varchar2 )
As
Cursor aud_cur( pi_mand IN Varchar2
, pi_I_O IN Varchar2
, pi_bcode IN Number ) Is
SELECT v_yn
, message
FROM acfg
WHERE mand = pi_mand
AND (pi_I_O = 'I')
OR (pi_I_O = 'O' AND bcode=pi_bcode);
Type acfg_tab Is Table Of aud_cur%Rowtype;
acfg_array acfg_tab;
Begin
Open aud_cur( pi_mand
, pi_I_O
, pi_bcode);
Loop
Fetch aud_cur Bulk Collect
INTO acfg_array Limit 1000;
Exit When aud_cur%Notfound;
For i IN acfg_array.First .. acfg_array.Last Loop
If upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'I' THEN
INSERT INTO auditing
( ID
, cdate
, ccode
, pid
, mand
, skey
, Source
, message )
VALUES
( audseq.Nextval
, Sysdate
, pi_bcode
, pi_pid
, pi_mand
, ?????? /* skey needs to be assigned based on the auditing.ccode */
, pi_Source
, ?????? /* this should be step a) from my previous reply,Build a dynamic SQL like select
func(cname) from tname where mand= pi_mand; /* sql should also include acfg.wheresql in the where clause, sql should also
include acfg.group_col1,acfg.group_col2, acfg.group_col3 if there are any values. Execute the sql and using the
acfg.message for that row, insert into auditing.message field */
);
Elsif upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'O' THEN
INSERT INTO auditing
( ID
, cdate
, ccode
, pid
, mand
, skey
, Source
, message )
VALUES
( audseq.Nextval
, Sysdate
, pi_bcode
, pi_pid
, pi_mand
, ?????? /* skey needs to be assigned based on the auditing.ccode */
, pi_Source
, ?????? /* this should be step b) from my previous reply,build a dynamic SQL like select
func(cname) from tname where mand= pi_mand and auditing.pid= pi_pid group by group_col1; pi_pid is the argument passed in
the SP. sql should also include acfg.wheresql in the where clause, sql should also include acfg.group_col2, acfg.group_col3
if there are any values.Execute the sql and using the acfg.message for that row, insert into auditing.message field */
);
Elsif upper(acfg_array(i).v_yn) = 'N' THEN
dbms_output.put_line('Data is ignored');
End If;
End Loop;
End Loop;
Close aud_cur;
Commit;
Exception
When Others Then
Rollback;
dbms_output.put_line(Sqlerrm(Sqlcode));
End auditingsp;
I would appreciate any kind of help.. Please give me your thoughts.
Thank you very much
Edited by: newbiegal on Jul 8, 2009 6:33 AM
-
Take a look at this:
http://download.oracle.com/docs/cd/B...a96624/toc.htm
Then buy and read PL/SQL best practices by Steven Feuerstein.
Specific tips about your procedure: use implicit cursors when you want to read every record in a cursor or better yet learn about pl/sql collections, "or" clauses tend to be expensive, can you do without it, you have missing business logic that no one outside your company will know, you need to learn how to create and use packages, use functions to encapsulate business logic, and read up on exception handling.
This is a terrible idea, removing this code would give you more information than leaving it in place.
Code:
Exception
When Others Then
Rollback;
dbms_output.put_line(Sqlerrm(Sqlcode));
But otherwise it probably won't blow up your database. :rolleyes:
-
I don't quite get your logic, but here is a first draft of something that might be close to what you want. You will need to fill in the details and test.
Code:
Create OR Replace Procedure auditingsp(
pi_bcode IN Varchar2 )
AS
TYPE acfg_tab IS TABLE OF aud_cur%Rowtype;
acfg_array acfg_tab;
BEGIN
SELECT id, v_YN, mand, bcode, skey, i_o,
tname, cname, func, group_col1,
group_col2, group_col3, message_id,
wheresql, message
BULK COLLECT INTO acfg_array
FROM acfg
INNER JOIN table1
ON
WHERE mand = pi_mand
AND pi_I_O IN ('I', 'O')
AND bcode=pi_bcode;
FOR i IN acfg_array.FIRST..acfg_array.LAST
LOOP
IF upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'I'
THEN
INSERT INTO auditing
( ID, cdate, ccode,
pid, mand,
skey, Source, message )
VALUES
( audseq.Nextval, SYSDATE, i.bcode,
i.pid, i.mand, i.skey,
i.Source, i.country )
ELSE
dbms_output.put_line('Data is ignored');
END IF;
END LOOP;
COMMIT;
END auditingsp;
/
-
Hi Gandolf,
Stored procedure is created for auditing records in the batch tables. Batch process calls the Stord procedure at the beginning of the batch with 'i' parameter(Only the acfg.i_o='i' records should be audited) and the batch calls the stored procedure with 'o' parameter at the end of the batch(Only the acfg.i_o='o' records should be written to the auditing table. So, the stored procedure writes the message(by evaluating with acfg table) into the auditing table pre batch and post batch. There will be 2 conditions in the procedure:
1. Only if acfg.v_yn ='Y', record should be inserted into auditing table. if acfg.v_yn ='N', data should be ignored.
2. Read the records in acfg table(cursor aud_cur in the Stored procedure) order by mand,bcode,i_o,skey.
3. If pi_i_o = 'i' then
a. Build sql evaluating the records in acfg table like
select func(cname) from tname where mand = pi_mand and
wheresql ='..' group by group_col1,group_col2,
group_col3 /* wheresql should be considered when there
is a where clause in tname for example if there is an
expression customer=3 in the wheresql. group_col1,
group_col2, group_col3 should also be considered when
there is an expression. */
b. Execute the sql, fetch the output in acfg.message and
insert the message into auditing.message field
4. If pi_i_o= 'o' then
a. Build sql evaluating the records in acfg table like
select func(cname) from tname where mand = pi_mand and
pid = pi_pid wheresql ='..' group by group_col1,
group_col2,group_col3 /* wheresql should be considered
when there is a where clause in tname for example if
there is an expression customer=3 in the wheresql.
group_col1,group_col2, group_col3 should also be
considered when there is an expression. */
b. Execute the sql, fetch the output in acfg.message and
insert the message into auditing.message field.
I created this procedure below, it works but when I try with the condition for 'i' and 'o' parameters it doesnt work..
Code:
create table auditing (
id number(5) not null,
cdate date,
ccode varchar2(10),
cterm varchar2(255),
pid number(20),
mand varchar2(10),
skey number(10),
message varchar2(2000),
source varchar2(255),
source_YN varchar2(1)
);
create table acfg (
id number(10) not null,
v_YN varchar2(1),
mand varchar2(10),
bcode varchar2(10),
skey number(10),
i_o varchar2(3),
tname varchar2(255),
cname varchar2(255),
func varchar2(64),
group_col1 varchar2(255),
group_col2 varchar2(255),
group_col3 varchar2(255),
message_id number(10),
wheresql varchar2(2000),
message varchar2(2000)
);
insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,group_col1,message)
values (1,'M1','P1',1,'o','table1','country','count','country','%1 entries found for %2');
insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
values (2,'M1','P1',2,'o','table1','firstname','count','%1 total lines');
insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
values (3,'M1','P2',1,'i','table1','firstname','count','%1 lines found');
create table table1
(country varchar2(30),
firstname varchar2(30),
mand varchar2(10),
pid number(20)
);
CREATE SEQUENCE audseq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 2;
CREATE OR REPLACE PROCEDURE auditingsp
( pi_pid IN auditing.pid%TYPE
, pi_mand IN acfg.mand%TYPE
, pi_bcode IN acfg.bcode%TYPE
, pi_opp IN NUMBER
, pi_i_o IN acfg.i_o%TYPE
, pi_source IN auditing.source%TYPE )
AS
CURSOR aud_cur IS
SELECT *
FROM acfg
WHERE mand = pi_mand
AND bcode = pi_bcode
AND ((UPPER (pi_I_O) = 'I')
OR (UPPER (pi_I_O) = 'O'))
ORDER BY skey;
TYPE acfg_tab IS TABLE OF aud_cur%ROWTYPE;
acfg_array acfg_tab;
i_sql VARCHAR2 (32767);
c1 SYS_REFCURSOR;
v_num NUMBER;
v_col VARCHAR2 (30);
BEGIN
OPEN aud_cur;
LOOP
FETCH aud_cur BULK COLLECT INTO acfg_array LIMIT 1000;
EXIT WHEN acfg_array.COUNT = 0;
FOR i IN 1 .. acfg_array.COUNT LOOP
i_sql :=
'SELECT ' || acfg_array(i).func || '(*) num, '
|| acfg_array(i).cname || ' col'
|| ' FROM ' || acfg_array(i).tname
|| ' WHERE mand = :pi_mand'
|| ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
|| ' GROUP BY ' || acfg_array(i).cname
|| ' ORDER BY num DESC';
OPEN c1 FOR i_sql USING pi_mand;
LOOP
FETCH c1 INTO v_num, v_col;
EXIT WHEN c1%NOTFOUND;
INSERT INTO auditing
(id, cdate, ccode, pid, mand, skey, source, message)
SELECT audseq.NEXTVAL, SYSDATE, acfg_array(i).bcode, pi_pid,
acfg_array(i).mand, acfg_array(i).skey, pi_source,
REPLACE (REPLACE (acfg_array(i).message, '%1', v_num), '%2', v_col)
FROM DUAL;
END LOOP;
CLOSE c1;
END LOOP;
END LOOP;
CLOSE aud_cur;
END auditingsp;
/
SQL>EXEC auditingsp (12345, 'M1', 'P1', 0, 'i', 'source1')
PL/SQL procedure successfully completed.
SQL>SELECT id, cdate, ccode, pid, mand, skey, message FROM auditing;
ID CDATE CCODE PID MAND SKEY MESSAGE
---------- --------- ---------- ---------- ---- ---------- ------------------------------
1 09-JUL-09 P1 12345 M1 1 20 entries found for Germany
2 09-JUL-09 P1 12345 M1 1 10 entries found for USA
3 09-JUL-09 P1 12345 M1 1 2 entries found for Denmark
4 09-JUL-09 P2 12345 M1 1 32 lines found
5 09-JUL-09 P1 12345 M1 2 32 total lines
But the sort key needs to be assigned in a sequence for every ccode,pid and mand. Like:
Code:
ID CDATE CCODE PID MAND SKEY MESSAGE
---------- --------- ---------- ---------- ---- ---------- ------------------------------
1 09-JUL-09 P1 12345 M1 1 20 entries found for Germany
2 09-JUL-09 P1 12345 M1 2 10 entries found for USA
3 09-JUL-09 P1 12345 M1 3 2 entries found for Denmark
4 09-JUL-09 P2 12345 M1 4 32 lines found
5 09-JUL-09 P1 12345 M1 2 32 total lines
-
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.
-
Try it.
Try this:
Code:
CREATE -- etc --
-- etc --
i PLS_INTEGER;
BEGIN
-- etc --
This statement seems to be out of order:
Code:
-- etc --
29 If upper(acfg_array(i).v_yn) = 'Y' THEN
30 If UPPER (pi_i_o) = 'I' THEN
31 FOR i IN 1 .. acfg_array.COUNT LOOP
-- etc --
;)
