Solved

PL/SQL - How do I compare two values in a table and then execute a SP if 'true'?

Posted on 2009-05-04
5
1,856 Views
Last Modified: 2013-12-18
Hello Gurus.
I am writing a stored procedure that will execute another stored procedure if the source_count = target_count in my process log table for the latest job number.  The CREATE table script for my process table is shown below.  I'm thinking I need to find the MAX job_number or the MAX end_date first and then compare the source_count and the target count.  If they are equal, then execute the SP.  If not, then update the process log table.  I'm stuck.  Can someone please nudge [or shove] me in the right direction?
Many thanks!
CREATE TABLE DDR_PROCESS_LOG

(

  PROGRAM_ID     VARCHAR2(30 BYTE),

  ERROR_SQLCODE  VARCHAR2(30 BYTE),

  ERROR_SQLERRM  VARCHAR2(250 BYTE),

  COMMENTS       VARCHAR2(500 BYTE),

  SOURCE_COUNT   VARCHAR2(20 BYTE),

  TARGET_COUNT   VARCHAR2(20 BYTE),

  START_TIME     DATE,

  END_TIME       DATE,

  JOB_NBR        NUMBER(18,1)

)

Open in new window

0
Comment
Question by:hypermac
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 24299846
Assuming that JOB_NBR is first priority,
Here is the procedure.  You might need a little modification on exception handling

CREATE OR REPLACE PROCEDURE pr_exec_procedure
 

v_max_job_nbr     DDR_PROCESS_LOG.JOB_NBR%TYPE;

v_max_job_end_date   DDR_PROCESS_LOG.end_time%TYPE;

v_max_date        DDR_PROCESS_LOG.end_time%TYPE;

v_program_id       DDR_PROCESS_LOG.program_id%TYPE;

v_source_count    DDR_PROCESS_LOG.source_count%TYPE;

v_target_count    DDR_PROCESS_LOG.target_count%TYPE;

AS
 

BEGIN

   SELECT   MJB, END_TIME

     INTO   v_max_job_nbr, v_max_job_end_date, v_program_id

     FROM   (  SELECT   MAX (JOB_NBR) MJB, END_TIME, PROGRAM_ID

                 FROM   DDR_PROCESS_LOG

             GROUP BY   END_TIME

             ORDER BY   MJB DESC)

    WHERE   ROWNUM < 2;
 

   SELECT   MAX (end_time) INTO v_max_date FROM DDR_PROCESS_LOG;
 

   IF v_max_job_end_date <> v_max_date

   THEN

      DBMSOUTPUT.PUT_LINE (

         'MAX JOB NBR AND END_DATE are different records please check'

      );

      RETURN; -- this will not proceed further.  If you want to move ahead, comment this

   END IF;
 

   SELECT   source_count, target_count

     INTO   v_source_count, v_target_count

     FROM   DDR_PROCESS_LOG

    WHERE   program_id = v_program_id;
 

   IF v_source_count = v_target_count

   THEN

      DBMSOUTPUT.PUT_LINE ('call different stored procedure here');

   ELSE

      DBMSOUTPUT.PUT_LINE ('UPDATE DDR_PROCESS_LOG HERE');

   END IF;

END;

Open in new window

0
 

Author Comment

by:hypermac
ID: 24301333
Thanks!  I've attached the entire SP including your recommendations.  My issue now is that I can't get the ADPDDR_HIST_DATA_USD_SP to execute.  I confirmed that the v_source_count = v_target_count.  I've been looking at this for a while and I'm getting nowhere!  Any help would be greatly appreciated!

Thanks Again!
CREATE OR REPLACE PROCEDURE ADPDDR.ADPDDR_MONTHLY_DATALOAD_SP2 IS

tmpVar NUMBER;

/******************************************************************************

   NAME:       ADPDDR_MONTHLY_DATALOAD

   PURPOSE:    
 

   REVISIONS:

   Ver        Date        Author           Description

   ---------  ----------  ---------------  ------------------------------------

   1.0        4/22/2009          1. Created this procedure.
 

   NOTES:
 

   Automatically available Auto Replace Keywords:

      Object Name:     ADPDDR_MONTHLY_DATALOAD

      Sysdate:         4/22/2009

      Date and Time:   4/22/2009, 5:03:48 PM, and 4/22/2009 5:03:48 PM

      Username:         (set in TOAD Options, Procedure Editor)

      Table Name:       (set in the "New PL/SQL Object" dialog)
 

******************************************************************************/

 

