Home » SQL & PL/SQL » SQL & PL/SQL » Create sp (10.2.0.3,windows 2003)
Create sp [message #411795] Mon, 06 July 2009 16:08 Go to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi all,

I need your help in creating a stored procedure. Since I've not used PL SQL in years, it seems to be complicated to me now.. I would appreciate any kind of help.

I created a table called auditing. This table needs to be inserted/updated via the SP by comparing/evaluating with another table acfg. Data in acfg will be entered by the users. Sample Data of 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
 


SQL>desc auditing;

 Name                       Null?                              Type
 --------------------     --------                 -------------------------
 ID		           NOT NULL                         NUMBER(5)
 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)



The stored procedure auditingsp will be called with these input parameters (pid, mand, bcode, opp, I_O, source).

*1*. SP should insert record into auditing table.

*2.* If acfg.source_yn is 'Y' (compare with mand=acfg.mand, bcode=acfg.bcode) following logic should be used:
SP should generate a dynamic sql from the a.cfg table
select func(column_name) from tname where mand='...' (If I_O='I')
select func(column_name) from tname where mand='...' and pid='...' (If I_O = 'O')
So, query for the 1st record in acfg table should be select count(country) from table1 where mand='M1' and pid='..' group by country;

Note: group_col1, group_col2, group_col3 are optional. But should be considered in the select query when NOT NULL.

*3.* The output of the above query should be written to auditing.message field by filling in the result of the above query in the acfg.message field.

In this example, acfg.message has this value: %1 entries found for %2. So %1 and %2 should be filled in from the output of the above query. The auditing.message should have this
value 20 entries found for Germany.

For the acfg table above, the auditing table should be inserted/updated like this:

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


*4.* Also, skey in auditing table should be assigned for every I or O call based on mand and bcode in the SP.


Please give me your suggestions on this for steps 2,3 and 4.. I'm not sure if I need to use cursors for step 2 & 3.

CREATE OR REPLACE procedure PUBS.auditingsp (pid IN number, mand IN varchar2, bcode IN varchar2, opp IN number, source IN varchar2)
IS 
BEGIN
declare
seqno number(38);
createcode varchar2(10);

begin
select audseq.nextval into seqno from dual ;

   insert into auditing (ID,cdate,pid,mand,source)
    values(seqno,sysdate,pid,mand,source);

/* steps 2,3,4 */
  
end;
end auditingsp;
/


Thanks a lot for all your help

[Updated on: Mon, 06 July 2009 16:10]

Report message to a moderator

