In this procedure i'm inserting and updating by using dynamic sql.Now i want to return two more OUT parameters the parameter should be return how many rows inserted and how many updated by UPDATE stmtas well as a INSERT.I'm not able to get it can you help on this?
CREATE OR REPLACE PROCEDURE Sp_Array_Test( PV_TGT_NAME IN VARCHAR2,
PV_SRC_NAME IN VARCHAR2,
PV_PK_COLS IN VARCHAR2,
PN_ERR_CD OUT NUMBER,
PN_ERR_MSG OUT VARCHAR2)
AS
TYPE ARR_TAB IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
--UTL_FP UTL_FILE.FILE_TYPE;
LV_AN_
BLOCK VARCHAR2(32767);
LN_CUR BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
LN_DESC DBMS_SQL.DESC_TAB;
LN_COL_CNT PLS_INTEGER := 0;
LV_SEL_UPD_STMT VARCHAR2(4000);
LV_SEL_INS_STMT VARCHAR2(4000);
ARR_INDX NUMBER := 1;
LV_DATA_TYPE VARCHAR2(8);
LN_FIND_FLAG NUMBER := 0;
LN_TAB ARR_TAB;
LV_COLS_ARR ARR_TAB;
LV_ERR_MSG VARCHAR2(500);
--PROCEDURE FILE_WRITE ( FH_IN IN UTL_FILE.FILE_TYPE,
-- STRING_IN IN VARCHAR2 ) IS
--BEGIN
-- UTL_FILE.PUT_LINE(FH_IN,STRING_IN);
-- LV_AN_BLOCK := LV_AN_BLOCK||STRING_IN;
--EXCEPTION
-- WHEN OTHERS THEN
-- RAISE;
--END FILE_WRITE;
BEGIN
-- UTL_FP := UTL_FILE.FOPEN('TEST_DIR', 'TEST.sql', 'W');
LV_SEL_UPD_STMT := 'SELECT A.'||REPLACE(PV_PK_COLS,',','||A.')||' PK_COLS , A.* , B.ROWID FROM '||PV_SRC_NAME||' A, '||PV_TGT_NAME||' B WHERE ';
LV_SEL_INS_STMT := 'SELECT A.* FROM '||PV_SRC_NAME||' A WHERE NOT EXISTS (SELECT ''1'' FROM '||PV_TGT_NAME||' B WHERE ';
LN_TAB(ARR_INDX) := 'DECLARE ';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := 'CURSOR CUR_VIEW_UPD IS '||LV_SEL_UPD_STMT ;
ARR_INDX := ARR_INDX + 1;
SELECT SUBSTR(COLS,DECODE(RN,1,1,INSTR(COLS,',',1,RN-1)+1),DECODE(RN,1,INSTR(COLS,',',1,RN)-1,INSTR(COLS,',',1,RN)-INSTR(COLS,',',1,RN-1)-1))
BULK COLLECT INTO LV_COLS_ARR
FROM ( SELECT RN, PV_PK_COLS||',' COLS
FROM (SELECT ROWNUM RN
FROM ALL_OBJECTS
WHERE ROWNUM <= LENGTH(PV_PK_COLS)- LENGTH(REPLACE(PV_PK_COLS,','))+1)) ;
FOR K IN 1 .. LV_COLS_ARR.COUNT LOOP
LV_SEL_UPD_STMT := LV_SEL_UPD_STMT||' A.'||LV_COLS_ARR(K)||' = ';
LN_TAB(ARR_INDX) := ' A.'||LV_COLS_ARR(K)||' = ';
LV_SEL_UPD_STMT := LV_SEL_UPD_STMT||' B.'||LV_COLS_ARR(K) ||CASE WHEN K = LV_COLS_ARR.COUNT THEN NULL ELSE ' AND ' END;
LN_TAB(ARR_INDX) := LN_TAB(ARR_INDX)||' B.'||LV_COLS_ARR(K) ||CASE WHEN K = LV_COLS_ARR.COUNT THEN ' ;' ELSE ' AND ' END;
ARR_INDX := ARR_INDX + 1;
END LOOP;
LN_TAB(ARR_INDX) := 'CURSOR CUR_VIEW_INS IS '||LV_SEL_INS_STMT ;
ARR_INDX := ARR_INDX + 1;
FOR K IN 1 .. LV_COLS_ARR.COUNT LOOP
LV_SEL_INS_STMT := LV_SEL_INS_STMT||' A.'||LV_COLS_ARR(K)||' = ';
LN_TAB(ARR_INDX) := ' A.'||LV_COLS_ARR(K)||' = ';
LV_SEL_INS_STMT := LV_SEL_INS_STMT||' B.'||LV_COLS_ARR(K) ||CASE WHEN K = LV_COLS_ARR.COUNT THEN NULL ELSE ' AND ' END;
LN_TAB(ARR_INDX) := LN_TAB(ARR_INDX)||' B.'||LV_COLS_ARR(K) ||CASE WHEN K = LV_COLS_ARR.COUNT THEN ' );' ELSE ' AND ' END;
ARR_INDX := ARR_INDX + 1;
END LOOP;
LV_ERR_MSG := 'WHILE PARSING SELECT STATEMENT -- '||LV_SEL_UPD_STMT;
DBMS_SQL.PARSE(LN_CUR, LV_SEL_UPD_STMT, DBMS_SQL.NATIVE);
LV_ERR_MSG := 'WHILE DESCRIBING SELECT STATEMENT -- '||LV_SEL_UPD_STMT;
DBMS_SQL.DESCRIBE_COLUMNS(LN_CUR, LN_COL_CNT, LN_DESC);
FOR i IN LN_DESC.FIRST .. LN_DESC.LAST LOOP
IF LN_DESC(i).col_type = 2 THEN
LV_DATA_TYPE := 'NUMBER';
ELSIF LN_DESC(i).col_type = 12 THEN
LV_DATA_TYPE := 'DATE';
ELSE
LV_DATA_TYPE := 'VARCHAR2';
END IF;
LN_TAB(ARR_INDX) := ' T_'||LN_DESC(i).col_name||' DBMS_SQL.'||LV_DATA_TYPE||'_TABLE;';
ARR_INDX := ARR_INDX + 1;
END LOOP;
LN_TAB(ARR_INDX) := 'BEGIN ';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_DATE_FORMAT = ''''DD-MON-YYYY HH24:MI:SS'''''';';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' OPEN CUR_VIEW_UPD;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' LOOP';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' FETCH CUR_VIEW_UPD BULK COLLECT INTO T_'||LN_DESC(LN_DESC.FIRST).col_name||',';
ARR_INDX := ARR_INDX + 1;
FOR i IN LN_DESC.FIRST + 1 .. LN_DESC.LAST - 1 LOOP
LN_TAB(ARR_INDX) := ' T_'||LN_DESC(i).col_name||',';
ARR_INDX := ARR_INDX + 1;
END LOOP;
LN_TAB(ARR_INDX) := ' T_'||LN_DESC(LN_DESC.LAST).col_name||' LIMIT 500 ;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' FORALL I IN 1 .. '||'T_'||LN_DESC(LN_DESC.LAST).col_name||'.COUNT ';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' UPDATE '||PV_TGT_NAME||' SET ';
ARR_INDX := ARR_INDX + 1;
LN_FIND_FLAG := 0;
FOR I IN LN_DESC.FIRST + 1 .. LN_DESC.LAST-1 LOOP
FOR K IN 1 .. LV_COLS_ARR.COUNT LOOP
LN_FIND_FLAG := 0;
IF LN_DESC(I).COL_NAME = LV_COLS_ARR(K) THEN
LN_FIND_FLAG := 1;
EXIT;
END IF;
END LOOP;
IF LN_FIND_FLAG = 0 THEN
LN_TAB(ARR_INDX) := ' '||LN_DESC(i).col_name||' = '||'T_'||LN_DESC(i).col_name||'(I)'||CASE WHEN I = LN_DESC.LAST-1 THEN ' WHERE ' ELSE ',' END;
ARR_INDX := ARR_INDX + 1;
END IF ;
END LOOP;
LN_TAB(ARR_INDX) := ' ROWID = '||'T_'||LN_DESC(LN_DESC.LAST).col_name||'(I) ;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' COMMIT;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' EXIT WHEN CUR_VIEW_UPD%NOTFOUND;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' END LOOP;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' CLOSE CUR_VIEW_UPD;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' COMMIT;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' OPEN CUR_VIEW_INS;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' LOOP';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' FETCH CUR_VIEW_INS BULK COLLECT INTO T_'||LN_DESC(LN_DESC.FIRST+1).col_name||',';
ARR_INDX := ARR_INDX + 1;
FOR i IN LN_DESC.FIRST + 2 .. LN_DESC.LAST - 2 LOOP
LN_TAB(ARR_INDX) := ' T_'||LN_DESC(i).col_name||',';
ARR_INDX := ARR_INDX + 1;
END LOOP;
LN_TAB(ARR_INDX) := ' T_'||LN_DESC(LN_DESC.LAST-1).col_name||' LIMIT 500 ;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' FORALL J IN 1 .. '||'T_'||LN_DESC(LN_DESC.FIRST + 1).col_name||'.COUNT ';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' INSERT INTO '||PV_TGT_NAME||' (';
ARR_INDX := ARR_INDX + 1;
FOR i IN LN_DESC.FIRST + 1 .. LN_DESC.LAST - 1 LOOP
LN_TAB(ARR_INDX) := ' '||LN_DESC(i).col_name||CASE WHEN I = LN_DESC.LAST - 1 THEN ' )' ELSE ',' END ;
ARR_INDX := ARR_INDX + 1;
END LOOP;
FOR i IN LN_DESC.FIRST + 1 .. LN_DESC.LAST - 1 LOOP
LN_TAB(ARR_INDX) := CASE WHEN I = LN_DESC.FIRST + 1 THEN 'VALUES (' ELSE NULL END ||' T_'||LN_DESC(i).col_name||'(J)'||CASE WHEN I = LN_DESC.LAST - 1 THEN ' ) ;' ELSE ',' END ;
ARR_INDX := ARR_INDX + 1;
END LOOP;
LN_TAB(ARR_INDX) := ' COMMIT;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' EXIT WHEN CUR_VIEW_INS%NOTFOUND;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' END LOOP;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' CLOSE CUR_VIEW_INS;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := ' COMMIT;';
ARR_INDX := ARR_INDX + 1;
LN_TAB(ARR_INDX) := 'END ;';
ARR_INDX := ARR_INDX + 1;
FOR J IN 1 .. LN_TAB.COUNT LOOP
-- DBMS_OUTPUT.PUT_LINE( LN_TAB(J));
-- FILE_WRITE(UTL_FP,LN_TAB(J));
LV_AN_BLOCK := LV_AN_BLOCK||LN_TAB(J);
END LOOP;
-- UTL_FILE.FCLOSE(UTL_FP);
EXECUTE IMMEDIATE LV_AN_BLOCK;
PN_ERR_CD := 0;
PN_ERR_MSG := 'Successful Completion';
EXCEPTION
WHEN OTHERS THEN
PN_ERR_CD := SQLCODE;
PN_ERR_MSG := LV_ERR_MSG||' -- '||SQLERRM ;
END;
/