The CREATE OR REPLACE PROCEDURE cs (pi_StartFetchTime IN DATE,
Pi_EndFetchTime IN DATE,
Po_OutErrCode OUT VARCHAR2,
Po_OutErrMsg OUT VARCHAR2) IS
L_sql varchar (2500);
The BEGIN


L_sql:="insert into TEMP_DWD_TEST_SBLSYL '
| | '('
| | 'OBJECT_RRN,'
| | 'LOT_ID,'
| | 'SHIFT,'
| | 'CLASS,'
| | 'APS_DATE,'
| | 'APS_MONTH,'
| | 'PART_NAME,'
| | 'APS_WEEK,'
| | 'CUSTOM_GROUP,'
| | 'PKG_TYPE,'
| | 'PKG_SORT'
| | ')
| | 'SELECT T.O BJECT_RRN,'
| | 'T.L OT_ID,'
| | 'T.S HIFT,'
| | 'tc LASS,'
| | 'T.A PS_DATE,'
| | 'T.A PS_MONTH,'
| | 'T.P ART_NAME,'
| | 'T.A PS_WEEK,'
| | 'CASE WHEN T.P ART_NAME LIKE' % 'S -' THEN ' 'AUTO' 'WHEN T.P ART_NAME LIKE' '% - HW' 'THEN' 'HW' 'ELSE NULL END AS CUSTOM_GROUP,'
| | 'T.P KG_TYPE,'
| | 'W.P KG_SORT'
| | 'FROM (SELECT A.O BJECT_RRN,'
| | 'arjun _lot LOT_ID,'
| | 'fnc_get_lot_device (A.s ub_lot part_name),'
| | 'CASE WHEN B.S YS_DATE & lt; B.S YS_DATE + 1/2 THEN 'work days' ELSE' night 'END AS the SHIFT,'
| | 'FNC_GET_CLASS (A.U PDATED) CLASS,'
| | 'TRUNC (A.U PDATED - 11/32) APS_DATE,'
| | 'B.Y EAR | | LPAD (B.W EEK, 2' 0 ') APS_WEEK, '
| | 'B.Y EAR | | LPAD (B.M ONTH, 2' 0 ') APS_MONTH, '
| | 'a.s ub_lot,'
| | 'fnc_get_lot_PKG (A.s ub_lot) PKG_TYPE'
| | 'FROM rpt_ods_test_sblsyl A, RPT_RBD_CALENDAR_VIEW B'
| | 'where B.S YS_DATE=TRUNC (A.U PDATED - 11/32)'
| | 'AND A.S UB_LOT IS NOT NULL,'
| | 'AND Anderson S_EXCEPTION=' 1 ' ' '
| | 'AND A.U PDATED & gt;="' | | pi_StartFetchTime | | '"'
| | 'AND A.U PDATED & lt; "' | | pi_EndFetchTime | | 'T' ' ')
| | 'LEFT JOIN RPT_DIM_PKG_SORT W'
| | 'ON t.P KG_TYPE=W.P KG_TYPE';
The execute immediate l_sql;

COMMIT;

The EXCEPTION
The WHEN OTHERS THEN
ROLLBACK;
Po_OutErrCode:=SQLCODE;
Po_OutErrMsg:=SQLERRM (SQLCODE);
INSERT INTO RFD_TIMER_ERROR
(OBJECT_ID,
OBJECT_TYPE,
ERROR_CODE,
ERROR_MESSAGE,
ERROR_DATE,
START_FETCH_TIME,
END_FETCH_TIME)
VALUES
(" cs ",
"PRC",
Po_OutErrCode,
Po_OutErrMsg,
SYSDATE,
Pi_StartFetchTime,
Pi_EndFetchTime);
COMMIT;

END cs;

PROCEDURE
Take out your insert statement execution alone, more than 10 seconds succeeded, but run into the PROCEDURE is not successful, please teach a great god,

CodePudding user response:

Refer to troubleshoot https://blog.csdn.net/jycjyc/article/details/106999386

CodePudding user response:

You pull the select print it out, come out perform see slow slow

CodePudding user response:

1, type the SQL statements in the process of out look, and take out single SQL is exactly the same;
2, the performance difference is big, should still execution plan is different, can play out of SQL with execution plan and see,