Home » SQL & PL/SQL » SQL & PL/SQL » insert or update records into target table (oracle 11g)
insert or update records into target table [message #573140] Fri, 21 December 2012 12:40 Go to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Hi,
I am trying to insert rec into target table if those rec are not existing and trying to update those rec if they already exists from three source tables.I had seen in posts that merge cannot be used with cursor.So can anyone please suggest me how to approach this task and finish my code.

SQL> create or replace
  2  PACKAGE                                sis_l_cpl_sis_reb_pgm_hist_pkg
  3  IS
  4     /********************************************************************
******************
  5        PACKAGE:    sis_load_cpl_sis_reb_pgm_hist
  6        PURPOSE:    Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_p
h_dtl,cmply_sis_sls_dtl,
  7                    cmply_sis_excl_dtl(intial load)
  8     *********************************************************************
******************/
  9
 10      -- load data from source  tables to target
 11
 12      FUNCTION fn_load_cpl_sis_reb_pgm_hist(P_RUN_DATE IN DATE,P_PGM_MSTR_
 IN CHAR ,P_SIS_INV_NBR IN VARCHAR2) RETURN  NUMBER;--ongoing
 13
 14
 15  /***********************************************************************
******
 16  ** Package Name:      sis_load_cpl_sis_reb_pgm_hist
*
 17  ** Function Name:     fn_load1t_cpl_sis_reb_pgm_hist
        *
 18  **
     *
 19  ** Description:       Load CMPLY_SIS_REB_PGM_HIST with data from
 20                        cmply_sis_purch_dtl,cmply_sis_sls_dtl,
 21                        cmply_sis_excl_dtl(intial load)
 *
 22  **
     *
 23  ** Source Tables:      cmply_sis_purch_dtl,
 24                         cmply_sis_sls_dtl,
 25                         cmply_sis_excl_dtl .
 *
 26  **
     *
 27  ** Target Table:      CMPLY_SIS_REB_PGM_HIST
        *
 28  **
     *
 29  ** Date          Who                      Desscription
     *
 30  ** ----------    --------------------    -------------------------------
---- *
 31  ** 12/14/2012    Swapna Boppana     Created
 32    **********************************************************************
*****/
 33
 34  END sis_l_cpl_sis_reb_pgm_hist_pkg;
 35  /

Package created.


SQL> create or replace
  2  PACKAGE BODY
        sis_l_cpl_sis_reb_pgm_hist_pkg
  3  IS
  4     /**********************************************************************
******************
  5        PACKAGE:    sis_l_cpl_sis_reb_pgm_hist_pkg
  6        PURPOSE:    Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_pur
h_dtl,cmply_sis_sls_dtl,
  7                    cmply_sis_excl_dtl(intial load)
  8     ***********************************************************************
******************/
  9
 10     FUNCTION fn_load_cpl_sis_reb_pgm_hist(P_RUN_DATE IN DATE,P_PGM_MSTR_NBR
IN CHAR ,P_SIS_INV_NBR IN VARCHAR2) RETURN  NUMBER
 11      IS
 12
 13
 14  /*************************************************************************
******
 15  ** Package Name:      sis_l_cpl_sis_reb_pgm_hist_pkg                     *
 16  ** Function Name:     fn_load_cpl_sis_reb_pgm_hist
      *
 17  **
     *
 18  ** Description:       update CMPLY_SIS_REB_PGM_HIST to reflect new amounts
from
 19                        cmply_sis_purch_dtl,cmply_sis_sls_dtl,
 20                        cmply_sis_excl_dtl(ongoing load)
  *
 21  **
     *
 22  ** Source Tables:      cmply_sis_purch_dtl,
 23                         cmply_sis_sls_dtl,
 24                         cmply_sis_excl_dtl .
 *
 25  **
     *
 26  ** Target Table:      CMPLY_SIS_REB_PGM_HIST
        *
 27  **
     *
 28  ** Date          Who                      Desscription
     *
 29  ** ----------    --------------------    ---------------------------------
---- *
 30  **                              *
 31    ************************************************************************
*****/
 32
 33      v_proc_name   VARCHAR2 (100) := casadm.fn_who_am_i (0);
 34        v_row_count   NUMBER := 0;                  -- counter for inserted
ows
 35        v_stat        NUMBER := 0;                  -- gather stats return v
lue
 36        v_rval        NUMBER   := 0;
 37       Cursor pgm_hist  IS
 38          SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,trim(FISC_MTH_OF_YR), SUM(SUM
AMT)
 39                FROM
 40                    (SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,FISC_MTH_OF_YR,SUM
REB_AMT)SUM_AMT
 41                     FROM CASADM.CMPLY_SIS_PURCH_DTL,CASADM.MT_TIME_CORP
 42                     WHERE PROCESS_DT= CLNDR_DT AND RUN_DATE=P_RUN_DATE AND
GM_MSTR_NBR=P_PGM_MSTR_NBR AND SIS_INV_NBR=P_SIS_INV_NBR
 43                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR
 44                     UNION ALL
 45                     SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,FISC_MTH_OF_YR,SUM
REB_AMT)SUM_AMT
 46                     FROM CASADM.CMPLY_SIS_EXCL_DTL,CASADM.MT_TIME_CORP
 47                     WHERE PRCS_DT= CLNDR_DT AND RUN_DATE=P_RUN_DATE AND PGM