Re: Create sp [message #411798 is a reply to message #411795] Mon, 06 July 2009 18:29 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
You mention all of the following & you need to provide details

SQL> DESC acfg
SQL> DESC TABLE1
SQL> DESC TNAME
Re: Create sp [message #411968 is a reply to message #411795] Tue, 07 July 2009 06:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
2) I wouldn't use dynamic SQL for this. By the looks of it you only have two conditions, so I'd use an IF... ELSE..END IF and simply pick one of two different pieces of SQL.

Unless the SQL is over a page long, I'd just use inline SQL rather than cursors for this.

3) This just sounds like you need to fetch the row(s) returned by the SQL in 2) and do either an insert or an update of the audit table
Re: Create sp [message #412040 is a reply to message #411795] Tue, 07 July 2009 08:52 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Thanks for your replies.

Blackswan,

I wouldnt know SQL> DESC TABLE1 and SQL> DESC TNAME, I just have to generate a sql from acfg table with its values..

Jrowbottom,

Yes, there are only 2 conditions, but I wouldnt know how many rows will be in acfg table and so I wouldnt know how many sqls need to be built.. Is it possible to use loop in inline sql?
Or is it a better approach compared with cursors?

Thanks for your help.

[Updated on: Tue, 07 July 2009 08:53]

Report message to a moderator

Re: Create sp [message #412082 is a reply to message #411795] Tue, 07 July 2009 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>>select func(column_name) from tname
>>select count(country) from table1
>I wouldnt know SQL> DESC TABLE1 and SQL> DESC TNAME,

Since you don't know & I don't know, the mystery remains a mystery
Re: Create sp [message #412086 is a reply to message #411795] Tue, 07 July 2009 12:56 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Blackswan,

Thatswhy I need to generate a dynamic sql, Because the table acfg can have any values.

>>select func(column_name) from tname

func,column_name, tname are columns in acfg table.

>>select count(country) from table1

this is just an example for one of the row in acfg table.

Thanks
Re: Create sp [message #412125 is a reply to message #411795] Tue, 07 July 2009 20:57 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Application team just explained to me the scenario again. 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.

Auditing record after inserted 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

Edited by: newbiegal on Jul 7, 2009 2:18 PM
Re: Create sp [message #412127 is a reply to message #411795] Tue, 07 July 2009 21:04 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Here is the test case..

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');



Thank you
Re: Create sp [message #412255 is a reply to message #412127] Wed, 08 July 2009 06:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What have you written so far - from the description you've posted,there doesn't seem to be anything to this other than IF...ELSIF.. and a cursor for loop.

Show us what you've got so far.
Re: Create sp [message #412283 is a reply to message #411795] Wed, 08 July 2009 08:38 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi JRowbottom,

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

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
Re: Create sp [message #412343 is a reply to message #412283] Wed, 08 July 2009 16:04 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Any suggestions on how I can code to insert the message field, please? I tried as below( comments with /* added */ ), but didnt work..

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;
   i_sql varchar2(2000);                                                                   /* added  */
   o_sql varchar2(2000);				  /*  added  */
   Type cur_typ IS REF CURSOR;    		                  /*  added  */   
   c1   cur_typ;                                                                             /*  added  */
  res_i_sql  varchar2(1000);                                                            /*  added   */
Begin
 
 i_sql  :=  'select func(cname) from tname where                                           /* added */   /* But, the tname is a column in acfg table, not sure how i can interpret that in this query.... */  
              mand=pi_mand and wheresql IS NOT NULL 
              group by group_col1, group_col2, group_col3';                        
                                                                                                                                                             
   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;
   
  open c1 for i_sql USING mand;                                                                 /* added */
   LOOP                                                                                                 /* added */
       fetch c1 into res_i_sql;                                                                      /* added */ 
       exit when c1%NOTFOUND;                                                               /* added */

      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
               , ??????                /*  added */                            /* Is it possible to insert the message here using the output of res_i_sql???  */
               );

         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've updated the procedure just for the if 'i' parameter , if I can get that work I'll write for 'o'. I'm doing a lot of research and learning things, but since I've a timeline, I request if someone can help me that would be such a great help to me..

Thanks a lot again

Re: Create sp [message #412356 is a reply to message #411795] Wed, 08 July 2009 20:20 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Anyone??? Help please..
Re: Create sp [message #412357 is a reply to message #411795] Wed, 08 July 2009 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>*2.* If acfg.source_yn is 'Y'
No work needs to occur because above is never true based upon what you have posted.
Re: Create sp [message #412401 is a reply to message #412356] Thu, 09 July 2009 03:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Some of what you posted doesn't match, some information is missing, and some of your requirements are unclear, so the following is not complete, but it should give you the general idea and demonstrate how to generate your message, which seems to be the main thing you are struggling with.

-- acfg table you provided and original data you provided:
SCOTT@orcl_11g> create table acfg (
  2  	     id number(10) not null,
  3  	     v_YN varchar2(1),
  4  	     mand varchar2(10),
  5  	     bcode varchar2(10),
  6  	     skey number(10),
  7  	     i_o varchar2(3),
  8  	     tname varchar2(255),
  9  	     cname varchar2(255),
 10  	     func varchar2(64),
 11  	     group_col1 varchar2(255),
 12  	     group_col2 varchar2(255),
 13  	     group_col3 varchar2(255),
 14  	     message_id number(10),
 15  	     wheresql varchar2(2000),
 16  	     message varchar2(2000)
 17  	     )
 18  /

Table created.

SCOTT@orcl_11g> begin
  2    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,group_col1,message)
  3    values (1,'M1','P1',1,'o','table1','country','count','country','%1 entries found for %2');
  4  
  5    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
  6    values (2,'M1','P1',2,'o','table1','firstname','count','%1 total lines');
  7  
  8    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
  9    values (3,'M1','P2',1,'i','table1','firstname','count','%1 lines found');
 10  end;
 11  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN mand	  FORMAT A4
SCOTT@orcl_11g> COLUMN bcode	  FORMAT A5
SCOTT@orcl_11g> COLUMN i_o	  FORMAT A3
SCOTT@orcl_11g> COLUMN tname	  FORMAT A6
SCOTT@orcl_11g> COLUMN cname	  FORMAT A16
SCOTT@orcl_11g> COLUMN func	  FORMAT A5
SCOTT@orcl_11g> COLUMN group_col1 FORMAT A10
SCOTT@orcl_11g> COLUMN message	  FORMAT A23
SCOTT@orcl_11g> SELECT id, mand, bcode, skey, i_o, tname, cname, func, message
  2  FROM   acfg
  3  /

        ID MAND BCODE       SKEY I_O TNAME  CNAME            FUNC  MESSAGE
---------- ---- ----- ---------- --- ------ ---------------- ----- -----------------------
         1 M1   P1             1 o   table1 country          count %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


-- additional table (table1) and data:
SCOTT@orcl_11g> CREATE TABLE table1
  2    (country   VARCHAR2 (30),
  3  	firstname VARCHAR2 (30),
  4  	mand	  VARCHAR2 (10))
  5  /

Table created.

SCOTT@orcl_11g> INSERT INTO table1
  2  SELECT 'Germany', 'fname', 'M1'
  3  FROM   DUAL
  4  CONNECT BY LEVEL <= 20
  5  /

20 rows created.

SCOTT@orcl_11g> INSERT INTO table1
  2  SELECT 'USA', 'fname', 'M1'
  3  FROM   DUAL
  4  CONNECT BY LEVEL <= 10
  5  /

10 rows created.

SCOTT@orcl_11g> INSERT INTO table1
  2  SELECT 'Denmark', 'fname', 'M1'
  3  FROM   DUAL
  4  CONNECT BY LEVEL <= 2
  5  /

2 rows created.


-- auditing table you provided and audseq sequence:
SCOTT@orcl_11g> create table auditing (
  2  	     id number(5) not null,
  3  	     cdate date,
  4  	     ccode varchar2(10),
  5  	     cterm varchar2(255),
  6  	     pid number(20),
  7  	     mand varchar2(10),
  8  	     skey number(10),
  9  	     message varchar2(2000),
 10  	     source varchar2(255),
 11  	     source_YN varchar2(1)
 12  	     )
 13  /

Table created.

SCOTT@orcl_11g> CREATE SEQUENCE audseq
  2  /

Sequence created.


-- procedure:
SCOTT@orcl_11g> 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	((UPPER (pi_I_O) = 'I')
 14  	 OR	 (UPPER (pi_I_O) = 'O' AND bcode = pi_bcode))
 15  	 ORDER	BY skey;
 16    TYPE acfg_tab IS TABLE OF aud_cur%ROWTYPE;
 17    acfg_array	acfg_tab;
 18    i_sql		VARCHAR2 (32767);
 19    c1		SYS_REFCURSOR;
 20    v_num		NUMBER;
 21    v_col		VARCHAR2 (30);
 22  BEGIN
 23    OPEN aud_cur;
 24    LOOP
 25  	 FETCH aud_cur BULK COLLECT INTO acfg_array LIMIT 1000;
 26  	 EXIT WHEN acfg_array.COUNT = 0;
 27  	 FOR i IN 1 .. acfg_array.COUNT LOOP
 28  	   i_sql :=
 29  		   'SELECT ' || acfg_array(i).func || '(*) num, '
 30  			     || acfg_array(i).cname || ' col'
 31  	     ||   ' FROM ' || acfg_array(i).tname
 32  	     ||   ' WHERE mand = :b_mand'
 33  	     ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
 34  	     ||   ' GROUP BY ' || acfg_array(i).cname
 35  	     ||   ' ORDER BY num DESC';
 36  	   OPEN c1 FOR i_sql USING pi_mand;
 37  	   LOOP
 38  	     FETCH c1 INTO v_num, v_col;
 39  	     EXIT WHEN c1%NOTFOUND;
 40  	     INSERT INTO auditing
 41  	       (id, cdate, ccode, pid, mand, skey, source, message)
 42  	     SELECT audseq.NEXTVAL, SYSDATE, acfg_array(i).bcode, pi_pid,
 43  		    acfg_array(i).mand, acfg_array(i).skey, pi_source,
 44  		    REPLACE (REPLACE (acfg_array(i).message, '%1', v_num), '%2', v_col)
 45  	     FROM   DUAL;
 46  	   END LOOP;
 47  	   CLOSE c1;
 48  	 END LOOP;
 49    END LOOP;
 50    CLOSE aud_cur;
 51  END auditingsp;
 52  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- execution and results:
SCOTT@orcl_11g> EXEC auditingsp (12345, 'M1', 'P1', 0, 'i', 'source1')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN message	 FORMAT A30
SCOTT@orcl_11g> SELECT id, cdate, ccode, pid, mand, skey, message
  2  FROM   auditing
  3  /

        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

SCOTT@orcl_11g>

[Updated on: Thu, 09 July 2009 03:13]

Report message to a moderator

Re: Create sp [message #412479 is a reply to message #412401] Thu, 09 July 2009 12:00 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi Barbara,

Thanks a lot for your time... great help.. That helps me a lot going forward.

Sorry, I wasnt clear with the requirements and missed information. 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.

Right now,the procedure inserts all records into the auditing table (both for 'i' and 'o') irrespective of the parameter passed to the stored procedure.. I've altered the procedure with if then else clause in the for loop for 'i' and 'o' parameters passed in the procedure.. /* added */


Table1 is the batch process table that calls the SP

create table table1 
         (country   varchar2(30),
          firstname varchar2(30),
          mand      varchar2(10),
          pid       number(20))                            /* added */           

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                                              /* added */
         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);
     o_sql		VARCHAR2 (32767);                                    /* added */
     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;         
         
    If upper(acfg_array(i).v_yn) = 'Y'  THEN                                         /* added */

        If UPPER (pi_i_o) = 'I' THEN                                                  /* added */

         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;

       ELSIF UPPER (pi_i_o) = 'O'  THEN                                                 /* added */
          FOR i IN 1 .. acfg_array.COUNT LOOP                                          /* added */

   	   o_sql :=
   		   'SELECT ' || acfg_array(i).func || '(*) num, '
  			     || acfg_array(i).cname || ' col'
   	     ||   ' FROM ' || acfg_array(i).tname
   	     ||   ' WHERE mand = :pi_mand'
             ||   ' AND ' || 'pid = :pi_pid'                                            /* added */

  	     ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
   	     ||   ' GROUP BY ' || acfg_array(i).cname
   	     ||   ' ORDER BY num DESC';
   	   OPEN c1 FOR o_sql USING pi_mand, pi_pid;
  	   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;

   ELSIF  upper(acfg_array(i).v_yn) = 'N'  THEN    
	dbms_output.put_line('Data is ignored');

   END if;
   END if;
     END LOOP;
     CLOSE aud_cur;
 END auditingsp;
/


I'm getting this error: PROCEDURE AUDITINGSP
On line: 30
PLS-00201: identifier 'I' must be declared

But Line 30, there is no identifier 'I'.. Please help me.

Thanks for your great support

Re: Create sp [message #412485 is a reply to message #412479] Thu, 09 July 2009 12:18 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Which line is line 30?

I spot at least two "i" variables in your code that are declared nowhere.
Re: Create sp [message #412495 is a reply to message #411795] Thu, 09 July 2009 13:31 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
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;
/


Line 30 if pi_i_o ='I' is from the argument that is passed to the procedure.. do I still need to declare it? Please help Thanks a lot
Re: Create sp [message #412502 is a reply to message #412495] Thu, 09 July 2009 14:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No. You need to declare the i that you use in the row before and after that.
Re: Create sp [message #412509 is a reply to message #412495] Thu, 09 July 2009 14:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You have used:

If upper(acfg_array(i).v_yn) = 'Y' THEN

before you have told it what i means by declaring:

FOR i IN

That is what the error message that you are getting is referring to. I have not checked for other errors. You should work through one error at a time. Better yet, you should build your code one tiny piece at a time, testing each piece before adding the next, so that when you get an error, there is no doubt what piece is causing it.
Re: Create sp [message #412525 is a reply to message #412479] Thu, 09 July 2009 15:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
If you only want the rows where i_o = pi_i_o, then just filter those in your cursor to begin with, so you don't have to use if and else and endif. The code below contains that and a few other modifications. It helps if you post starting data in the acfg and table1 tables, what parameter values you want to pass to the procedure, and the results that you want in the auditing table, based on those. Your provided data does not match your results. There weren't even any values provided for the v_yn column.

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    v_yn  = 'Y'
    AND    UPPER (i_o) = UPPER (pi_i_o)  
    ORDER  BY mand, bcode, i_o, skey;
  TYPE acfg_tab IS TABLE OF aud_cur%ROWTYPE;
  acfg_array       acfg_tab;
  i_sql            VARCHAR2 (32767);
  o_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 = :b_mand'
        ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
        ||   ' GROUP BY ' || acfg_array(i).cname 
        ||            ',' || NVL (acfg_array(i).group_col1, 1)
        ||            ',' || NVL (acfg_array(i).group_col2, 1)
        ||            ',' || NVL (acfg_array(i).group_col3, 1)
        ||   ' 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;
/

Re: Create sp [message #412533 is a reply to message #411795] Thu, 09 July 2009 16:19 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Thank you Barbara.

I've updated v_yn ='Y' in acfg table.

ID	V_yn	Mand	Bcode	Skey	I_O	Tname	Cname	Func	Group_col1	WhereSQL	Message
1	  Y	M1	P1	1	O	Table1	COUNTRY	COUNT	COUNTRY		%1 entries found for  %2
2	 Y	M1	P1	2	O	Table1	FIRSTNAME	COUNT			%1 total lines
3	 Y	M1	P2	1	I	Table1	FIRSTNAME	COUNT			%1 lines found
4	Y	M1	P2	2	O	Table2	FirstAndLastName	COUNT			%1 lines written



SQL>EXEC auditingsp (12345, 'M1', 'P1', 0, 'i', 'source1')

PL/SQL procedure successfully completed.

SQL> SELECT id, cdate, ccode, pid, mand, skey, message
  2  FROM   auditing
  3  /

        ID CDATE     CCODE             PID MAND       SKEY MESSAGE
---------- --------- ---------- ---------- ---- ---------- ------------------------------
         1 09-JUL-09 P1              12345 M1            1 1 entries found for USA
         2 09-JUL-09 P1              12345 M1            1 1 entries found for DENMARK
         3 09-JUL-09 P1              12345 M1            1 1 entries found for GERMANY
         5 09-JUL-09 P1              12345 M1            2 32 total lines



The auditing.skey should be assigned based on the Mand and Ccode like in a sequence.. The auditing.skey should 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



Is it possible to assign the skey using a sequence based on mand and ccode in the auditing table?

Thank you very much for your support.
Re: Create sp [message #412535 is a reply to message #411795] Thu, 09 July 2009 17:15 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Is it possible to assign the skey using a sequence based on mand and ccode in the auditing table?
Yes. Modify the procedure to meet this requirement.
Re: Create sp [message #412538 is a reply to message #412533] Thu, 09 July 2009 21:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
sant_new wrote on Thu, 09 July 2009 14:19

Is it possible to assign the skey using a sequence based on mand and ccode in the auditing table?



Yes, you could do this in various ways. You could order your cursor by mand and ccode, declare a number variable, increment by 1 each loop and use that for your sequence, comparing the new mand and ccode value to the previous ones and resetting fhe sequence for each new combination. Or, you could do a separate update using the row_number analytic function, after the procedure.

It is difficult to tell what you want since what you have posted still does not match your stated requirements. For example, there is no row in your table with (mand, bcode, i_o) = ('M1', 'P1', 'i') so if you run the procedure with those parameters it would not do anything. If you want more help you need to post a realistic example and make some attempt at the sequence problem yourself.
Re: Create sp [message #412691 is a reply to message #411795] Fri, 10 July 2009 11:58 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi Barbara,

Quote:

For example, there is no row in your table with (mand, bcode, i_o) = ('M1', 'P1', 'i') so if you run the procedure with those parameters



Not all the batch processes will call the stored procedure at the beginning(with 'i' parameter) and at the end of the batch (with 'o' parameter). So, the batch can call the stored procedure at the beginning with 'i' and doesnt necessarily have to call at the end of the batch and viceversa. Hence, I did not include (mand, bcode, i_o) = ('M1', 'P1', 'i') in the example..

I've added few more examples to the acfg table..


select * from table1;

COUNTRY      FIRSTNAME  MAND              PID
------------ ---------- ---------- ----------
Germany      fname      M1              12345
USA          fname      M1              12345
DENMARK      fname      M1              12345


   CREATE TABLE TABLE2
(
  FIRSTANDLASTNAME  VARCHAR2(30 BYTE),
  paid_amt          number(30), 
  MAND              VARCHAR2(10 BYTE),
  PID               NUMBER(20),
  usr              varchar2(15)
)

insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname1',0,'M1','eliz',12346);

insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname2',0,'M1','amy',12346);

insert into table2 (firstandlastname,paid_amt,mand,usr,pid)values ('fname3',0,'M1','amy',12346);

insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname4',10,'M2','bill',12348);

insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname4',12,'M2','bill',12348);

insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname4',14,'M2','amy',12346);

insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname5',11,'M2','bill',12348);

select * from table2;

FIRSTANDLASTNAME   PAID_AMT      MAND    PID       USR
----------------  ---------- ---------- -----     ----------
fname1                0          M1      12346     eliz
fname2                0          M1      12346     amy
fname3                0          M1      12346     amy
fname4               10          M2      12348     bill
fname4               12          M2      12348     bill
fname4               14          M2      12346     amy
fname5               11          M2      12348     bill


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
4       M1      P2      2       O       Table2  Firstandlastname        COUNT                                            %1 lines written
5       M2      P2      1       I       Table2  Paid_amt                Sum        Firstandlastname      usr=bill         Paid_amt for %1 is %2                           
6       M2      P3      2       O       Table2  Paid_amt                Sum        Firstandlastname      usr=bill         Paid_amt post batch for %1 is %2 

AUDITING TABLE:
---------------

ID	Cdate	                       Ccode 	     Pid	Mand	Skey	Message
----------------------------------------------------------------------------------------------------------------------
1	01.04.2009 08:30:00	         P1	     12345	M1	1	1 entries found for Germany
2	01.04.2009 08:30:01	         P1	     12345	M1	2	1 entries found for USA
3	01.04.2009 08:30:02	         P1	     12345	M1	3	1  entries found for Denmark
4	01.04.2009 08:30:03	         P1          12345	M1	4	3 total lines
5	01.04.2009 08:31:00	         P2	     12346	M1	1	2 lines found
6       01.04.2009 08:31:01              P2          12346	M1      2       2 lines written
7       07.09.2009 06:11:01              P2          12347      M2      1       Paid_amt for fname5 is 11
8       07.09.2009 06:11:01              P2          12347      M2      2       Paid_amt for fname4 is 22
9       07.09.2009 06:11:01              P3          12348      M2      1       Paid_amt post batch for fname5 is 11
10      07.09.2009 06:11:01              P3          12348      M2      2       Paid_amt post batch for fname4 is 22


EXECUTION:
----------

For row 3 in acfg table, the dynamic sql should be:
 
'select func(cname) from tname where mand=pi_mand' 
'select count(firstname) from table1 where mand='M1''

SQL>EXEC auditingsp (12346, 'M1', 'P2', 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
        ----------------------------------------------------------------
         4 09-JUL-09 P2              12345 M1            1    2 lines found


For Row 5 in acfg table('i' parameter), I get errors.. Dynamic sql should be:

'select func(cname),group_col1 from tname where mand=pi_mand and wheresql='..'  group by group_col1'   /* group_col1 in 'select func(cname),group_col1' should be selected only if there is a valid value for the field. Likewise, for group_col2,group_col3 */ 

'select sum(paid_amt), firstandlastname from table2 where mand='M2' and usr='bill' group by firstandlastname'   /* In this example, group_col1 has value firstandlastname */

SQL>select sum(paid_amt), firstandlastname from table2 where mand='M2' and usr='bill' group by firstandlastname;

SUM(PAID_AMT) FIRSTANDLASTNAME
------------- ------------------
           11 fname5
           22 fname4

SQL> EXEC auditingsp (12347, 'M2', 'P2', 0, 'I', 'source2');
BEGIN auditingsp (12347, 'M2', 'P2', 0, 'i', 'source2'); END;

*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "TEST.AUDITINGSP", line 41
ORA-06512: at line 1


I get the same error for row 6 in acfg table('o' parameter) as well.. if the SP is called with 'o' parameter, there should be pid=pi_pid in the where clause also.(Do I need to create if then else for this condition?)
 
 Dynamic SQl for row 6 in acfg table shoud be:

 'select func(cname),group_col1 from tname where mand=pi_mand and pid=pi_pid and wheresql='..'  group by group_col1' 

'select sum(paid_amt), firstandlastname from table2 where mand='M2' and pid=12348 and usr='bill' group by firstandlastname'

SQL> EXEC auditingsp (12348, 'M2', 'P3', 0, 'O', 'source2');
BEGIN auditingsp (12348, 'M2', 'P3', 0, 'O', 'source2'); END;

*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "TEST.AUDITINGSP", line 41
ORA-06512: at line 1



I'm thinking the error is due to the missing expression group_col1 in the select string.. So I tried adding in the below string, but still get the same error..

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
14     AND    v_yn  = 'Y'
15     AND    UPPER (i_o) = UPPER (pi_i_o)  
16     ORDER  BY mand, bcode, i_o, 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);
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     FOR i IN 1 .. acfg_array.COUNT LOOP
30       i_sql :=
31              'SELECT ' || acfg_array(i).func || '(*) num, ' 
32                        || acfg_array(i).group_col || ' col'                              /* changed */  
33        ||   ' FROM ' || acfg_array(i).tname 
34        ||   ' WHERE mand = :b_mand'
35        ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
36        ||   ' GROUP BY ' || acfg_array(i).cname 
37        ||            ',' || NVL (acfg_array(i).group_col1, 1)
38        ||            ',' || NVL (acfg_array(i).group_col2, 1)
39        ||            ',' || NVL (acfg_array(i).group_col3, 1)
40        ||   ' ORDER BY num DESC';
41      OPEN c1 FOR i_sql USING pi_mand;
42      LOOP
43        FETCH c1 INTO v_num, v_col; 
44        EXIT WHEN c1%NOTFOUND;
45        INSERT INTO auditing 
46          (id, cdate, ccode, pid, mand, skey, source, message)
47        SELECT audseq.NEXTVAL, SYSDATE, acfg_array(i).bcode, pi_pid, 
48               acfg_array(i).mand, acfg_array(i).skey, pi_source, 
49               REPLACE (REPLACE (acfg_array(i).message, '%1', v_num), '%2', v_col) 
50        FROM   DUAL;
51      END LOOP;
52      CLOSE c1;
53    END LOOP;
54  END LOOP;
55  CLOSE aud_cur;
56 END auditingsp;
/


Also, I tried the sequence for skey using rownumber() function, I'm able to select the records like below:

SQL> select mand,ccode,pid,row_number() over (partition by mand, ccode order by mand,ccode) newseq from auditing;

MAND CCODE PID NEWSEQ
---------- ---------- ---------- ----------
M1 P1 12345 1
M1 P1 12345 2
M1 P1 12345 3
M1 P1 12345 4
M1 P2 12345 1

But, when I update the skey using this select, I get ORA-01427

SQL>update auditing set (skey) = (select row_number() over (partition by mand, ccode order by mand,ccode) from auditing);
order by mand,ccode) from auditing)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

Thank you very much for all your help

[Updated on: Fri, 10 July 2009 16:05]

Report message to a moderator

Re: Create sp [message #412725 is a reply to message #412691] Fri, 10 July 2009 16:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You still don't seem to understand how to post a proper COMPLETE problem. You need to post, all together, in one post, so that we don't have to guess which pieces from other posts you might be using:

1. create table statements for all tables (acfg, auditing, table1, table2)
2. insert statements for data for all tables (acfg, table1, table2)
3. create statements for any other objects (like sequences)
4. the latest version of the procedure that you are running
5. how you are executing the procedure, including all paramters (if you are running it twice, once with 'i' and once with 'o' then include that and if you are running it for multiple other values then include that)
6. what results you want in the auditing table THAT MATCHES THE DATA THAT YOU PROVIDED AND THE EXECUTION PARAMETERS THAT YOU PROVIDED. So basically you are saying if you start with such and such and run such and such you should end up with such and wuch. This is necessary in order to understand your requirements.
7. all questions in the thread, not by private message

You have shown select statement for the acfg and table1 tables without any insert statements for the rows.

If there is some doubt about what dynamic sql is being generated, then you can use dbms_output to display what is generated and comment out the execution. If you see a problem, then try to figure out what you need to do to modify the code to fix it.

For the update, you have to tell it which row, so you need to include more than just the row_number in the query. You need to use a correlated update, comparing the rowid or the columns that determine the row_number, and use that in your where clause, so that it knows which row to get the value from. You can find examples of correlated updates in the online documentation.



[Updated on: Fri, 10 July 2009 16:32]

Report message to a moderator

Re: Create sp [message #412726 is a reply to message #412691] Fri, 10 July 2009 16:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I can see a problem with your wheresql values of "usr=bill". There are no quotes around "bill". So, that would cas an error. You need to have valid where clauses in your wheresql column.
Re: Create sp [message #412727 is a reply to message #412691] Fri, 10 July 2009 17:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
The func(*) works when the func is count, but the * will need to be changed to the column name to allow for when the func is sum.
Re: Create sp [message #412728 is a reply to message #412691] Fri, 10 July 2009 17:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Here is what I get, trying to put all the pieces that you provided together. Notice how I provided complete create table and insert scripts. I put ... in place of some of the update code so you can figure that out.

SCOTT@orcl_11g> -- tables:
SCOTT@orcl_11g> create table acfg (
  2  	     id number(10) not null,
  3  	     v_YN varchar2(1),
  4  	     mand varchar2(10),
  5  	     bcode varchar2(10),
  6  	     skey number(10),
  7  	     i_o varchar2(3),
  8  	     tname varchar2(255),
  9  	     cname varchar2(255),
 10  	     func varchar2(64),
 11  	     group_col1 varchar2(255),
 12  	     group_col2 varchar2(255),
 13  	     group_col3 varchar2(255),
 14  	     message_id number(10),
 15  	     wheresql varchar2(2000),
 16  	     message varchar2(2000)
 17  	     )
 18  /

Table created.

SCOTT@orcl_11g> create table table1
  2  	      (country	 varchar2(30),
  3  	       firstname varchar2(30),
  4  	       mand	 varchar2(10),
  5  	       pid	 number(20))
  6  /

Table created.

SCOTT@orcl_11g> CREATE TABLE TABLE2
  2  (
  3    FIRSTANDLASTNAME  VARCHAR2(30 BYTE),
  4    paid_amt 	 number(30),
  5    MAND		 VARCHAR2(10 BYTE),
  6    PID		 NUMBER(20),
  7    usr		varchar2(15)
  8  )
  9  /

Table created.

SCOTT@orcl_11g> create table auditing (
  2  	     id number(5) not null,
  3  	     cdate date,
  4  	     ccode varchar2(10),
  5  	     cterm varchar2(255),
  6  	     pid number(20),
  7  	     mand varchar2(10),
  8  	     skey number(10),
  9  	     message varchar2(2000),
 10  	     source varchar2(255),
 11  	     source_YN varchar2(1)
 12  	     )
 13  /

Table created.

SCOTT@orcl_11g> -- data (ADDED QUTOES AROUND BILL):
SCOTT@orcl_11g> begin
  2    insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func,group_col1,message)
  3    values (1,'Y','M1','P1',1,'O','table1','country','count','country','%1 entries found for %2');
  4  
  5    insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func,message)
  6    values (2,'Y','M1','P1',2,'O','table1','firstname','count','%1 total lines');
  7  
  8    insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func,message)
  9    values (3,'Y','M1','P2',1,'I','table1','firstname','count','%1 lines found');
 10  
 11    insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func,group_col1,message)
 12    values (4,'Y','M1','P2',2,'O','table2','firstandlastname','count','country','%1 lines written');
 13  
 14    insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func,group_col1,wheresql,message)
 15    values (5,'Y','M2','P2',1,'I','table2','paid_amt','sum','firstandlastname','usr=''bill''','Paid_amt for %2 is %1');
 16  
 17    insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func,group_col1,wheresql,message)
 18    values (6,'Y','M2','P3',2,'O','table2','paid_amt','sum','firstandlastname','usr=''bill''','Paid_amt post batch for %2 is %1');
 19  
 20    INSERT INTO table1 VALUES ('Germany', 'fname', 'M1', 12345);
 21    INSERT INTO table1 VALUES ('USA', 'fname', 'M1', 12345);
 22    INSERT INTO table1 VALUES ('Denmark', 'fname', 'M1', 12345);
 23  
 24    insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname1',0,'M1','eliz',12346);
 25    insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname2',0,'M1','amy',12346);
 26    insert into table2 (firstandlastname,paid_amt,mand,usr,pid)values ('fname3',0,'M1','amy',12346);
 27    insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname4',10,'M2','bill',12348);
 28    insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname4',12,'M2','bill',12348);
 29    insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname4',14,'M2','amy',12346);
 30    insert into table2 (firstandlastname,paid_amt,mand,usr,pid) values ('fname5',11,'M2','bill',12348);
 31  end;
 32  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- sequence:
SCOTT@orcl_11g> CREATE SEQUENCE audseq
  2  /

Sequence created.

SCOTT@orcl_11g> -- procedure (ADDED DBMS_OUTPUT AND CHANGED * TO CNAME):
SCOTT@orcl_11g> 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
 14  	 AND	v_yn  = 'Y'
 15  	 AND	UPPER (i_o) = UPPER (pi_i_o)
 16  	 ORDER	BY mand, bcode, i_o, 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);
 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  	 FOR i IN 1 .. acfg_array.COUNT LOOP
 30  	   i_sql :=
 31  		   'SELECT ' || acfg_array(i).func || '(' || acfg_array(i).cname || ') num, '
 32  			     || acfg_array(i).cname || ' col'
 33  	     ||   ' FROM ' || acfg_array(i).tname
 34  	     ||   ' WHERE mand = :b_mand'
 35  	     ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
 36  	     ||   ' GROUP BY ' || acfg_array(i).cname
 37  	     || 	   ',' || NVL (acfg_array(i).group_col1, 1)
 38  	     || 	   ',' || NVL (acfg_array(i).group_col2, 1)
 39  	     || 	   ',' || NVL (acfg_array(i).group_col3, 1)
 40  	     ||   ' ORDER BY num DESC';
 41  	   DBMS_OUTPUT.PUT_LINE (i_sql);
 42  	   OPEN c1 FOR i_sql USING pi_mand;
 43  	   LOOP
 44  	     FETCH c1 INTO v_num, v_col;
 45  	     EXIT WHEN c1%NOTFOUND;
 46  	     INSERT INTO auditing
 47  	       (id, cdate, ccode, pid, mand, skey, source, message)
 48  	     SELECT audseq.NEXTVAL, SYSDATE, acfg_array(i).bcode, pi_pid,
 49  		    acfg_array(i).mand, acfg_array(i).skey, pi_source,
 50  		    REPLACE (REPLACE (acfg_array(i).message, '%1', v_num), '%2', v_col)
 51  	     FROM   DUAL;
 52  	   END LOOP;
 53  	   CLOSE c1;
 54  	 END LOOP;
 55    END LOOP;
 56    CLOSE aud_cur;
 57  END auditingsp;
 58  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- executions:
SCOTT@orcl_11g> EXEC auditingsp (12345, 'M1', 'P1', 0, 'O', 'source1')
SELECT count(country) num, country col FROM table1 WHERE mand = :b_mand AND 1=1 GROUP BY country,country,1,1 ORDER BY
num DESC
SELECT count(firstname) num, firstname col FROM table1 WHERE mand = :b_mand AND 1=1 GROUP BY firstname,1,1,1 ORDER BY
num DESC

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC auditingsp (12346, 'M1', 'P2', 0, 'i', 'source1')
SELECT count(firstname) num, firstname col FROM table1 WHERE mand = :b_mand AND 1=1 GROUP BY firstname,1,1,1 ORDER BY
num DESC

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC auditingsp (12347, 'M2', 'P2', 0, 'I', 'source2')
SELECT sum(paid_amt) num, paid_amt col FROM table2 WHERE mand = :b_mand AND usr='bill' GROUP BY
paid_amt,firstandlastname,1,1 ORDER BY num DESC

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC auditingsp (12348, 'M2', 'P3', 0, 'O', 'source2')
SELECT sum(paid_amt) num, paid_amt col FROM table2 WHERE mand = :b_mand AND usr='bill' GROUP BY
paid_amt,firstandlastname,1,1 ORDER BY num DESC

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- results:
SCOTT@orcl_11g> SELECT id, cdate, ccode, pid, mand, skey, message
  2  FROM   auditing
  3  /

        ID CDATE     CCODE             PID MAND       SKEY MESSAGE
---------- --------- ---------- ---------- ---- ---------- --------------------------------
         1 10-JUL-09 P1              12345 M1            1 1 entries found for USA
         2 10-JUL-09 P1              12345 M1            1 1 entries found for Germany
         3 10-JUL-09 P1              12345 M1            1 1 entries found for Denmark
         4 10-JUL-09 P1              12345 M1            2 3 total lines
         5 10-JUL-09 P2              12346 M1            1 3 lines found
         6 10-JUL-09 P2              12347 M2            1 Paid_amt for 12 is 12
         7 10-JUL-09 P2              12347 M2            1 Paid_amt for 11 is 11
         8 10-JUL-09 P2              12347 M2            1 Paid_amt for 10 is 10
         9 10-JUL-09 P3              12348 M2            2 Paid_amt post batch for 12 is 12
        10 10-JUL-09 P3              12348 M2            2 Paid_amt post batch for 11 is 11
        11 10-JUL-09 P3              12348 M2            2 Paid_amt post batch for 10 is 10

11 rows selected.

SCOTT@orcl_11g> -- udpate:
SCOTT@orcl_11g> UPDATE auditing a
  2  SET    a.skey =
  3  	    (SELECT ...
  4  	     FROM   (SELECT ...,
  5  			    ROW_NUMBER () OVER (PARTITION BY mand, ccode ORDER BY mand, ccode) rn
  6  		     FROM   auditing) aa
  7  	     WHERE  ...)
  8  /

11 rows updated.

SCOTT@orcl_11g> -- udpated results:
SCOTT@orcl_11g> SELECT id, cdate, ccode, pid, mand, skey, message
  2  FROM   auditing
  3  /

        ID CDATE     CCODE             PID MAND       SKEY MESSAGE
---------- --------- ---------- ---------- ---- ---------- --------------------------------
         1 10-JUL-09 P1              12345 M1            1 1 entries found for USA
         2 10-JUL-09 P1              12345 M1            2 1 entries found for Germany
         3 10-JUL-09 P1              12345 M1            3 1 entries found for Denmark
         4 10-JUL-09 P1              12345 M1            4 3 total lines
         5 10-JUL-09 P2              12346 M1            1 3 lines found
         6 10-JUL-09 P2              12347 M2            1 Paid_amt for 12 is 12
         7 10-JUL-09 P2              12347 M2            3 Paid_amt for 11 is 11
         8 10-JUL-09 P2              12347 M2            2 Paid_amt for 10 is 10
         9 10-JUL-09 P3              12348 M2            2 Paid_amt post batch for 12 is 12
        10 10-JUL-09 P3              12348 M2            1 Paid_amt post batch for 11 is 11
        11 10-JUL-09 P3              12348 M2            3 Paid_amt post batch for 10 is 10

11 rows selected.

SCOTT@orcl_11g> 


Re: Create sp [message #412729 is a reply to message #411795] Fri, 10 July 2009 18:09 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi Barbara,

Here is the complete problem..


CREATE TABLE table1
      (country   VARCHAR2 (30),
       firstname VARCHAR2 (30),
       mand      VARCHAR2 (10),
       pid       number(20));

INSERT INTO table1 (country,firstname,mand,pid) values ('Germany', 'fname', 'M1',12345 );
INSERT INTO table1 (country,firstname,mand,pid) values ('USA', 'fname', 'M1',12345);   
INSERT INTO table1 (country,firstname,mand,pid) values ('DENMARK', 'fname', 'M1',12345);
   

SELECT * FROM TABLE1;

COUNTRY      FIRSTNAME  MAND              PID
------------ ---------- ---------- ----------
Germany      fname      M1              12345
USA          fname      M1              12345
DENMARK      fname      M1              12345

CREATE TABLE TABLE2
(
  firstandlastname  VARCHAR2(30 BYTE),
  paid_amt          number(30), 
  MAND              VARCHAR2(10 BYTE),
  PID               NUMBER(20),
  usr               varchar2(15));

insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname1',0,'M1','eliz');
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname2',0,'M1','amy'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname3',0,'M1','amy'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname4',10,'M2','bill'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname4',12,'M2','bill'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname4',14,'M2','amy'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname5',11,'M2','bill'); 

select * from table2;

FIRSTANDLASTNAME   PAID_AMT      MAND       USR
----------------  ---------- ---------- ---------------
fname1                0          M1         eliz
fname2                0          M1         amy
fname3                0          M1         amy
fname4               10          M2         bill
fname4               12          M2         bill
fname4               14          M2         amy
fname5               11          M2         bill

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');
insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func, wheresql,group_col1,message) values (5,'Y','M2','P2',1,'i','table2','paid_amt','sum','usr=''bill''','firstandlastname','Paid_amt for %1 is %2');
insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func, wheresql,group_col1,message) values (6,'Y','M2','P3',2,'o','table2','paid_amt','sum','usr=''bill''','firstandlastname','Paid_amt post batch  for %1 is %2');


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
4       M1      P2      2       O       Table2  Firstandlastname        COUNT                                            %1 lines written
5       M2      P2      1       I       Table2  Paid_amt                Sum        Firstandlastname      usr='bill'         Paid_amt for %1 is %2                           
6       M2      P3      2       O       Table2  Paid_amt                Sum        Firstandlastname      usr='bill'         Paid_amt post batch for %1 is %2 


AUDITING TABLE:
---------------

ID	Cdate	                       Ccode 	     Pid	Mand	Skey	Message
----------------------------------------------------------------------------------------------------------------------
1	01.04.2009 08:30:00	         P1	     12345	M1	1	1 entries found for Germany
2	01.04.2009 08:30:01	         P1	     12345	M1	2	1 entries found for USA
3	01.04.2009 08:30:02	         P1	     12345	M1	3	1  entries found for Denmark
4	01.04.2009 08:30:03	         P1          12345	M1	4	3 total lines
5	01.04.2009 08:31:00	         P2	     12346	M1	1	2 lines found
6       01.04.2009 08:31:01              P2          12346	M1      2       2 lines written
7       07.09.2009 06:11:01              P2          12347      M2      1       Paid_amt for fname5 is 11
8       07.09.2009 06:11:01              P2          12347      M2      2       Paid_amt for fname4 is 22
9       07.09.2009 06:11:01              P3          12348      M2      1       Paid_amt post batch for fname5 is 11
10      07.09.2009 06:11:01              P3          12348      M2      2       Paid_amt post batch for fname4 is 22

LOGIC:
------

Stored procedure is created for auditing records in the batch
tables. Batch process calls the Stored 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 acfg table) into the auditing table pre batch and post batch run. 
 
There will be 2 conditions in the procedure, Records are written to the auditing table only if acfg.v_yn and based on the pi_i_o= acfg.i_o, the records are written into the auditing table (where clause in the select query is different when pi_i_o= 'i' or pi_i_o ='o'). 

1. Only if acfg.v_yn ='Y', records should be inserted into auditing table. if acfg.v_yn ='N', data should be ignored.

  Read the records in acfg table(cursor aud_cur in the Stored procedure) order by mand,bcode,i_o,skey.

  2. 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 only when  
        acfg.wheresql is not null. Similarly group_col1, 
        group_col2, group_col3 should also be considered when 
        the values are not null. */
      
     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 /* Tname.pid will be assigned with 
        a number when the batch process runs. Hence evaluate 
        pid=pi_pid. wheresql should be considered only when 
        acfg.wheresql is not null. Similarly group_col1, 
        group_col2, group_col3 should also be considered when 
        the values are not null. */

     b. Execute the sql, fetch the output in acfg.message and 
        insert the message into auditing.message field.



I'll post the testing results soon, I'll probably have to include if then else because the where clause is different for 'i' and 'o' parameters in pi_i_o. I'll update the procedure and run it and post it here.

Thanks a million for your support.
Re: Create sp [message #412730 is a reply to message #412729] Fri, 10 July 2009 18:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Where are your execution statements with parameters?
Re: Create sp [message #412732 is a reply to message #411795] Fri, 10 July 2009 22:20 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi Barbara,

Here is the complete problem..


CREATE TABLE table1
      (country   VARCHAR2 (30),
       firstname VARCHAR2 (30),
       mand      VARCHAR2 (10),
       pid       number(20));

INSERT INTO table1 (country,firstname,mand,pid) values ('Germany', 'fname', 'M1',12345 );
INSERT INTO table1 (country,firstname,mand,pid) values ('USA', 'fname', 'M1',12345);   
INSERT INTO table1 (country,firstname,mand,pid) values ('DENMARK', 'fname', 'M1',12345);
   

SELECT * FROM TABLE1;

COUNTRY      FIRSTNAME  MAND              PID
------------ ---------- ---------- ----------
Germany      fname      M1              12345
USA          fname      M1              12345
DENMARK      fname      M1              12345

CREATE TABLE TABLE2
(
  firstandlastname  VARCHAR2(30 BYTE),
  paid_amt          number(30), 
  MAND              VARCHAR2(10 BYTE),
  PID               NUMBER(20),
  usr               varchar2(15));

insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname1',0,'M1','eliz');
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname2',0,'M1','amy'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname3',0,'M1','amy'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname4',10,'M2','bill'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname4',12,'M2','bill'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname4',14,'M2','amy'); 
insert into table2 (firstandlastname,paid_amt,mand,usr) values ('fname5',11,'M2','bill'); 

select * from table2;

FIRSTANDLASTNAME   PAID_AMT      MAND       USR
----------------  ---------- ---------- ---------------
fname1                0          M1         eliz
fname2                0          M1         amy
fname3                0          M1         amy
fname4               10          M2         bill
fname4               12          M2         bill
fname4               14          M2         amy
fname5               11          M2         bill

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),                             /* can be count, sum, min, max or a value(not function)
	group_col1 varchar2(255),                      /* optional */
	group_col2 varchar2(255),                      /* optional */
	group_col3 varchar2(255),                      /* optional */
	message_id number(10),
	wheresql varchar2(2000),                       /* optional */
	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');
insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func, wheresql,group_col1,message) values (5,'Y','M2','P2',1,'i','table2','paid_amt','sum','usr=''bill''','firstandlastname','Paid_amt is %1 for %2');
insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func, wheresql,group_col1,message) values (6,'Y','M2','P3',2,'o','table2','paid_amt','sum','usr=''bill''','firstandlastname','Paid_amt post batch  is %1 for %2');


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
4       M1      P2      2       O       Table2  Firstandlastname        COUNT                                                %1 lines written
5       M2      P2      1       I       Table2  Paid_amt                Sum        Firstandlastname      usr='bill'         Paid_amt is %1 for %2                           
6       M2      P3      2       O       Table2  Paid_amt                Sum        Firstandlastname      usr='bill'         Paid_amt post batch is %1 for %2 


AUDITING TABLE:
---------------

ID	Cdate	                       Ccode 	     Pid	Mand	Skey	Message
----------------------------------------------------------------------------------------------------------------------
1	01.04.2009 08:30:00	         P1	     12345	M1	1	1 entries found for Germany
2	01.04.2009 08:30:01	         P1	     12345	M1	2	1 entries found for USA
3	01.04.2009 08:30:02	         P1	     12345	M1	3	1  entries found for Denmark
4	01.04.2009 08:30:03	         P1          12345	M1	4	3 total lines
5	01.04.2009 08:31:00	         P2	     12346	M1	1	2 lines found
6       01.04.2009 08:31:01              P2          12346	M1      2       2 lines written
7       07.09.2009 06:11:01              P2          12347      M2      1       Paid_amt is 22 for fname4
8       07.09.2009 06:11:01              P2          12347      M2      2       Paid_amt is 11 for fname5
9       07.09.2009 06:11:01              P3          12348      M2      1       Paid_amt post batch is 22 for fname4
10      07.09.2009 06:11:01              P3          12348      M2      2       Paid_amt post batch is 11 for fname5


LOGIC:
---------

Stored procedure is created for auditing records in the batch tables. Batch process calls the Stored 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 acfg table) into the auditing table pre batch and post batch run.

There will be 2 conditions in the procedure, Records are written to the auditing table only if acfg.v_yn and based on the pi_i_o= acfg.i_o, the records are written into the auditing table (where clause in the select query is different when pi_i_o= 'i' or pi_i_o ='o').

Quote:

1. Only if acfg.v_yn ='Y', records should be inserted into auditing table. if acfg.v_yn ='N', data should be ignored.

Read the records in acfg table(cursor aud_cur in the Stored procedure) order by mand,bcode,i_o,skey.

2. 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 only when acfg.wheresql is not null. Similarly group_col1, group_col2, group_col3 should also be considered
when the values are not null. */

b. Execute the sql, fetch the output in acfg.message and insert the message into auditing.message field. Auditing.skey needs to be assigned like a sequence based
on the same mand and ccode in auditing table. Insert auditing.source_yn, source_yn will be 'Y' when pi_source is not NULL and source_yn will be 'N' when pi_source
is NULL.

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 /* Tname.pid will be assigned with a number when the batch process runs. Hence evaluate pid=pi_pid. wheresql should be considered
only when acfg.wheresql is not null. Similarly group_col1,group_col2, group_col3 should also be considered when the values are not null. */

b. Execute the sql, fetch the output in acfg.message and insert the message into auditing.message field. Auditing.skey needs to be assigned like a sequence based
on the same mand and ccode in auditing table. Insert auditing.source_yn, source_yn will be 'Y' when pi_source is not NULL and source_yn will be 'N' when pi_source is
NULL.



I've altered the procedure with if then else clause (where clause difference in the sql string), not sure if there is a better way to do this..

CREATE OR REPLACE PROCEDURE SNA.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
  CURSORaud_cur IS
    SELECT * 
    FROM   acfg
    WHERE  mand  = pi_mand
    AND    bcode = pi_bcode
    AND    v_yn  = 'Y'
    AND    UPPER (i_o) = UPPER (pi_i_o)  
    ORDER  BY mand, bcode, i_o, skey;
  TYPE acfg_tab IS TABLE OF aud_cur%ROWTYPE;
  acfg_array       acfg_tab;
  i_sql            VARCHAR2 (32767);
  o_sql            VARCHAR2 (32767);
  c1               SYS_REFCURSOR;
  v_num            NUMBER;
  v_col            VARCHAR2 (30);
  err_msg          varchar2(4000);
  
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

    If UPPER (pi_i_o) = 'I' THEN 
     i_sql :=
               'SELECT ' || acfg_array(i).func || '(' || acfg_array(i).cname || ') 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 
        ||            ',' || NVL (acfg_array(i).group_col1, 1)
        ||            ',' || NVL (acfg_array(i).group_col2, 1)
        ||            ',' || NVL (acfg_array(i).group_col3, 1)
        ||   ' ORDER BY num DESC';
        DBMS_OUTPUT.PUT_LINE (i_sql);
      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;
    
    ElsIf UPPER (pi_i_o) = 'O'  THEN

      i_sql :=
              'SELECT ' || acfg_array(i).func || '(' || acfg_array(i).cname || ') num, '
                        || acfg_array(i).cname || ' col'  
        ||   ' FROM ' || acfg_array(i).tname 
        ||   ' WHERE mand = :pi_mand'
        ||   ' AND ' || 'pid = :pi_pid'
        ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
        ||   ' GROUP BY ' || acfg_array(i).cname 
        ||            ',' || NVL (acfg_array(i).group_col1, 1)
        ||            ',' || NVL (acfg_array(i).group_col2, 1)
        ||            ',' || NVL (acfg_array(i).group_col3, 1)
        ||   ' ORDER BY num DESC';
        DBMS_OUTPUT.PUT_LINE (i_sql);
      OPEN c1 FOR i_sql USING pi_mand,pi_pid;
      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 if;
  END LOOP;
  END LOOP;
  CLOSE aud_cur;
 
  
EXCEPTION  
  WHEN OTHERS THEN  
 
    ERR_MSG := 'ERROR: ' || SQLCODE || ' MESSAGE: ' || SQLERRM;  
    raise_application_error(-20000, err_msg);  
END auditingsp;
/


EXECUTIONS OF THE EXAMPLES:
---------------------------------------------------

Row 1 & 2 in ACFG table:
--------------------
SQL>EXEC auditingsp (12345, 'M1', 'P1', 0, 'O', 'source1')
SELECT count(country) num, country col FROM table1 WHERE mand = :b_mand AND pid=:pi_pid  AND 1=1 GROUP BY country,country,1,1 ORDER BY num DESC
SELECT count(firstname) num, firstname col FROM table1 WHERE mand = :b_mand AND pid=:pi_pid  AND 1=1 GROUP BY firstname,1,1,1 ORDER BY num DESC

PL/SQL procedure successfully completed.

SQL>SELECT id, cdate, ccode, pid, mand, skey, message   FROM   auditing;

ID CDATE       CCODE       PID     MAND    SKEY        MESSAGE
----------------------------------------------------------------
 1 10-JUL-09    P1        12345     M1      1       1 entries found for USA
 2 10-JUL-09    P1        12345     M1      1       1 entries found for Germany
 3 10-JUL-09    P1        12345     M1      1       1 entries found for Denmark
 4 10-JUL-09    P1        12345     M1      2       3 total lines 

But, the sql needs to be:

select count(country) num, country col from table1 where mand=:b_mand and pid=:pi_pid and 1=1 group by country,1,1 order by num desc
select count(firstname) num from table1 where mand = :b_mand and pid =:pid and 1=1 group by 1,1,1 order by num desc
/* since there is no value for group_col* in acfg table for this row(2). But, the output remains the same... */


Row 3 in ACFG table:
--------------------
SQL>EXEC auditingsp (12346, 'M1', 'P2', 0, 'i', 'source1')
SELECT count(firstname) num, firstname col FROM table1 WHERE mand = :b_mand AND 1=1 GROUP BY firstname,1,1,1 ORDER BY
num DESC

PL/SQL procedure successfully completed.

SQL>SELECT id, cdate, ccode, pid, mand, skey, message   FROM   auditing;

ID CDATE       CCODE       PID     MAND    SKEY        MESSAGE
----------------------------------------------------------------
 5 10-JUL-09    P2        12346     M1      1       3 lines found

But, the Sql should be:
select count(firstname) num from table1 where mand = :b_mand AND 1=1 GROUP BY 1,1,1 ORDER BY num DESC
/* since there is no value for group_col* in acfg table for this row(3). But, the output remains the same...


Row 4 in ACFG table:
--------------------
SQL>EXEC auditingsp (12346, 'M1', 'P2', 0, 'O', 'source1')
select count(firstandlastname) num, firstandlastname col from table2 where mand = :b_mand  AND pid=:pi_pid  AND 1=1 GROUP BY firstandlastname, 1,1,1 ORDER BY num DESC             

PL/SQL procedure successfully completed.

SQL>SELECT id, cdate, ccode, pid, mand, skey, message   FROM   auditing;

ID CDATE       CCODE       PID     MAND    SKEY        MESSAGE
----------------------------------------------------------------
 6 10-JUL-09    P2        12346     M1      2       1 lines written
 7 10-JUL-09    P2        12346     M1      2       1 lines written
 8 10-JUL-09    P2        12346     M1      2       1 lines written

But, the sql for this row should be:
select count(firstandlastname) num from table2 where mand =:b_mand and pid = :pi_pid AND 1=1 GROUP BY 1,1,1 ORDER BY num DESC
/* output of this query is 3. But, the Stored procedure is giving us 3 lines grouping by firstandlastname. */

Auditing table for this row should be:
SQL>SELECT id, cdate, ccode, pid, mand, skey, message   FROM   auditing;

ID CDATE       CCODE       PID     MAND    SKEY        MESSAGE
----------------------------------------------------------------
 6 10-JUL-09    P2        12346     M1      2       3 lines written

Row 5 in ACFG table:
-------------------
SQL>EXEC auditingsp (12347, 'M2', 'P2', 0, 'I', 'source2')
SELECT sum(paid_amt) num, paid_amt col FROM table2 WHERE mand = :b_mand AND usr='bill' GROUP BY paid_amt, firstandlastname,1,1 ORDER BY num DESC

PL/SQL procedure successfully completed.

SQL>SELECT id, cdate, ccode, pid, mand, skey, message   FROM   auditing;

ID CDATE       CCODE       PID     MAND    SKEY        MESSAGE
------------------------------------------------------------------------
 9 10-JUL-09     P2        12347     M2      1       Paid_amt for 12 is 12
 10 10-JUL-09    P2        12347     M2      1       Paid_amt for 11 is 11
 11 10-JUL-09    P2        12347     M2      1       Paid_amt for 10 is 10

But, the sql for this row should be:
select sum(paid_amt) num, firstandlastname col from table2 where mand = :b_mand and usr='bill' group by firstandlastname,1,1 ORDER BY num DESC
/* Output of this query is like below, but the stored procedure returned 3 lines grouping by paid_amt,firstandlastname */
SQL>SELECT id, cdate, ccode, pid, mand, skey, message   FROM   auditing;

ID CDATE       CCODE       PID     MAND    SKEY        MESSAGE
------------------------------------------------------------------------
 9 10-JUL-09     P2        12347     M2      1       Paid_amt is 22 for fname4
 10 10-JUL-09    P2        12347     M2      1       Paid_amt is 11 for fname5


I tried changing this piece for the above issues, to select group_col1, it returns the correct result.

i_sql :=
       SELECT '  || acfg_array(i).func || '(' 
                 || acfg_array(i).cname || ') num, '
                 || NVL (acfg_array(i).group_col1, 1)  || ' col'
        ||   ' FROM ' || acfg_array(i).tname 
        ||   ' WHERE mand = :pi_mand'
        ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
        ||   ' GROUP BY ' || NVL (acfg_array(i).group_col1, 1)
        ||            ',' || NVL (acfg_array(i).group_col2, 1)
        ||            ',' || NVL (acfg_array(i).group_col3, 1)
        ||   ' ORDER BY num DESC';


I'm still working on the skey update problem .. Is it possible to include the update statement for the auditing.skey in the procedure itself? or it cannot be done inside the procedure?

Thanks very much.
Re: Create sp [message #412736 is a reply to message #411795] Fri, 10 July 2009 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
I hope you are NOT expecting any response until some time Monday.

Everyone should enjoy their summer weekend away from Oracle.
Re: Create sp [message #412737 is a reply to message #412732] Fri, 10 July 2009 23:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You left out the v_yn values of 'Y' in the first few rows inserted into the acfg table. It looks like you've got it except for the update. You can put the update at the end of the procedure, but then it has to do duplicate work. It is more efficient to run one update statement after all of the runs of the procedure.
Re: Create sp [message #412738 is a reply to message #412736] Fri, 10 July 2009 23:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
BlackSwan wrote on Fri, 10 July 2009 21:32
I hope you are NOT expecting any response until some time Monday.

Everyone should enjoy their summer weekend away from Oracle.


For those of us who don't live by the beach like you, so we don't get those cool ocean breezes, it is too hot to do much of anything but sit at a desk in front of a fan.
Re: Create sp [message #412769 is a reply to message #411795] Sat, 11 July 2009 10:50 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi Barbara,

Yes, seems to work now.. Thanks to you for your great help.

I still couldnt get the skey right.. Also, I need to insert source_yn in auditing table, when pi_source is NOT NULL, source_yn='Y' and pi_source is NULL, then source_yn='N'.

[code]
UPDATE auditing a
SET a.skey =
(SELECT a.skey
FROM (SELECT
ROW_NUMBER () OVER (PARTITION BY mand, ccode ORDER BY mand, ccode) rn
FROM auditing )aa
WHERE a.id=aa.id and mand='M1' and ccode='P1')

Error at line 2
ORA-00904: "AA"."ID": invalid identifier

Inserting source_yn in the insert statement..


CREATE OR REPLACE PROCEDURE SNA.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    v_yn  = 'Y'
    AND    UPPER (i_o) = UPPER (pi_i_o)  
    ORDER  BY mand, bcode, i_o, skey;
  TYPE acfg_tab IS TABLE OF aud_cur%ROWTYPE;
  acfg_array       acfg_tab;
  i_sql            VARCHAR2 (32767);
  o_sql            VARCHAR2 (32767);
  c1               SYS_REFCURSOR;
  v_num            NUMBER;
  v_col            VARCHAR2 (30);
  err_msg          varchar2(4000);
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
    If UPPER (pi_i_o) = 'I' THEN 
     i_sql :=
               'SELECT '  || acfg_array(i).func || '(' || acfg_array(i).cname || ') num, '
                          || NVL (acfg_array(i).group_col1, 1)  || ' col'
        ||   ' FROM ' || acfg_array(i).tname 
        ||   ' WHERE mand = :pi_mand'
        ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
        ||   ' GROUP BY ' || NVL (acfg_array(i).group_col1, 1)
        ||            ',' || NVL (acfg_array(i).group_col2, 1)
        ||            ',' || NVL (acfg_array(i).group_col3, 1)
        ||   ' ORDER BY num DESC';
        DBMS_OUTPUT.PUT_LINE (i_sql);
      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,source_yn ,message)
        SELECT audseq.NEXTVAL, SYSDATE, acfg_array(i).bcode, pi_pid, 
               acfg_array(i).mand, acfg_array(i).skey, pi_source, 
               decode(source_yn,pi_source is not null,'Y',
                                pi_source is NULL,'N'),
               REPLACE (REPLACE (acfg_array(i).message, '%1', v_num), '%2', v_col) 
        FROM   DUAL;
      END LOOP;
      CLOSE c1;
    ElsIf UPPER (pi_i_o) = 'O'  THEN
      i_sql :=             
               'SELECT '  || acfg_array(i).func || '(' || acfg_array(i).cname || ') num, '
                          || NVL (acfg_array(i).group_col1, 1)  || ' col'
        ||   ' FROM ' || acfg_array(i).tname 
        ||   ' WHERE mand = :pi_mand'
        ||   ' AND ' || 'pid = :pi_pid'
        ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
        ||   ' GROUP BY ' || NVL (acfg_array(i).group_col1, 1)
        ||            ',' || NVL (acfg_array(i).group_col2, 1)
        ||            ',' || NVL (acfg_array(i).group_col3, 1)
        ||   ' ORDER BY num DESC';
        DBMS_OUTPUT.PUT_LINE (i_sql);
      OPEN c1 FOR i_sql USING pi_mand,pi_pid;
      LOOP
        FETCH c1 INTO v_num, v_col; 
        EXIT WHEN c1%NOTFOUND;
        INSERT INTO auditing 
          (id, cdate, ccode, pid, mand, skey, source,source_yn ,message)
        SELECT audseq.NEXTVAL, SYSDATE, acfg_array(i).bcode, pi_pid, 
               acfg_array(i).mand, acfg_array(i).skey, pi_source, 
               decode(source_yn,pi_source is not null,'Y',
                                pi_source is NULL,'N'),
               REPLACE (REPLACE (acfg_array(i).message, '%1', v_num), '%2', v_col) 
        FROM   DUAL;
      END LOOP;
      CLOSE c1;
  End if;
  END LOOP;
  END LOOP;
  CLOSE aud_cur;
EXCEPTION  
  WHEN OTHERS THEN  
    ERR_MSG := 'ERROR: ' || SQLCODE || ' MESSAGE: ' || SQLERRM;  
    raise_application_error(-20000, err_msg);  
END auditingsp;
/


Warning: compiled but with compilation errors
On line:  55
PL/SQL: ORA-00907: missing right parenthesis


Please help me..We have clients with 9i,10g and 11g databases.. Will this stored procedure work all versions?

Thank you so much

Re: Create sp [message #412775 is a reply to message #412769] Sat, 11 July 2009 13:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
For your skey, you need to update it with the row_number value, so you should be selecting the rn alias for that, not skey. Also, it is easier to compare rowid's (or their aliases) in your where clause. Of course, you have to select them in your inner query before you can compare them. Your where clause attempts to select columns that haven't even been selected in your inner query. You should look up correlated subqueries in the online documentation and try some simple examples to understand what you are trying to do, instead of just guessing.

Your decode syntax is invalid. Look up decode in the online documentation. You cannot use "is not null" in the middle of a decode statement. You can check for null values using decode or nvl or nvl2, but not like that.

Once you get it working, as to whether it will work for prior versions, you will have to test and see.

Re: Create sp [message #412939 is a reply to message #411795] Mon, 13 July 2009 08:22 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Thank you so much, Barbara
Re: Create sp [message #412955 is a reply to message #411795] Mon, 13 July 2009 10:53 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
I got the update for skey..

UPDATE auditing a
SET a.skey = 
(SELECT aa.rn
FROM (SELECT rowid,
ROW_NUMBER () OVER (PARTITION BY mand, ccode ORDER BY id asc) rn
FROM auditing )aa 
WHERE a.rowid=aa.rowid);

 


Thank you

[Updated on: Mon, 13 July 2009 11:36]

Report message to a moderator

Re: Create sp [message #412980 is a reply to message #411795] Mon, 13 July 2009 14:33 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi Barbara,

I need to change a functionaly in the procedure..

The value in acfg.cname can have codes which needs to be transformed into term..

For example, in the first row in acfg table, func is 'country'.
In table1, the value for country will be 'GE' instead of 'Germany', value will be 'DE' for 'Denmark' etc. This is not just for country but also for other cdt type values. We already have a function(get_cdt) that will get the term for this code from another cdt table.


I've altered table1 with country codes and I've added cdt_type in acfg table.



CREATE TABLE table1
      (country   VARCHAR2 (30),
       firstname VARCHAR2 (30),
       mand      VARCHAR2 (10),
       pid       number(20));

INSERT INTO table1 (country,firstname,mand,pid) values ('GE', 'fname', 'M1',12345 );
INSERT INTO table1 (country,firstname,mand,pid) values ('US', 'fname', 'M1',12345);   
INSERT INTO table1 (country,firstname,mand,pid) values ('DE', 'fname', 'M1',12345);  

SELECT * FROM TABLE1;

COUNTRY  FIRSTNAME  MAND     PID
------------------------------------------------
GE       fname      M1       12345
US       fname      M1       12345
DE       fname      M1       12345



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),                             /* can be count, sum, min, max or a value(not function)
	group_col1 varchar2(255),                      
	group_col2 varchar2(255),                      
	group_col3 varchar2(255),                      
	message_id number(10),
	wheresql varchar2(2000),                       
	message varchar2(2000),
        cdt_type varchar2(10)
	);

insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func,group_col1,message,cdt_type) values (1,'Y','M1','P1',1,'o', 'table1', 'country','count','country', '%1 entries found for %2','Coun');
insert into acfg (id,v_yn,mand,bcode,skey,i_o,tname,cname,func, message) values (2,'Y','M1','P1',2,'o','table1','firstname','count','%1 total lines');

ACFG TABLE:
-----------

ID      V_yn	Mand	Bcode	Skey	I_O	Tname	Cname	        Func	 Group_col1     WhereSQL         Message                    Cdt_type
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
1	 Y	M1	P1	1	O	Table1	COUNTRY    	COUNT	  COUNTRY	   	     %1 entries found for  %2        Coun
2	 Y	M1	P1	2	O	Table1	FIRSTNAME	COUNT		                         %1 total lines

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    v_yn  = 'Y'
    AND    UPPER (i_o) = UPPER (pi_i_o)  
    ORDER  BY mand, bcode, i_o, 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);
  err_msg          varchar2(4000);
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
    If UPPER (pi_i_o) = 'I' THEN 
     i_sql :=
               'SELECT '  || acfg_array(i).func || '(' || acfg_array(i).cname || ') num, '
                          || NVL (acfg_array(i).group_col1, 1)  || ' col'
        ||   ' FROM ' || acfg_array(i).tname 
        ||   ' WHERE mand = :pi_mand'
        ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
        ||   ' GROUP BY ' || NVL (acfg_array(i).group_col1, 1)
        ||            ',' || NVL (acfg_array(i).group_col2, 1)
        ||            ',' || NVL (acfg_array(i).group_col3, 1)
        ||   ' ORDER BY num DESC';
        DBMS_OUTPUT.PUT_LINE (i_sql);
      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;
    ElsIf UPPER (pi_i_o) = 'O'  THEN
      i_sql :=             
               'SELECT '  || acfg_array(i).func || '(' || acfg_array(i).cname || ') num, '
                          || NVL (acfg_array(i).group_col1, 1)  || ' col'
        ||   ' FROM ' || acfg_array(i).tname 
        ||   ' WHERE mand = :pi_mand'
        ||   ' AND ' || 'pid = :pi_pid'
        ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
        ||   ' GROUP BY ' || NVL (acfg_array(i).group_col1, 1)
        ||            ',' || NVL (acfg_array(i).group_col2, 1)
        ||            ',' || NVL (acfg_array(i).group_col3, 1)
        ||   ' ORDER BY num DESC';
        DBMS_OUTPUT.PUT_LINE (i_sql);
      OPEN c1 FOR i_sql USING pi_mand,pi_pid;
      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 if;
  END LOOP;
  END LOOP;
  CLOSE aud_cur;
UPDATE auditing a
SET a.skey = 
(SELECT aa.rn
FROM (SELECT rowid,
ROW_NUMBER () OVER (PARTITION BY mand, ccode ORDER BY id asc) rn
FROM auditing )aa 
WHERE a.rowid=aa.rowid);
EXCEPTION  
  WHEN OTHERS THEN  
    ERR_MSG := 'ERROR: ' || SQLCODE || ' MESSAGE: ' || SQLERRM;  
    raise_application_error(-20000, err_msg);  
END auditingsp;
/



EXECUTION:
----------

SQL> EXEC auditingsp (12345, 'M1', 'P1', 0, 'O', '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 10-JUL-09    P1        12345     M1      1       1 entries found for US
 2 10-JUL-09    P1        12345     M1      1       1 entries found for GE
 3 10-JUL-09    P1        12345     M1      1       1 entries found for DE
 4 10-JUL-09    P1        12345     M1      2       3 total lines 


But, the auditing table should be:

SQL>SELECT id, cdate, ccode, pid, mand, skey, message   FROM   auditing;

ID CDATE       CCODE       PID     MAND    SKEY        MESSAGE
----------------------------------------------------------------
 1 10-JUL-09    P1        12345     M1      1       1 entries found for USA
 2 10-JUL-09    P1        12345     M1      1       1 entries found for Germany
 3 10-JUL-09    P1        12345     M1      1       1 entries found for Denmark
 4 10-JUL-09    P1        12345     M1      2       3 total lines 



Conditions:
-----------

If there is a value for acfg.cdt_type, we need to use the function for acfg.cname to get the term(example like country in acfg.cname). There are a lot of cdt type codes that will be in table1 or table2 or more tables, so for those cdt_type cnames we need to use this get_cdt function to get the term.

If acfg.cdt_type is blank/NULL then function is not used.

Example Input parameters for the function:

SQL>select get_cdt('COUN','GE','M1','','TERM','') from dual;

Germany


'COUN' is from acfg.cdt_type
'GE' is table1.country
'M1' is pi_mand
'TERM' is hard coded

If acfg.cdt_type is blank, no function needs to be used..

Please help me... Thanks for your great help



Re: Create sp [message #412984 is a reply to message #412980] Mon, 13 July 2009 16:28 Go to previous messageGo to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I need to change a functionaly in the procedure..



Then proceed to do so.

What have you tried so far, and what is the actual problem you face? We are not really here do do ALL your work for free for you, you know?
Previous Topic: Use Associative Arrays in sql
Next Topic: send email with attachement (merged 3)
Goto Forum:
  


Current Time: Sat Dec 03 18:09:15 CST 2016

Total time taken to generate the page: 0.13170 seconds