Plsql help..

[newplsql]

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:

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:

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

[newplsql]

I've created this below procedure... But, struggling on writing the message field..

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

[PAVB]

so... are you asking for somebody to do your job?

I'll gladly address specific questions, what do you have so far?

[LKBrwn_DBA]

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.

[newplsql]

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..

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

[gandolf989]

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.

Exception
   When Others Then
      Rollback;
      dbms_output.put_line(Sqlerrm(Sqlcode));

But otherwise it probably won't blow up your database.

[gandolf989]

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.

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;
/