MSTR_NBR=P_PGM_MSTR_NBR AND SIS_INV_NBR=P_SIS_INV_NBR
 48                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR
 49                     UNION ALL
 50                     SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,FISC_MTH_OF_YR,SUM
REB_AMT)SUM_AMT
 51                     FROM CASADM.CMPLY_SIS_SLS_DTL,CASADM.MT_TIME_CORP
 52                     WHERE PRCS_DT= CLNDR_DT AND RUN_DATE=P_RUN_DATE AND PGM
MSTR_NBR=P_PGM_MSTR_NBR AND SIS_INV_NBR=P_SIS_INV_NBR
 53                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR)
 54                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR;
 55
 56
 57
 58     BEGIN
 59       dbms_output.put_line('-----------------------------------------------
------------');
 60       dbms_output.put_line('Process:  ' || v_proc_name);
 61       dbms_output.put_line('-----------------------------------------------
------------');
 62       dbms_output.put_line('Process started at ' || TO_CHAR(SYSDATE, 'mm/dd
yyyy hh24:mi:ss'));
 63       dbms_output.put_line('-----------------------------------------------
------------');
 64       dbms_output.put_line('Load the CMPLY_SIS_REB_PGM from SIS_CMPLNC_HDR_
SFDW');
 65       dbms_output.put_line ('Before Insert');
 66
 67       FOR updrec IN pgm_hist LOOP
 68        MERGE
 69             INTO casadm.CMPLY_SIS_REB_PGM_HIST s
 70              USING pgm_hist  t
 71              ON (s.PGM_TRK_NBR=t.PGM_TRK_NBR AND s.CNTL_LOCN=t.CNTL_LOCN AN
 s.FISC_YR=t.FISC_YR
 72                  AND s.FISC_MTH_OF_YR=t.FISC_MTH_OF_YR)
 73        WHEN MATCHED THEN
 74             UPDATE SET s.REB_AMT=s.REB_AMT+t.SUM(SUM_AMT)
 75        WHEN NOT MATCHED THEN
 76             INSERT INTO casadm.CMPLY_SIS_REB_PGM_HIST(PGM_TRK_NBR,
 77                                                CNTL_LOCN,
 78                                                FISC_YR,
 79                                                FISC_MTH_OF_YR,
 80                                                REB_AMT)
 81                                                VALUES
 82                                                (t.PGM_TRK_NBR,
 83                                                t.CNTL_LOCN,
 84                                                t.FISC_YR,
 85                                                t.FISC_MTH_OF_YR,
 86                                                t.SUM(SUM_AMT));
 87
 88     END LOOP;
 89
 90
 91        v_row_count := SQL%ROWCOUNT;
 92        dbms_output.put_line('Inserted ' || v_row_count || ' rows ' || TO_CH
R(SYSDATE, 'mm/dd/yyyy hh24:mi:ss'));
 93
 94
 95       v_stat:= casadm.fn_gather_statistic (p_owner         => 'CASADM',
 96                                            p_table         => 'CMPLY_SIS_RE
_PGM_HIST',
 97                                            p_granularity   => 'PARTITION');
 98      dbms_output.put_line('Gather Statistics Returns: ' || v_stat);
 99      dbms_output.put_line('Process Ended at: ' || TO_CHAR(SYSDATE, 'mm/dd/y
yy hh24:mi:ss'));
100       RETURN v_rval;
101     EXCEPTION
102        WHEN OTHERS
103        THEN
104           dbms_output.put_line('Process Ended at: ' || TO_CHAR(SYSDATE, 'mm
dd/yyyy hh24:mi:ss'));
105           dbms_output.put_line('Error message:    ' || SQLCODE || SQLERRM);
106           dbms_output.put_line(casadm.fn_who_am_i(0) || chr(10) || ' CALLED
FROM ' || casadm.fn_who_am_i(1) || chr(10) || ' CALLED FROM ' || casadm.fn_who_
m_i(2) || chr(10) || ' TERMINATED: INTERNAL ERROR OCCURRED.  ');
107           dbms_output.put_line(rtrim(dbms_utility.format_error_stack, chr(1
)));
108           dbms_output.put_line(rtrim(dbms_utility.format_error_backtrace, c
r(10)));
109          ROLLBACK;
110      --   RETURN - 1;
111          RAISE;
112     END fn_load_cpl_sis_reb_pgm_hist;
113     END sis_l_cpl_sis_reb_pgm_hist_pkg;
114  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY SIS_L_CPL_SIS_REB_PGM_HIST_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
67/7     PL/SQL: SQL Statement ignored
75/19    PL/SQL: ORA-00926: missing VALUES keyword


Thanks
Swapna.
Re: insert or update records into target table [message #573142 is a reply to message #573140] Fri, 21 December 2012 13:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
swapnabpnn wrote on Fri, 21 December 2012 13:40
Hi,
I am trying to insert rec into target table if those rec are not existing and trying to update those rec if they already exists from three source tables.


Scrap your code and read docs on MERGE statement.

SY.
Re: insert or update records into target table [message #573144 is a reply to message #573142] Fri, 21 December 2012 13:34 Go to previous messageGo to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
What do you mean by scrap your code?
Re: insert or update records into target table [message #573145 is a reply to message #573144] Fri, 21 December 2012 13:44 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>What do you mean by scrap your code?
no PL/SQL is required; just a single MERGE statement is sufficient
Re: insert or update records into target table [message #573146 is a reply to message #573145] Fri, 21 December 2012 14:16 Go to previous messageGo to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
ok thank you.I will execute and will post the code.

Thanks
Re: insert or update records into target table [message #573147 is a reply to message #573146] Fri, 21 December 2012 14:49 Go to previous messageGo to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
I tried to implement without plsql in my code and using merge by reading the document.still i am getting the below error.Can any please help.

SQL> create or replace
  2  PACKAGE BODY
        sis_l_cpl_sis_reb_pgm_hist_pkg
  3  IS
  4     /***********************************************************************
******************
  5        PACKAGE:    sis_l_cpl_sis_reb_pgm_hist_pkg
  6        PURPOSE:    Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_purc
h_dtl,cmply_sis_sls_dtl,
  7                    cmply_sis_excl_dtl(intial load)
  8     ************************************************************************
******************/
  9
 10     FUNCTION fn_load_cpl_sis_reb_pgm_hist(P_RUN_DATE IN DATE,P_PGM_MSTR_NBR
IN CHAR ,P_SIS_INV_NBR IN VARCHAR2) RETURN  NUMBER
 11      IS
 12
 13
 14  /**************************************************************************
******
 15  ** Package Name:      sis_l_cpl_sis_reb_pgm_hist_pkg                     *
 16  ** Function Name:     fn_load_cpl_sis_reb_pgm_hist
      *
 17  **
     *
 18  ** Description:       update CMPLY_SIS_REB_PGM_HIST to reflect new amounts
from
 19                        cmply_sis_purch_dtl,cmply_sis_sls_dtl,
 20                        cmply_sis_excl_dtl(ongoing load)
  *
 21  **
     *
 22  ** Source Tables:      cmply_sis_purch_dtl,
 23                         cmply_sis_sls_dtl,
 24                         cmply_sis_excl_dtl .
 *
 25  **
     *
 26  ** Target Table:      CMPLY_SIS_REB_PGM_HIST
        *
 27  **
     *
 28  ** Date          Who                      Desscription
     *
 29  ** ----------    --------------------    ----------------------------------
---- *
 30  **                              *
 31    *************************************************************************
*****/
 32
 33      v_proc_name   VARCHAR2 (100) := casadm.fn_who_am_i (0);
 34        v_row_count   NUMBER := 0;                  -- counter for inserted r
ows
 35        v_stat        NUMBER := 0;                  -- gather stats return va
lue
 36        v_rval        NUMBER   := 0;
 37
 38
 39     BEGIN
 40       dbms_output.put_line('------------------------------------------------
------------');
 41       dbms_output.put_line('Process:  ' || v_proc_name);
 42       dbms_output.put_line('------------------------------------------------
------------');
 43       dbms_output.put_line('Process started at ' || TO_CHAR(SYSDATE, 'mm/dd/
yyyy hh24:mi:ss'));
 44       dbms_output.put_line('------------------------------------------------
------------');
 45       dbms_output.put_line('Load the CMPLY_SIS_REB_PGM from SIS_CMPLNC_HDR_U
SFDW');
 46       dbms_output.put_line ('Before Insert');
 47
 48
 49        MERGE
 50             INTO casadm.CMPLY_SIS_REB_PGM_HIST s
 51              USING (SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,trim(FISC_MTH_OF_YR
), SUM(SUM_AMT) g
 52                FROM
 53                    (SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,FISC_MTH_OF_YR,SUM(
REB_AMT)SUM_AMT
 54                     FROM CASADM.CMPLY_SIS_PURCH_DTL,CASADM.MT_TIME_CORP
 55                     WHERE PROCESS_DT= CLNDR_DT AND RUN_DATE=P_RUN_DATE AND P
GM_MSTR_NBR=P_PGM_MSTR_NBR AND SIS_INV_NBR=P_SIS_INV_NBR
 56                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR
 57                     UNION ALL
 58                     SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,FISC_MTH_OF_YR,SUM(
REB_AMT)SUM_AMT
 59                     FROM CASADM.CMPLY_SIS_EXCL_DTL,CASADM.MT_TIME_CORP
 60                     WHERE PRCS_DT= CLNDR_DT AND RUN_DATE=P_RUN_DATE AND PGM_
MSTR_NBR=P_PGM_MSTR_NBR AND SIS_INV_NBR=P_SIS_INV_NBR
 61                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR
 62                     UNION ALL
 63                     SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,FISC_MTH_OF_YR,SUM(
REB_AMT)SUM_AMT
 64                     FROM CASADM.CMPLY_SIS_SLS_DTL,CASADM.MT_TIME_CORP
 65                     WHERE PRCS_DT= CLNDR_DT AND RUN_DATE=P_RUN_DATE AND PGM_
MSTR_NBR=P_PGM_MSTR_NBR AND SIS_INV_NBR=P_SIS_INV_NBR
 66                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR)
 67                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR)t

 68              ON (s.PGM_TRK_NBR=t.PGM_TRK_NBR AND s.CNTL_LOCN=t.CNTL_LOCN AND
 s.FISC_YR=t.FISC_YR
 69                  AND s.FISC_MTH_OF_YR=t.FISC_MTH_OF_YR)
 70        WHEN MATCHED THEN
 71             UPDATE SET s.REB_AMT=s.REB_AMT+t*g
 72        WHEN NOT MATCHED THEN
 73             INSERT INTO casadm.CMPLY_SIS_REB_PGM_HIST(s.PGM_TRK_NBR,
 74                                                s.CNTL_LOCN,
 75                                                s.FISC_YR,
 76                                                s.FISC_MTH_OF_YR,
 77                                                s.REB_AMT)
 78                                                VALUES
 79                                                (t.PGM_TRK_NBR,
 80                                                t.CNTL_LOCN,
 81                                                t.FISC_YR,
 82                                                t.FISC_MTH_OF_YR,
 83                                                t.g);
 84
 85
 86
 87
 88        v_row_count := SQL%ROWCOUNT;
 89        dbms_output.put_line('Inserted ' || v_row_count || ' rows ' || TO_CHA
R(SYSDATE, 'mm/dd/yyyy hh24:mi:ss'));
 90
 91
 92       v_stat:= casadm.fn_gather_statistic (p_owner         => 'CASADM',
 93                                            p_table         => 'CMPLY_SIS_REB
_PGM_HIST',
 94                                            p_granularity   => 'PARTITION');
 95      dbms_output.put_line('Gather Statistics Returns: ' || v_stat);
 96      dbms_output.put_line('Process Ended at: ' || TO_CHAR(SYSDATE, 'mm/dd/yy
yy hh24:mi:ss'));
 97       RETURN v_rval;
 98     EXCEPTION
 99        WHEN OTHERS
100        THEN
101           dbms_output.put_line('Process Ended at: ' || TO_CHAR(SYSDATE, 'mm/
dd/yyyy hh24:mi:ss'));
102           dbms_output.put_line('Error message:    ' || SQLCODE || SQLERRM);
103           dbms_output.put_line(casadm.fn_who_am_i(0) || chr(10) || ' CALLED
FROM ' || casadm.fn_who_am_i(1) || chr(10) || ' CALLED FROM ' || casadm.fn_who_a
m_i(2) || chr(10) || ' TERMINATED: INTERNAL ERROR OCCURRED.  ');
104           dbms_output.put_line(rtrim(dbms_utility.format_error_stack, chr(10
)));
105           dbms_output.put_line(rtrim(dbms_utility.format_error_backtrace, ch
r(10)));
106          ROLLBACK;
107      --   RETURN - 1;
108          RAISE;
109     END fn_load_cpl_sis_reb_pgm_hist;
110     END sis_l_cpl_sis_reb_pgm_hist_pkg;
111  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY SIS_L_CPL_SIS_REB_PGM_HIST_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
48/7     PL/SQL: SQL Statement ignored
72/19    PL/SQL: ORA-00926: missing VALUES keyword
SQL>


Re: insert or update records into target table [message #573148 is a reply to message #573147] Fri, 21 December 2012 15:12 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>72/19 PL/SQL: ORA-00926: missing VALUES keyword
What part of the line above do you NOT understand?
Re: insert or update records into target table [message #573149 is a reply to message #573147] Fri, 21 December 2012 15:15 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
You seem to have combined a regular INSERT and an INSERT as part of a MERGE statement.

There is no INSERT INTO when you use MERGE. It's simply INSERT.

[Updated on: Fri, 21 December 2012 15:18]

Report message to a moderator

Re: insert or update records into target table [message #573150 is a reply to message #573149] Fri, 21 December 2012 15:51 Go to previous messageGo to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
I changed insert into insert and try to run the code,still I am getting same error.


SQL> create or replace
  2  PACKAGE BODY
        sis_l_cpl_sis_reb_pgm_hist_pkg
  3  IS
  4     /***********************************************************************
******************
  5        PACKAGE:    sis_l_cpl_sis_reb_pgm_hist_pkg
  6        PURPOSE:    Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_purc
h_dtl,cmply_sis_sls_dtl,
  7                    cmply_sis_excl_dtl(intial load)
  8     ************************************************************************
******************/
  9
 10     FUNCTION fn_load_cpl_sis_reb_pgm_hist(P_RUN_DATE IN DATE,P_PGM_MSTR_NBR
IN CHAR ,P_SIS_INV_NBR IN VARCHAR2) RETURN  NUMBER
 11      IS
 12
 13
 14  /**************************************************************************
******
 15  ** Package Name:      sis_l_cpl_sis_reb_pgm_hist_pkg                     *
 16  ** Function Name:     fn_load_cpl_sis_reb_pgm_hist
      *
 17  **
     *
 18  ** Description:       update CMPLY_SIS_REB_PGM_HIST to reflect new amounts
from
 19                        cmply_sis_purch_dtl,cmply_sis_sls_dtl,
 20                        cmply_sis_excl_dtl(ongoing load)
  *
 21  **
     *
 22  ** Source Tables:      cmply_sis_purch_dtl,
 23                         cmply_sis_sls_dtl,
 24                         cmply_sis_excl_dtl .
 *
 25  **
     *
 26  ** Target Table:      CMPLY_SIS_REB_PGM_HIST
        *
 27  **
     *
 28  ** Date          Who                      Desscription
     *
 29  ** ----------    --------------------    ----------------------------------
---- *
 30  **                              *
 31    *************************************************************************
*****/
 32
 33      v_proc_name   VARCHAR2 (100) := casadm.fn_who_am_i (0);
 34        v_row_count   NUMBER := 0;                  -- counter for inserted r
ows
 35        v_stat        NUMBER := 0;                  -- gather stats return va
lue
 36        v_rval        NUMBER   := 0;
 37
 38
 39     BEGIN
 40       dbms_output.put_line('------------------------------------------------
------------');
 41       dbms_output.put_line('Process:  ' || v_proc_name);
 42       dbms_output.put_line('------------------------------------------------
------------');
 43       dbms_output.put_line('Process started at ' || TO_CHAR(SYSDATE, 'mm/dd/
yyyy hh24:mi:ss'));
 44       dbms_output.put_line('------------------------------------------------
------------');
 45       dbms_output.put_line('Load the CMPLY_SIS_REB_PGM from SIS_CMPLNC_HDR_U
SFDW');
 46       dbms_output.put_line ('Before Insert');
 47
 48
 49
 50        MERGE
 51             INTO casadm.CMPLY_SIS_REB_PGM_HIST t
 52              USING (SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,trim(FISC_MTH_OF_YR
), SUM(SUM_AMT) g
 53                FROM
 54                    (SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,FISC_MTH_OF_YR,SUM(
REB_AMT)SUM_AMT
 55                     FROM CASADM.CMPLY_SIS_PURCH_DTL,CASADM.MT_TIME_CORP
 56                     WHERE PROCESS_DT= CLNDR_DT AND RUN_DATE=P_RUN_DATE AND P
GM_MSTR_NBR=P_PGM_MSTR_NBR AND SIS_INV_NBR=P_SIS_INV_NBR
 57                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR
 58                     UNION ALL
 59                     SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,FISC_MTH_OF_YR,SUM(
REB_AMT)SUM_AMT
 60                     FROM CASADM.CMPLY_SIS_EXCL_DTL,CASADM.MT_TIME_CORP
 61                     WHERE PRCS_DT= CLNDR_DT AND RUN_DATE=P_RUN_DATE AND PGM_
MSTR_NBR=P_PGM_MSTR_NBR AND SIS_INV_NBR=P_SIS_INV_NBR
 62                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR
 63                     UNION ALL
 64                     SELECT PGM_TRK_NBR,CNTL_LOCN,FISC_YR,FISC_MTH_OF_YR,SUM(
REB_AMT)SUM_AMT
 65                     FROM CASADM.CMPLY_SIS_SLS_DTL,CASADM.MT_TIME_CORP
 66                     WHERE PRCS_DT= CLNDR_DT AND RUN_DATE=P_RUN_DATE AND PGM_
MSTR_NBR=P_PGM_MSTR_NBR AND SIS_INV_NBR=P_SIS_INV_NBR
 67                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR)
 68                     GROUP BY PGM_TRK_NBR,CNTL_LOCN,FISC_YR, FISC_MTH_OF_YR)s

 69              ON (t.PGM_TRK_NBR=s.PGM_TRK_NBR AND t.CNTL_LOCN=s.CNTL_LOCN AND
 t.FISC_YR=s.FISC_YR
 70                  AND t.FISC_MTH_OF_YR=s.FISC_MTH_OF_YR)
 71        WHEN MATCHED THEN
 72             UPDATE SET t.REB_AMT=t.REB_AMT+s*g
 73        WHEN NOT MATCHED THEN
 74             INSERT  casadm.CMPLY_SIS_REB_PGM_HIST(t.PGM_TRK_NBR,
 75                                                t.CNTL_LOCN,
 76                                                t.FISC_YR,
 77                                                t.FISC_MTH_OF_YR,
 78                                                t.REB_AMT)
 79                                                VALUES
 80                                                (s.PGM_TRK_NBR,
 81                                                s.CNTL_LOCN,
 82                                                s.FISC_YR,
 83                                                s.FISC_MTH_OF_YR,
 84                                                s.g);
 85
 86
 87
 88
 89        v_row_count := SQL%ROWCOUNT;
 90        dbms_output.put_line('Inserted ' || v_row_count || ' rows ' || TO_CHA
R(SYSDATE, 'mm/dd/yyyy hh24:mi:ss'));
 91
 92
 93       v_stat:= casadm.fn_gather_statistic (p_owner         => 'CASADM',
 94                                            p_table         => 'CMPLY_SIS_REB
_PGM_HIST',
 95                                            p_granularity   => 'PARTITION');
 96      dbms_output.put_line('Gather Statistics Returns: ' || v_stat);
 97      dbms_output.put_line('Process Ended at: ' || TO_CHAR(SYSDATE, 'mm/dd/yy
yy hh24:mi:ss'));
 98       RETURN v_rval;
 99     EXCEPTION
100        WHEN OTHERS
101        THEN
102           dbms_output.put_line('Process Ended at: ' || TO_CHAR(SYSDATE, 'mm/
dd/yyyy hh24:mi:ss'));
103           dbms_output.put_line('Error message:    ' || SQLCODE || SQLERRM);
104           dbms_output.put_line(casadm.fn_who_am_i(0) || chr(10) || ' CALLED
FROM ' || casadm.fn_who_am_i(1) || chr(10) || ' CALLED FROM ' || casadm.fn_who_a
m_i(2) || chr(10) || ' TERMINATED: INTERNAL ERROR OCCURRED.  ');
105           dbms_output.put_line(rtrim(dbms_utility.format_error_stack, chr(10
)));
106           dbms_output.put_line(rtrim(dbms_utility.format_error_backtrace, ch
r(10)));
107          ROLLBACK;
108      --   RETURN - 1;
109          RAISE;
110     END fn_load_cpl_sis_reb_pgm_hist;
111     END sis_l_cpl_sis_reb_pgm_hist_pkg;
112  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY SIS_L_CPL_SIS_REB_PGM_HIST_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
49/7     PL/SQL: SQL Statement ignored
73/20    PL/SQL: ORA-00926: missing VALUES keyword
SQL>

Re: insert or update records into target table [message #573151 is a reply to message #573150] Fri, 21 December 2012 16:50 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
You can lead some folks to knowledge, but you can't make them think.
>73/20 PL/SQL: ORA-00926: missing VALUES keyword
Re: insert or update records into target table [message #573299 is a reply to message #573150] Wed, 26 December 2012 08:15 Go to previous message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
I think it is time to look up the proper syntax for a MERGE statement. Your INSERT is incorrect.
Previous Topic: How to call a URL in PL/SQL
Next Topic: triggers in oracle plsql
Goto Forum:
  


Current Time: Fri Aug 29 23:47:12 CDT 2014

Total time taken to generate the page: 0.09367 seconds