-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
-
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;
/
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
|