DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Plsql help..

  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Plsql help..

    Hi all,

    I need some help from you experts on this stored procedure..

    There are 4-5 batch procedures that will call this stored procedure. The batch will call the stored procedure with 'I' parameter in the beginning of the batch process. The batch will call the stored procedure with 'O' parameter at the end of the batch process. The stored procedure should write the details into the auditing table.

    Example record in acfg table:
    Code:
    ID	Mand	Bcode	Skey	I_O	Tname	Cname					Func	 Group_col1	   WhereSQL		  Message
    1	M1	P1	1	O	Table1	COUNTRY					COUNT	   COUNTRY					%1 entries found for  %2
    2	M1	P1	2	O	Table1	FIRSTNAME			   COUNT							   %1 total lines
    3	M1	P2	1	I	Table1	FIRSTNAME			COUNT								%1 lines found
    The logic of the stored procedure for the example is:

    *1*. If acfg.v_yn is 'Y'

    Read the record in acfg table using mand=pi_mand and bcode = pi_bcode order by skey;

    *a*. if I_O parameter is 'I' then:

    Fetch the records from step 1).
    Generate a dynamic SQL like select func(cname) from tname where mand= pi_mand; /* sql should also include acfg.wheresql in the
    where clause, sql should also include acfg.group_col1,acfg.group_col2, acfg.group_col3 if there are any values. */

    Executing this above sql calculates count(firstname) from table1 where mand='M1';

    Fetch the output of the above query and using the acfg.message field for that row '%1 lines found' insert/update this message into auditing.message as '32 lines
    found'

    Assign skey for the auditing table, based on the auditing.ccode.

    *b*. if I_O parameter is 'O' then:

    Fetch the records from step 1).
    Generate a dynamic SQL like select func(cname) from tname where mand= pi_mand and auditing.pid=pi_pid group by group_col1; /* pi_pid is the argument passed in
    the SP. sql should also include acfg.wheresql in the where clause, sql should also include acfg.group_col2, acfg.group_col3 if there are any values. */

    Executing this above sql calculates count(country) from table1 where mand='M1' and auditing.pid=12345 group by country;

    Fetch the output of the above query and using the acfg.message field for that row '%1 entries found for %2' insert/update this message into auditing.message as
    '20 entries found for Germany' '10 entries found for USA' '2 entries found for Denmark' /* can be more than 1 record */

    Assign skey for the auditing table, based on the auditing.ccode for these 3 records.

    The resulting record in the auditing table will look like:

    Code:
    ID	Cdate						   Ccode 		 Pid	Mand	Skey	Message
    1	01.04.2009 08:30:00			 P1		 12345	M1	1	20 entries found for Germany
    2	01.04.2009 08:30:01			 P1		 12345	M1	2	10 entries found for USA
    3	01.04.2009 08:30:02			 P1		 12345	M1	3	2  entries found for Denmark
    4	01.04.2009 08:30:03			 P1		  12345	M1	4	32 total lines
    5	01.04.2009 08:31:00			 P2		 12346	M1	1	32 lines found

    I would appreciate any kind of help with this... Please help me.. Thanks a lot

  2. #2
    Join Date
    Jul 2009
    Posts
    5
    I've created this below procedure... But, struggling on writing the message field..

    Code:
    create table auditing (
    	id number(5) not null,
    	cdate date,
    	ccode varchar2(10),
    	cterm varchar2(255),
    	pid number(20),
    	mand varchar2(10),
    	skey number(10),
    	message varchar2(2000),
    	source varchar2(255),
    	source_YN varchar2(1)
    	);
    
    create table acfg (
    	id number(10) not null,
    	v_YN varchar2(1),
    	mand varchar2(10),
    	bcode varchar2(10),
    	skey number(10),
    	i_o varchar2(3),
    	tname varchar2(255),
    	cname varchar2(255),
    	func varchar2(64),
    	group_col1 varchar2(255),
    	group_col2 varchar2(255),
    	group_col3 varchar2(255),
    	message_id number(10),
    	wheresql varchar2(2000),
    	message varchar2(2000)
    	);
    	
    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,group_col1,message)
    values (1,'M1','P1',1,'o','table1','country','count','country','%1 entries found for %2');
    
    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
    values (2,'M1','P1',2,'o','table1','firstname','count','%1 total lines');
    
    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
    values (3,'M1','P2',1,'i','table1','firstname','count','%1 lines found');
    
    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
    values (4,'M1','P2',2,'o','table2','firstandlastname','count','%1 lines written');
    
    
    CREATE SEQUENCE audseq
    	 MinVALUE 1
    		MAXVALUE 999999999999999999999999999
    	   START WITH 1
    		inCREMENT BY 1
    		CACHE 20;
    
    	  Create OR Replace Procedure auditingsp( pi_pid	IN Number
    									  , pi_mand   IN Varchar2
    									  , pi_bcode  IN Varchar2
    									  , pi_opp	IN Number
    									  , pi_I_O	IN Varchar2
    									  , pi_Source IN Varchar2 )
     As
       Cursor aud_cur( pi_mand IN Varchar2
    			   , pi_I_O  IN Varchar2
    			   , pi_bcode  IN Number ) Is
    	  SELECT v_yn
    		   , message 
    		FROM acfg
    	   WHERE mand = pi_mand
    		 AND (pi_I_O = 'I')
    		  OR (pi_I_O = 'O' AND bcode=pi_bcode);
     
       Type acfg_tab Is Table Of aud_cur%Rowtype;
       acfg_array acfg_tab;
    Begin
       Open aud_cur( pi_mand
    			 , pi_I_O
    			 , pi_bcode);
       Loop
    	  Fetch aud_cur Bulk Collect
    		 INTO acfg_array Limit 1000;
    	  Exit When aud_cur%Notfound;
       
    	  For i IN acfg_array.First .. acfg_array.Last Loop
    
    		 If upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'I' THEN		
    			INSERT INTO auditing
    			   ( ID
    			   , cdate
    			   , ccode
    			   , pid
    			   , mand
    		   , skey
    			   , Source
    			   , message )
    			VALUES
    			   ( audseq.Nextval
    			   , Sysdate
    			   , pi_bcode
    			   , pi_pid
    			   , pi_mand
    		   , ??????			 /* skey needs to be assigned based on the auditing.ccode */
    			   , pi_Source
    			   , ??????			 /* this should be step a) from my previous reply,Build a dynamic SQL like select 
    func(cname) from tname where mand= pi_mand; /* sql should also include acfg.wheresql in the where clause, sql should also 
    include acfg.group_col1,acfg.group_col2, acfg.group_col3 if there are any values. Execute the sql and using the 
    acfg.message for that row, insert into auditing.message field */ 
     		   );
    
    		 Elsif upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'O' THEN
    		 INSERT INTO auditing
    			   ( ID
    			   , cdate
    			   , ccode
    			   , pid
    			   , mand
    		   , skey
    			   , Source
    			   , message )
    			VALUES
    			   ( audseq.Nextval
    			   , Sysdate
    			   , pi_bcode
    			   , pi_pid
    			   , pi_mand
    		   , ??????			 /* skey needs to be assigned based on the auditing.ccode */
    			   , pi_Source
    			   , ??????			 /* this should be step b) from my previous reply,build a dynamic SQL like select 
    func(cname) from tname where mand= pi_mand and auditing.pid= pi_pid group by group_col1;  pi_pid is the argument passed in 
    the SP. sql should also include acfg.wheresql in the where clause, sql should also include acfg.group_col2, acfg.group_col3
     if there are any values.Execute the sql and using the acfg.message for that row, insert into auditing.message field  */
    		   );
    
    		 Elsif upper(acfg_array(i).v_yn) = 'N' THEN
    			 dbms_output.put_line('Data is ignored');
    		 End If;
    	  End Loop;
       End Loop;
       Close aud_cur;
       Commit;
    
    Exception
       When Others Then
    	  Rollback;
    	  dbms_output.put_line(Sqlerrm(Sqlcode));
    End auditingsp;
    I would appreciate any kind of help.. Please give me your thoughts.

    Thank you very much

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    so... are you asking for somebody to do your job?

    I'll gladly address specific questions, what do you have so far?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Thumbs down Student or what?

    It seems you are either 1) a student with a big homework assignment (or project) or 2) you have deceived your employer into thinking you know PL/SQL but really have no clue.

    If the case is "1) a student", then in order to learn something, you should actually try to do the work yourself and request assistance when you really need it.

    If the case is "2) a deceiver", then you should resign out of moral/ethical (and maybe legal) grounds.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Jul 2009
    Posts
    5
    I've been trying to post this procedure, that I've tried but for some reason it is not getting added to my post...

    I just need help in writing the message into the auditing table..

    Code:
    Create OR Replace Procedure auditingsp( pi_pid    IN Number
                                          , pi_mand   IN Varchar2
                                          , pi_bcode  IN Varchar2
                                          , pi_opp    IN Number
                                          , pi_I_O    IN Varchar2
                                          , pi_Source IN Varchar2 )
     As
       Cursor aud_cur( pi_mand IN Varchar2
                   , pi_I_O  IN Varchar2
                   , pi_bcode  IN Number ) Is
          SELECT v_yn
               , message 
            FROM acfg
           WHERE mand = pi_mand
             AND (pi_I_O = 'I')
              OR (pi_I_O = 'O' AND bcode=pi_bcode);
     
       Type acfg_tab Is Table Of aud_cur%Rowtype;
       acfg_array acfg_tab;
    Begin
       Open aud_cur( pi_mand
                 , pi_I_O
                 , pi_bcode);
       Loop
          Fetch aud_cur Bulk Collect
             INTO acfg_array Limit 1000;
          Exit When aud_cur%Notfound;
       
          For i IN acfg_array.First .. acfg_array.Last Loop
    
             If upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'I' THEN		
                INSERT INTO auditing
                   ( ID
                   , cdate
                   , ccode
                   , pid
                   , mand
    	       , skey
                   , Source
                   , message )
                VALUES
                   ( audseq.Nextval
                   , Sysdate
                   , pi_bcode
                   , pi_pid
                   , pi_mand
    	       , ??????             /* skey needs to be assigned based on the auditing.ccode */
                   , pi_Source
                   , ??????             /* this should be step a) from my previous reply,Build a dynamic SQL like select 
    func(cname) from tname where mand= pi_mand; /* sql should also include acfg.wheresql in the where clause, sql should also 
    include acfg.group_col1,acfg.group_col2, acfg.group_col3 if there are any values. Execute the sql and using the 
    acfg.message for that row, insert into auditing.message field */ 
     	       );
    
             Elsif upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'O' THEN
    		 INSERT INTO auditing
                   ( ID
                   , cdate
                   , ccode
                   , pid
                   , mand
    	       , skey
                   , Source
                   , message )
                VALUES
                   ( audseq.Nextval
                   , Sysdate
                   , pi_bcode
                   , pi_pid
                   , pi_mand
    	       , ??????             /* skey needs to be assigned based on the auditing.ccode */
                   , pi_Source
                   , ??????             /* this should be step b) from my previous reply,build a dynamic SQL like select 
    func(cname) from tname where mand= pi_mand and auditing.pid= pi_pid group by group_col1;  pi_pid is the argument passed in 
    the SP. sql should also include acfg.wheresql in the where clause, sql should also include acfg.group_col2, acfg.group_col3
     if there are any values.Execute the sql and using the acfg.message for that row, insert into auditing.message field  */
    	       );
    
             Elsif upper(acfg_array(i).v_yn) = 'N' THEN
    	         dbms_output.put_line('Data is ignored');
             End If;
          End Loop;
       End Loop;
       Close aud_cur;
       Commit;
    
    Exception
       When Others Then
          Rollback;
          dbms_output.put_line(Sqlerrm(Sqlcode));
    End auditingsp;
    I would appreciate any kind of help.. Please give me your thoughts.

    Thank you very much

    Edited by: newbiegal on Jul 8, 2009 6:33 AM

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Take a look at this:

    http://download.oracle.com/docs/cd/B...a96624/toc.htm

    Then buy and read PL/SQL best practices by Steven Feuerstein.

    Specific tips about your procedure: use implicit cursors when you want to read every record in a cursor or better yet learn about pl/sql collections, "or" clauses tend to be expensive, can you do without it, you have missing business logic that no one outside your company will know, you need to learn how to create and use packages, use functions to encapsulate business logic, and read up on exception handling.

    This is a terrible idea, removing this code would give you more information than leaving it in place.
    Code:
    Exception
       When Others Then
          Rollback;
          dbms_output.put_line(Sqlerrm(Sqlcode));
    But otherwise it probably won't blow up your database.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I don't quite get your logic, but here is a first draft of something that might be close to what you want. You will need to fill in the details and test.

    Code:
    Create OR Replace Procedure auditingsp( 
           pi_bcode  IN Varchar2 )
    AS
       TYPE acfg_tab IS TABLE OF aud_cur%Rowtype;
       acfg_array acfg_tab;
    BEGIN
       SELECT id, v_YN, mand, bcode, skey, i_o, 
              tname, cname, func, group_col1, 
              group_col2, group_col3, message_id, 
              wheresql, message     
         BULK COLLECT INTO acfg_array
         FROM acfg
        INNER JOIN table1
           ON 
        WHERE mand = pi_mand
          AND pi_I_O IN ('I', 'O') 
          AND bcode=pi_bcode;
    
       FOR i IN acfg_array.FIRST..acfg_array.LAST
       LOOP
          IF upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'I' 
          THEN
    			   INSERT INTO auditing
    			        ( ID,             cdate,   ccode,    
                    pid,            mand, 
                    skey,           Source,  message )
             VALUES
    			        ( audseq.Nextval, SYSDATE, i.bcode, 
                    i.pid,          i.mand,  i.skey,
                    i.Source,       i.country )
          ELSE
             dbms_output.put_line('Data is ignored');
          END IF;
       END LOOP;
       COMMIT;
    END auditingsp;
    /

  8. #8
    Join Date
    Jul 2009
    Posts
    5
    Hi Gandolf,

    Stored procedure is created for auditing records in the batch tables. Batch process calls the Stord procedure at the beginning of the batch with 'i' parameter(Only the acfg.i_o='i' records should be audited) and the batch calls the stored procedure with 'o' parameter at the end of the batch(Only the acfg.i_o='o' records should be written to the auditing table. So, the stored procedure writes the message(by evaluating with acfg table) into the auditing table pre batch and post batch. There will be 2 conditions in the procedure:

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

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

    3. If pi_i_o = 'i' then

    a. Build sql evaluating the records in acfg table like
    select func(cname) from tname where mand = pi_mand and
    wheresql ='..' group by group_col1,group_col2,
    group_col3 /* wheresql should be considered when there
    is a where clause in tname for example if there is an
    expression customer=3 in the wheresql. group_col1,
    group_col2, group_col3 should also be considered when
    there is an expression. */

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


    4. If pi_i_o= 'o' then

    a. Build sql evaluating the records in acfg table like
    select func(cname) from tname where mand = pi_mand and
    pid = pi_pid wheresql ='..' group by group_col1,
    group_col2,group_col3 /* wheresql should be considered
    when there is a where clause in tname for example if
    there is an expression customer=3 in the wheresql.
    group_col1,group_col2, group_col3 should also be
    considered when there is an expression. */

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

    I created this procedure below, it works but when I try with the condition for 'i' and 'o' parameters it doesnt work..

    Code:
    create table auditing (
    	id number(5) not null,
    	cdate date,
    	ccode varchar2(10),
    	cterm varchar2(255),
    	pid number(20),
    	mand varchar2(10),
    	skey number(10),
    	message varchar2(2000),
    	source varchar2(255),
    	source_YN varchar2(1)
    	);
    
    create table acfg (
    	id number(10) not null,
    	v_YN varchar2(1),
    	mand varchar2(10),
    	bcode varchar2(10),
    	skey number(10),
    	i_o varchar2(3),
    	tname varchar2(255),
    	cname varchar2(255),
    	func varchar2(64),
    	group_col1 varchar2(255),
    	group_col2 varchar2(255),
    	group_col3 varchar2(255),
    	message_id number(10),
    	wheresql varchar2(2000),
    	message varchar2(2000)
    	);
    	
    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,group_col1,message)
    values (1,'M1','P1',1,'o','table1','country','count','country','%1 entries found for %2');
    
    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
    values (2,'M1','P1',2,'o','table1','firstname','count','%1 total lines');
    
    insert into acfg (id,mand,bcode,skey,i_o,tname,cname,func,message)
    values (3,'M1','P2',1,'i','table1','firstname','count','%1 lines found');
    
    create table table1 
             (country   varchar2(30),
              firstname varchar2(30),
              mand      varchar2(10),
              pid       number(20)
              );   
       
    CREATE SEQUENCE audseq
         MINVALUE 1
            MAXVALUE 999999999999999999999999999
           START WITH 1
            INCREMENT BY 1
            CACHE 2;
    
    CREATE OR REPLACE PROCEDURE auditingsp
          ( pi_pid    IN auditing.pid%TYPE
          , pi_mand   IN acfg.mand%TYPE
          , pi_bcode  IN acfg.bcode%TYPE
          , pi_opp    IN NUMBER
          , pi_i_o    IN acfg.i_o%TYPE
          , pi_source IN auditing.source%TYPE )
         AS
          CURSOR aud_cur IS
      	 SELECT *
      	 FROM	acfg
      	 WHERE    mand = pi_mand
                     AND bcode = pi_bcode
                     AND ((UPPER (pi_I_O) = 'I')
                     OR (UPPER (pi_I_O) = 'O'))
                     ORDER  BY skey;
         TYPE acfg_tab IS TABLE OF aud_cur%ROWTYPE;
         acfg_array	acfg_tab;
         i_sql		VARCHAR2 (32767);
         c1		SYS_REFCURSOR;
         v_num		NUMBER;
         v_col		VARCHAR2 (30);
         BEGIN
         OPEN aud_cur;
         LOOP
       	 FETCH aud_cur BULK COLLECT INTO acfg_array LIMIT 1000;
       	 EXIT WHEN acfg_array.COUNT = 0;
       	 FOR i IN 1 .. acfg_array.COUNT LOOP
       	   i_sql :=
       		   'SELECT ' || acfg_array(i).func || '(*) num, '
      			     || acfg_array(i).cname || ' col'
       	     ||   ' FROM ' || acfg_array(i).tname
       	     ||   ' WHERE mand = :pi_mand'
      	     ||   ' AND ' || NVL (acfg_array(i).wheresql, '1=1')
       	     ||   ' GROUP BY ' || acfg_array(i).cname
       	     ||   ' ORDER BY num DESC';
       	   OPEN c1 FOR i_sql USING pi_mand;
      	   LOOP
       	     FETCH c1 INTO v_num, v_col;
       	     EXIT WHEN c1%NOTFOUND;
       	     INSERT INTO auditing
       	       (id, cdate, ccode, pid, mand, skey, source, message)
       	     SELECT audseq.NEXTVAL, SYSDATE, acfg_array(i).bcode, pi_pid,
       		    acfg_array(i).mand, acfg_array(i).skey, pi_source,
      		    REPLACE (REPLACE (acfg_array(i).message, '%1', v_num), '%2', v_col)
      	     FROM   DUAL;
       	   END LOOP;
       	   CLOSE c1;
       	 END LOOP;
        END LOOP;
         CLOSE aud_cur;
     END auditingsp;
    /
    
    SQL>EXEC auditingsp (12345, 'M1', 'P1', 0, 'i', 'source1')
    
    PL/SQL procedure successfully completed.
    
    SQL>SELECT id, cdate, ccode, pid, mand, skey, message  FROM   auditing;
      
            ID CDATE     CCODE             PID MAND       SKEY MESSAGE
    ---------- --------- ---------- ---------- ---- ---------- ------------------------------
             1 09-JUL-09 P1              12345 M1            1 20 entries found for Germany
             2 09-JUL-09 P1              12345 M1            1 10 entries found for USA
             3 09-JUL-09 P1              12345 M1            1 2 entries found for Denmark
             4 09-JUL-09 P2              12345 M1            1 32 lines found
             5 09-JUL-09 P1              12345 M1            2 32 total lines
    But the sort key needs to be assigned in a sequence for every ccode,pid and mand. Like:

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

  9. #9
    Join Date
    Jul 2009
    Posts
    5
    I tried the below procedure with if else clauses with the conditions 'i' and 'o' parameters,

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

    Line 30 if pi_i_o = 'I' is from the argument passed to the procedure, do I still have to declare it?

    Please help me, thanks very much for your support.

  10. #10
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Try it.

    Try this:
    Code:
    CREATE -- etc --
      -- etc --
      i PLS_INTEGER;
    BEGIN
      -- etc --
    This statement seems to be out of order:
    Code:
      -- etc --
     29   If upper(acfg_array(i).v_yn) = 'Y'  THEN
     30     If UPPER (pi_i_o) = 'I' THEN
     31       FOR i IN 1 .. acfg_array.COUNT LOOP
      -- etc --
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width