PROGRAM_ID  VARCHAR2(30) := 'ADPDDR_MONTHLY_DATALOAD_SP';

l_source_count			 VARCHAR2(20);

l_target_count           VARCHAR2(20);

l_start_time		     DATE;

l_max_job_nbr            NUMBER;

l_source_count2			 VARCHAR2(20);

l_target_count2          VARCHAR2(20);

v_source_count           VARCHAR2(20);

v_target_count           VARCHAR2(20);

v_max_date               DATE;

v_max_job_nbr            NUMBER;

v_max_job_end_date       DATE;

v_program_id             VARCHAR2(30);
 
 
 

BEGIN
 

    l_start_time   := SYSDATE;
 

    SELECT   COUNT( * )

    INTO   l_source_count

    FROM   adp_rpt.adpds_rpt_curprd;
 
 

    DELETE FROM ADPDDR.ADPDDR_CURPRD;

    COMMIT;

    

    INSERT INTO ADPDDR.ADPDDR_CURPRD(MONTH)

    (SELECT MONTH FROM ADP_RPT.ADPDS_RPT_CURPRD);

    

    COMMIT;

    

    SELECT   COUNT( * )

    INTO   l_target_count

    FROM   adpddr.adpddr_curprd;

    

ADPDDR_PROCESS_LOG_SP(PROGRAM_ID,0,NULL,'Monthly DataLoad - Load Current Period - Completed Successfully', l_source_count, l_target_count, l_start_time, SYSDATE); 

 EXCEPTION

    WHEN OTHERS THEN

       ADPDDR_PROCESS_LOG_SP('ADPDDR_MONTHLY_DATALOAD_SP',SQLCODE,SQLERRM,'Raise Application Error - 20013');

  RAISE_APPLICATION_ERROR(-20013, SQLERRM);

  

  COMMIT;

    

   

    SELECT MJB, END_TIME, MJB||program_id

    INTO v_max_job_nbr, v_max_job_end_date, v_program_id

    FROM ( SELECT MAX (JOB_NBR) MJB, END_TIME, program_id

    FROM ADPDDR_PROCESS_LOG

    GROUP BY END_TIME, program_id

    ORDER BY MJB DESC)

    WHERE ROWNUM < 2;
 

    SELECT MAX (end_time) INTO v_max_date FROM ADPDDR_PROCESS_LOG;
 

    IF v_max_job_end_date <> v_max_date

    THEN

    DBMS_OUTPUT.PUT_LINE (

    'MAX JOB NBR AND END_DATE are different records please check'

    );
 

    --RETURN; -- this will not proceed further. If you want to move ahead, comment this

    END IF;

    

    SELECT source_count, target_count

    INTO v_source_count, v_target_count

    FROM ADPDDR_PROCESS_LOG

    WHERE job_nbr = v_max_job_nbr;

    

    IF v_source_count = v_target_count

    THEN

    ADPDDR_HIST_DATA_USD_SP;

    

    END IF;

    

    END;

/

Open in new window

0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24304854
OK,
Lets do tracing.
Compile the following SP.  I have put DBMS_OUTPUT at various points.
Execute the procedure but do remember to put on SET SERVEROUTPUT ON first
Let me know what you get while you execute the SP

CREATE OR REPLACE PROCEDURE ADPDDR.ADPDDR_MONTHLY_DATALOAD_SP2 IS

tmpVar NUMBER;

/******************************************************************************

   NAME:       ADPDDR_MONTHLY_DATALOAD

   PURPOSE:    

 

   REVISIONS:

   Ver        Date        Author           Description

   ---------  ----------  ---------------  ------------------------------------

   1.0        4/22/2009          1. Created this procedure.

 

   NOTES:

 

   Automatically available Auto Replace Keywords:

      Object Name:     ADPDDR_MONTHLY_DATALOAD

      Sysdate:         4/22/2009

      Date and Time:   4/22/2009, 5:03:48 PM, and 4/22/2009 5:03:48 PM

      Username:         (set in TOAD Options, Procedure Editor)

      Table Name:       (set in the "New PL/SQL Object" dialog)

 

******************************************************************************/

 

PROGRAM_ID  VARCHAR2(30) := 'ADPDDR_MONTHLY_DATALOAD_SP';

l_source_count          VARCHAR2(20);

l_target_count           VARCHAR2(20);

l_start_time           DATE;

l_max_job_nbr            NUMBER;

l_source_count2          VARCHAR2(20);

