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,853 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

771 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

11 Experts available now in Live!

Get 1:1 Help Now