1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PLSQL help

Discussion in 'SQL PL/SQL' started by newtoplsql, Jul 8, 2009.

  1. newtoplsql

    newtoplsql Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    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 (Text):
    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 (Text):
    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. newtoplsql

    newtoplsql Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I've created this below procedure... But, still struggling on writing the message field..

    Code (Text):


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

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Looks like a real life situation problem . .. .

    1. First Of all you need to have One Function that forms and retuns the SQL what you need with 3 group by columns and One aggregare function colum.

    2. Then Need to Execute the SQL returned form the function dynamically and store the result into another Array table (rather than a variable) . Pl/sql table shoul alos have 3 fieds some thing like group1_col1 ,group1_col2 ,group1_col3 , function_cal.

    3. Loop through this table and insert into auditing table inside this loop with corresponding skey value. ie, Insertion into auditing table should be inside the loop of pl/sql table derived out of dynamic exicution of SQL.

    You can try the following code for returning dynamically formed SQL.

    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION SCOTT.FORM_SQL( i_pid    IN NUMBER,
      2                                             i_bcode  IN Varchar2 )
      3  RETURN VARCHAR2
      4  AS
      5  sql_str    VARCHAR2(500);
      6  BEGIN
      7  sql_str := 'SELECT ';
      8
      9      SELECT UPPER(sql_str ||NVL(GROUP_COL1,'NULL')||','||NVL(GROUP_COL2,'NULL')||','||NVL(GROUP_COL3,'NULL')||','||
     10             FUNC||'('||CNAME||') FROM '||TNAME||
     11             DECODE(WHERESQL,NULL,NULL,' WHERE '||WHERESQL)||
     12             DECODE(GROUP_COL1,NULL,NULL,' GROUP BY '||GROUP_COL1||
     13                      DECODE(GROUP_COL2,NULL,NULL,','||GROUP_COL2||
     14                              DECODE(GROUP_COL3,NULL,NULL,','||GROUP_COL3))))
     15        INTO sql_str
     16        FROM ACFG
     17       WHERE ID  = i_pid
     18         AND BCODE = i_bcode;
     19
     20      RETURN sql_str;
     21  Exception
     22     WHEN Others THEN
     23      RETURN 'SELECT NULL,NULL,NULL,0 FROM DUAL';
     24  END FORM_SQL;
     25  /

    FUNCTION created.

    SQL> SELECT FORM_SQL(1,'P1') FROM DUAL;

    FORM_SQL(1,'P1')
    --------------------------------------------------------------------------------
    --------------------
    SELECT COUNTRY,NULL,NULL,COUNT(COUNTRY) FROM TABLE1 GROUP BY COUNTRY

    SQL> SELECT FORM_SQL(2,'P1') FROM DUAL;

    FORM_SQL(2,'P1')
    --------------------------------------------------------------------------------
    --------------------
    SELECT NULL,NULL,NULL,COUNT(FIRSTNAME) FROM TABLE1 WHERE FLG='5'

    SQL> SELECT FORM_SQL(3,'P2') FROM DUAL;

    FORM_SQL(3,'P2')
    --------------------------------------------------------------------------------
    --------------------
    SELECT COUNTRY,FIRSTNAME,NULL,COUNT(FIRSTNAME) FROM TABLE1 WHERE FLG='6' GROUP BY COUNTRY,FIRSTNAME

    SQL> SELECT FORM_SQL(4,'P2') FROM DUAL;

    FORM_SQL(4,'P2')
    --------------------------------------------------------------------------------
    --------------------
    SELECT COUNTRY,STATE,LASTNAME,COUNT(LASTNAME) FROM TABLE2 GROUP BY COUNTRY,STATE,LASTNAME

    SQL>