l_target_count2          VARCHAR2(20);

v_source_count           VARCHAR2(20);

v_target_count           VARCHAR2(20);

v_max_date               DATE;

v_max_job_nbr            NUMBER;

v_max_job_end_date       DATE;

v_program_id             VARCHAR2(30);

 

 

 

BEGIN

 

    l_start_time   := SYSDATE;

 

    SELECT   COUNT( * )

    INTO   l_source_count

    FROM   adp_rpt.adpds_rpt_curprd;

 

 

    DELETE FROM ADPDDR.ADPDDR_CURPRD;

    COMMIT;

    

    INSERT INTO ADPDDR.ADPDDR_CURPRD(MONTH)

    (SELECT MONTH FROM ADP_RPT.ADPDS_RPT_CURPRD);

    

    COMMIT;

    

    SELECT   COUNT( * )

    INTO   l_target_count

    FROM   adpddr.adpddr_curprd;

    

ADPDDR_PROCESS_LOG_SP(PROGRAM_ID,0,NULL,'Monthly DataLoad - Load Current Period - Completed Successfully', l_source_count, l_target_count, l_start_time, SYSDATE); 

 EXCEPTION

    WHEN OTHERS THEN

       ADPDDR_PROCESS_LOG_SP('ADPDDR_MONTHLY_DATALOAD_SP',SQLCODE,SQLERRM,'Raise Application Error - 20013');

  RAISE_APPLICATION_ERROR(-20013, SQLERRM);

  

  COMMIT;

    

   

  DBMS_OUTPUT.PUT_LINE('getting max job_nbr and end date');

  

    SELECT MJB, END_TIME, MJB||program_id

    INTO v_max_job_nbr, v_max_job_end_date, v_program_id

    FROM ( SELECT MAX (JOB_NBR) MJB, END_TIME, program_id

    FROM ADPDDR_PROCESS_LOG

    GROUP BY END_TIME, program_id

    ORDER BY MJB DESC)

    WHERE ROWNUM < 2;

   

    DBMS_OUTPUT.PUT_LINE('getting the max date');

    

    SELECT MAX (end_time) INTO v_max_date FROM ADPDDR_PROCESS_LOG;

 

   

    DBMS_OUTPUT.PUT_LINE('comparing dates');

 

    IF v_max_job_end_date <> v_max_date

    THEN

    DBMS_OUTPUT.PUT_LINE (

    'MAX JOB NBR AND END_DATE are different records please check'

    );

 

    --RETURN; -- this will not proceed further. If you want to move ahead, comment this

    END IF;

    

    DBMS_OUTPUT.PUT_LINE('getting source target counts');

    

    SELECT source_count, target_count

    INTO v_source_count, v_target_count

    FROM ADPDDR_PROCESS_LOG

    WHERE job_nbr = v_max_job_nbr;

    

    DBMS_OUTPUT.PUT_LINE('v_source_count => ' || v_source_count);

    DBMS_OUTPUT.PUT_LINE('v_target_count => ' || v_target_count);

    

    IF v_source_count = v_target_count

    THEN

    ADPDDR_HIST_DATA_USD_SP;

    

    END IF;

    

    

    EXCEPTION 

    

    WHEN others THEN

    

      DBMS_OUTPUT.PUT_LINE('EXCEPTION OCCURED '|| SQLERRM);

      

    

    END;

/

 

Open in new window

0
 

Author Comment

by:hypermac
ID: 24305744
Thanks for contiuning to help!  When I try to compile in TOAD I receive the following errors:
ERROR line 112, col 5, ending_line 112, ending_col 13, Found 'EXCEPTION', Expecting CASE or another statement
ERROR line 119, col 8, ending_line 119, ending_col 8, Found ';', Invalid identifier: ;
ERROR line 119, col 8, ending_line 119, ending_col 8, Found ';', Expecting CASE
ERROR line 119, col 8, ending_line 119, ending_col 8,Expecting: ;   -or-   identifier  string

I'll try to start workig through the errors.  Your assistance is greatly appreciated!
0
 
LVL 14

Accepted Solution

by:
ajexpert earned 500 total points
ID: 24305852
Try this

/* Formatted on 5/5/2009 11:35:41 AM (QP5 v5.115.810.9015) */

CREATE OR REPLACE PROCEDURE ADPDDR.ADPDDR_MONTHLY_DATALOAD_SP2

