Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
1,885 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

704 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