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  |
 |
swapnabpnn
Messages: 68
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 #573147 is a reply to message #573146] |
Fri, 21 December 2012 14:49  |
|
swapnabpnn
Messages: 68
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 #573150 is a reply to message #573149] |
Fri, 21 December 2012 15:51 |
|
swapnabpnn
Messages: 68
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>
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 22 21:32:44 CDT 2013
Total time taken to generate the page: 0.31501 seconds
|
] 
Members
Search
Help
Register
Login
Home