IS

   tmpVar               NUMBER;

   /******************************************************************************

      NAME:       ADPDDR_MONTHLY_DATALOAD

      PURPOSE:
 

      REVISIONS:

      Ver        Date        Author           Description

      ---------  ----------  ---------------  ------------------------------------

      1.0        4/22/2009          1. Created this procedure.
 

      NOTES:
 

      Automatically available Auto Replace Keywords:

         Object Name:     ADPDDR_MONTHLY_DATALOAD

         Sysdate:         4/22/2009

         Date and Time:   4/22/2009, 5:03:48 PM, and 4/22/2009 5:03:48 PM

         Username:         (set in TOAD Options, Procedure Editor)

         Table Name:       (set in the "New PL/SQL Object" dialog)
 

   ******************************************************************************/
 

   PROGRAM_ID           VARCHAR2 (30) := 'ADPDDR_MONTHLY_DATALOAD_SP';

   l_source_count       VARCHAR2 (20);

   l_target_count       VARCHAR2 (20);

   l_start_time         DATE;

   l_max_job_nbr        NUMBER;

   l_source_count2      VARCHAR2 (20);

   l_target_count2      VARCHAR2 (20);

   v_source_count       VARCHAR2 (20);

   v_target_count       VARCHAR2 (20);

   v_max_date           DATE;

   v_max_job_nbr        NUMBER;

   v_max_job_end_date   DATE;

   v_program_id         VARCHAR2 (30);

BEGIN

   l_start_time := SYSDATE;
 

   SELECT   COUNT ( * ) INTO l_source_count FROM adp_rpt.adpds_rpt_curprd;
 
 

   DELETE FROM   ADPDDR.ADPDDR_CURPRD;
 

   COMMIT;
 

   INSERT INTO ADPDDR.ADPDDR_CURPRD (MONTH)

      (  SELECT   MONTH FROM ADP_RPT.ADPDS_RPT_CURPRD);
 

   COMMIT;
 

   SELECT   COUNT ( * ) INTO l_target_count FROM adpddr.adpddr_curprd;
 

   ADPDDR_PROCESS_LOG_SP (

      PROGRAM_ID,

      0,

      NULL,

      'Monthly DataLoad - Load Current Period - Completed Successfully',

      l_source_count,

      l_target_count,

      l_start_time,

      SYSDATE

   );
 
 
 

   DBMS_OUTPUT.PUT_LINE ('getting max job_nbr and end date');
 

   SELECT   MJB, END_TIME, MJB || program_id

     INTO   v_max_job_nbr, v_max_job_end_date, v_program_id

     FROM   (  SELECT   MAX (JOB_NBR) MJB, END_TIME, program_id

                 FROM   ADPDDR_PROCESS_LOG

             GROUP BY   END_TIME, program_id

             ORDER BY   MJB DESC)

    WHERE   ROWNUM < 2;
 

   DBMS_OUTPUT.PUT_LINE ('getting the max date');
 

   SELECT   MAX (end_time) INTO v_max_date FROM ADPDDR_PROCESS_LOG;
 
 

   DBMS_OUTPUT.PUT_LINE ('comparing dates');
 

   IF v_max_job_end_date <> v_max_date

   THEN

      DBMS_OUTPUT.PUT_LINE (

         'MAX JOB NBR AND END_DATE are different records please check'

      );

   --RETURN; -- this will not proceed further. If you want to move ahead, comment this

   END IF;
 

   DBMS_OUTPUT.PUT_LINE ('getting source target counts');
 

   SELECT   source_count, target_count

     INTO   v_source_count, v_target_count

     FROM   ADPDDR_PROCESS_LOG

    WHERE   job_nbr = v_max_job_nbr;
 

   DBMS_OUTPUT.PUT_LINE ('v_source_count => ' || v_source_count);

   DBMS_OUTPUT.PUT_LINE ('v_target_count => ' || v_target_count);
 

   IF v_source_count = v_target_count

   THEN

      ADPDDR_HIST_DATA_USD_SP;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      ADPDDR_PROCESS_LOG_SP ('ADPDDR_MONTHLY_DATALOAD_SP',

                             SQLCODE,

                             SQLERRM,

                             'Raise Application Error - 20013');

      RAISE_APPLICATION_ERROR (-20013, SQLERRM);
 

      COMMIT;

END ADPDDR_MONTHLY_DATALOAD_SP2;

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pl/SQL Query 31 74
How to count the number of rows in multiple Oracle Tables 10 60
SQL Query - Oracle 10g - Subract date from next data row 4 48
Oracle Subquery bad Join 11 59
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now