CREATE OR REPLACE PROCEDURE PROC_GEN_MERGE_SCRIPT(P_TABLENAME VARCHAR2,
P_WHERE_CLAUSE VARCHAR2 DEFAULT '1=1') AS
X_CNT_CLOB NUMBER;
X_INSERT_COLUMN_LIST VARCHAR2(10000);
IS_PRIMARY_KEY_THERE PLS_INTEGER := 1; -- 1 Stand for Yes and 0 for No
/*
----------------------------------------
Create By : Ajit Kumar Dubey
Version : ß (Beta)
Creation Date : 18 Sep 2014
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@
There are two procedure in this Proc
GEN_MERGE_WITH_COB_COL
GEN_MERGE_WITHOUT_CLOB
Table Needed for this Proc is
create table SEEDED_MERGE_SCRIPT
(
TABLE_NAME VARCHAR2(30),
SCRIPT CLOB
);
@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
----------------------------------------
*/
/*
####################################################
*****************************
*************
This Procedure will be used when there is/are clob column(s) in the table;
*************
*****************************
#####################################################
*/
PROCEDURE GEN_MERGE_WITH_COB_COL(P_TABLENAME VARCHAR2,
P_WHERE_CLAUSE VARCHAR2) AS
L_TABLENAME VARCHAR2(30) := NULL;
L_WHERE_CLAUSE CLOB := P_WHERE_CLAUSE;
L_TABLENAME1 CLOB;
ERRPOS NUMBER(5);
-- Cursors defined here -----
CURSOR CUR_CONSTRAINT_COLS IS
SELECT COLUMN_NAME
FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
WHERE UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
AND UCC.TABLE_NAME = L_TABLENAME
ORDER BY POSITION;
CURSOR CUR_COLUMNS IS
SELECT UT.COLUMN_NAME,
NVL(UCCC.CONSTRAINT_TYPE, 'X') CONSTRAINT_TYPE,
SUBSTR(UT.DATA_TYPE, 1, 9) DATATYPE
FROM USER_TAB_COLUMNS UT,
(SELECT UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
WHERE UCC.TABLE_NAME = L_TABLENAME
AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P') UCCC
WHERE UT.COLUMN_NAME = UCCC.COLUMN_NAME(+)
AND UT.TABLE_NAME = L_TABLENAME
ORDER BY COLUMN_ID;
CUR_DATA_SET SYS_REFCURSOR;
CUR_DATA_SET1 SYS_REFCURSOR;
--- Cursor Definition Ends Here -----
RES_DATA_SET CLOB;
L_DATA_SET CLOB := NULL;
L_SQL_STMT CLOB := NULL;
L_SQL_STMT1 CLOB := NULL;
L_DATA_PREP_SQL_STR CLOB := NULL;
L_DATA_PREP_SQL_STR1 CLOB := NULL;
L_COL_LIST CLOB := NULL;
L_JOIN_COND CLOB := NULL;
L_UPD_STR CLOB := NULL;
L_INSRT_VALUES CLOB := NULL;
L_TABLE_EXIST_IND NUMBER(1) := 0;
L_COL_FORMAT CLOB := NULL;
NO_PRIMARY_KEY EXCEPTION;
TABLE_DOES_NOT_EXIST EXCEPTION;
----------------------------------------
X_CLOB_INDEX NUMBER := 0;
TYPE STORE_RELATION_TAB IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
X_STORE_RELATION_TAB STORE_RELATION_TAB;
X_MODEL_CLOB_OUT CLOB;
X_CLOB_COLUMN_LIST VARCHAR2(4000);
X_PK_COLUMN_STRING VARCHAR2(10000);
X_PK_COLUMN_WITH_VALS VARCHAR2(10000);
X_ASSIGN_NULL_TO_CLOB VARCHAR2(10000);
X_ROWID VARCHAR2(100);
BEGIN
L_TABLENAME := UPPER(P_TABLENAME);
BEGIN
SELECT COUNT(1)
INTO L_TABLE_EXIST_IND
FROM USER_TABLES
WHERE TABLE_NAME = L_TABLENAME;
IF L_TABLE_EXIST_IND = 0 THEN
RAISE TABLE_DOES_NOT_EXIST;
END IF;
END;
BEGIN
FOR RES_CONSTRAINT_COLS IN CUR_CONSTRAINT_COLS LOOP
L_JOIN_COND := L_JOIN_COND || 'T.' ||
RES_CONSTRAINT_COLS.COLUMN_NAME || ' = ' || 'S.' ||
RES_CONSTRAINT_COLS.COLUMN_NAME || ' AND ';
END LOOP;
L_JOIN_COND := SUBSTR(L_JOIN_COND, 1, LENGTH(L_JOIN_COND) - 5);
IF L_JOIN_COND IS NULL THEN
--RAISE NO_PRIMARY_KEY;
IS_PRIMARY_KEY_THERE := 0;
END IF;
END;
--$#$#$#-----------------------------------------------
FOR RES_COLUMNS IN CUR_COLUMNS LOOP
IF RES_COLUMNS.DATATYPE = 'TIMESTAMP' THEN
L_COL_FORMAT := '''to_timestamp(''' || '||' || '''''''''' || '||' ||
'to_char(' || RES_COLUMNS.COLUMN_NAME ||
',''dd-mm-yyyy hh24:mi:ss.ff'')' || '||' ||
'''''''''' || '||' || ''',''' || '||' ||
'''''''dd-mm-yyyy hh24:mi:ss.ff''' || ''')''';
ELSIF RES_COLUMNS.DATATYPE = 'DATE' THEN
L_COL_FORMAT := '''to_date(''' || '||' || '''''''''' || '||' ||
'to_char(' || RES_COLUMNS.COLUMN_NAME ||
',''dd-mm-yyyy'')' || '||' || '''''''''' || '||' ||
''',''' || '||' || '''''''dd-mm-yyyy''' || ''')''';
ELSIF RES_COLUMNS.DATATYPE = 'CLOB' THEN
X_CLOB_INDEX := X_CLOB_INDEX + 1;
X_STORE_RELATION_TAB(X_CLOB_INDEX) := RES_COLUMNS.COLUMN_NAME;
L_COL_FORMAT := q'~ q'#X_CLOB_VAR(~' || X_CLOB_INDEX || ')#'''; /*'*/
X_CLOB_COLUMN_LIST := CASE WHEN X_CLOB_COLUMN_LIST IS NULL THEN RES_COLUMNS.COLUMN_NAME ELSE X_CLOB_COLUMN_LIST || ',' || RES_COLUMNS.COLUMN_NAME END;
ELSE
L_COL_FORMAT := '''''''''' || '||' || 'Replace(' ||
RES_COLUMNS.COLUMN_NAME || ',''''''''' ||
',''''''''''''' || ')' || '||' || '''''''''';
END IF;
L_DATA_PREP_SQL_STR1 := L_DATA_PREP_SQL_STR1 || L_COL_FORMAT || '||' ||
''' ' || RES_COLUMNS.COLUMN_NAME || '''' || '||' ||
''',''' || '||';
L_COL_LIST := L_COL_LIST || RES_COLUMNS.COLUMN_NAME || ',';
IF RES_COLUMNS.CONSTRAINT_TYPE <> 'P' THEN
L_UPD_STR := L_UPD_STR || 'T.' || RES_COLUMNS.COLUMN_NAME || ' = ' || 'S.' ||
RES_COLUMNS.COLUMN_NAME || ',' || CHR(10) || ' ';
END IF;
---PK Columns match and populate_its value
IF INSTR(L_JOIN_COND, '.' || RES_COLUMNS.COLUMN_NAME) > 0 THEN
X_PK_COLUMN_STRING := CASE WHEN X_PK_COLUMN_STRING IS NULL THEN '''' || RES_COLUMNS.COLUMN_NAME || '''' || '||''=''||' || L_COL_FORMAT ELSE X_PK_COLUMN_STRING || '||'' AND ''||' || '''' || RES_COLUMNS.COLUMN_NAME || '''' || '||''=''||' || L_COL_FORMAT END;
END IF;
L_INSRT_VALUES := L_INSRT_VALUES || 'S.' ||
RES_COLUMNS.COLUMN_NAME || ',';
X_INSERT_COLUMN_LIST := CASE WHEN X_INSERT_COLUMN_LIST IS NULL THEN RES_COLUMNS.COLUMN_NAME ELSE X_INSERT_COLUMN_LIST || ',' || RES_COLUMNS.COLUMN_NAME END;
END LOOP;
--$#$#$#-----------------------------------------------
ERRPOS := 1;
SELECT REGEXP_REPLACE(L_UPD_STR, CHR(10), '', LENGTH(L_UPD_STR) - 7)
INTO L_UPD_STR
FROM DUAL;
L_DATA_PREP_SQL_STR1 := SUBSTR(L_DATA_PREP_SQL_STR1,
1,
LENGTH(L_DATA_PREP_SQL_STR1) - 7);
L_COL_LIST := SUBSTR(L_COL_LIST, 1, LENGTH(L_COL_LIST) - 1);
L_UPD_STR := SUBSTR(L_UPD_STR, 1, LENGTH(L_UPD_STR) - 6);
L_INSRT_VALUES := SUBSTR(L_INSRT_VALUES,
1,
LENGTH(L_INSRT_VALUES) - 1);
--DBMS_OUTPUT.PUT_LINE(L_DATA_PREP_SQL_STR1);
L_TABLENAME1 := L_TABLENAME;
IF IS_PRIMARY_KEY_THERE = 0 THEN
X_PK_COLUMN_STRING := '''1=1''';
END IF;
L_DATA_PREP_SQL_STR := 'SELECT ' || L_DATA_PREP_SQL_STR1 || ' ,' ||
X_PK_COLUMN_STRING || ', ROWID' || ' FROM ' ||
L_TABLENAME1 || ' where ' || L_WHERE_CLAUSE;
--INSERT INTO DROP_MSG_LOG VALUES (L_DATA_PREP_SQL_STR, SYSDATE);
--COMMIT;
ERRPOS := 2;
--~$~#~$~#---------------------------------------
OPEN CUR_DATA_SET FOR L_DATA_PREP_SQL_STR;
LOOP
FETCH CUR_DATA_SET
INTO RES_DATA_SET, X_PK_COLUMN_WITH_VALS, X_ROWID;
EXIT WHEN CUR_DATA_SET%NOTFOUND;
X_MODEL_CLOB_OUT := NULL;
--$$##--------------------------------------------
DECLARE
X_UNION_STMT VARCHAR2(20000);
X_CLB_VALUE CLOB;
X_INDEX NUMBER;
BEGIN
X_ASSIGN_NULL_TO_CLOB := NULL;
FOR I_INDX IN 1 .. X_STORE_RELATION_TAB.COUNT LOOP
-- X_UNION_STMT := CASE WHEN X_UNION_STMT IS NULL THEN 'SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ' COL1,' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ' || X_PK_COLUMN_WITH_VALS ELSE X_UNION_STMT || 'UNION ALL SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ',' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ' || X_PK_COLUMN_WITH_VALS END;
X_UNION_STMT := CASE WHEN X_UNION_STMT IS NULL THEN 'SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ' COL1,' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ROWID =''' || X_ROWID || '''' ELSE X_UNION_STMT || 'UNION ALL SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ',' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ROWID =''' || X_ROWID || '''' END;
X_ASSIGN_NULL_TO_CLOB := CASE WHEN X_ASSIGN_NULL_TO_CLOB IS NULL THEN 'X_CLOB_VAR(' || I_INDX || ') := NULL; ' ELSE X_ASSIGN_NULL_TO_CLOB || CHR(10) || 'X_CLOB_VAR(' || I_INDX || ') := NULL; ' END;
END LOOP;
X_UNION_STMT := 'Select * from (' || X_UNION_STMT ||
') order by RNM ASC';
OPEN CUR_DATA_SET1 FOR X_UNION_STMT;
LOOP
FETCH CUR_DATA_SET1
INTO X_CLB_VALUE, X_INDEX;
EXIT WHEN CUR_DATA_SET1%NOTFOUND;
--##------------------------------------
DECLARE
X_STMT CLOB;
X_MODEL_QUERY CLOB;
X_LENGTH NUMBER := 0;
X_START_POSITION NUMBER := 1;
X_END_POSITION NUMBER;
X_EXIT_LOOP NUMBER := 0;
BEGIN
X_MODEL_QUERY := X_CLB_VALUE;
X_LENGTH := DBMS_LOB.GETLENGTH(X_MODEL_QUERY);
WHILE X_START_POSITION < X_LENGTH LOOP
X_END_POSITION := CASE WHEN X_START_POSITION + 1600 > X_LENGTH THEN X_LENGTH ELSE X_START_POSITION + 1600 END;
-- DBMS_OUTPUT.PUT_LINE('End Postion :' || X_END_POSITION);
X_STMT := X_STMT || CHR(10) || 'X_CLOB_VAR(' ||
X_INDEX || ') := X_CLOB_VAR(' || X_INDEX ||
')|| q''^' ||
DBMS_LOB.SUBSTR(X_MODEL_QUERY,
1600,
X_START_POSITION) ||
'^'';';
X_START_POSITION := X_END_POSITION;
--DBMS_OUTPUT.PUT_LINE('Start Position :' || X_START_POSITION);
X_EXIT_LOOP := X_EXIT_LOOP + 1;
--DBMS_OUTPUT.PUT_LINE('Loop :' || X_EXIT_LOOP);
EXIT WHEN X_EXIT_LOOP > 5;
END LOOP;
--X_MODEL_CLOB_OUT := NULL;
X_MODEL_CLOB_OUT := X_MODEL_CLOB_OUT || CHR(10) || X_STMT;
--EXIT;
END;
--##------------------------------------
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
NULL;
END;
--$$##--------------------------------------------
---End fetch clob
L_DATA_SET := ' SELECT ' || RES_DATA_SET || ' FROM DUAL' || CHR(10);
SELECT REGEXP_REPLACE(L_DATA_SET, CHR(10), '', LENGTH(L_DATA_SET) - 1)
INTO L_DATA_SET
FROM DUAL;
--L_DATA_SET := SUBSTR(L_DATA_SET, 1, LENGTH(L_DATA_SET) - 8);
ERRPOS := 3;
IF IS_PRIMARY_KEY_THERE = 1 THEN
X_MODEL_CLOB_OUT := CHR(10) || 'DECLARE' || CHR(10) ||
'TYPE CLOB_TAB is TABLE of CLOB INDEX by PLS_INTEGER;' ||
CHR(10) || 'X_CLOB_VAR CLOB_TAB;' || CHR(10) ||
' BEGIN ' || CHR(10) || X_ASSIGN_NULL_TO_CLOB ||
CHR(10) || X_MODEL_CLOB_OUT;
L_SQL_STMT := X_MODEL_CLOB_OUT || CHR(10) || 'MERGE INTO ' ||
L_TABLENAME1 || ' T ' || CHR(10) || 'USING ( ' ||
CHR(10) || L_DATA_SET || CHR(10) || ' ) S' ||
CHR(10) || 'ON (' || L_JOIN_COND || ')' ||
CHR(10) || 'WHEN MATCHED THEN ' || CHR(10) ||
' UPDATE ' || CHR(10) || ' SET ' || L_UPD_STR ||
CHR(10) || 'WHEN NOT MATCHED THEN ' || CHR(10) ||
' INSERT ' || CHR(10) || ' (' || L_COL_LIST || ')' ||
CHR(10) || ' VALUES' || CHR(10) || ' (' ||
L_INSRT_VALUES || ' );' || CHR(10) || 'END;' ||
CHR(10) || '/';
L_SQL_STMT1 := L_SQL_STMT1 || L_SQL_STMT;
ELSE
X_MODEL_CLOB_OUT := CHR(10) || 'DECLARE' || CHR(10) ||
'TYPE CLOB_TAB is TABLE of CLOB INDEX by PLS_INTEGER;' ||
CHR(10) || 'X_CLOB_VAR CLOB_TAB;' || CHR(10) ||
' BEGIN ' || CHR(10) || X_ASSIGN_NULL_TO_CLOB ||
CHR(10) || X_MODEL_CLOB_OUT;
L_SQL_STMT := X_MODEL_CLOB_OUT || CHR(10) || CHR(10) ||
'INSERT INTO ' || L_TABLENAME1 || ' (' ||
X_INSERT_COLUMN_LIST || ') ' || CHR(10) ||
L_DATA_SET || ' ;' || CHR(10) ||
'EXCEPTION WHEN OTHERS THEN NULL;' || CHR(10) ||
'END;' || CHR(10) || '/';
L_SQL_STMT1 := L_SQL_STMT1 || L_SQL_STMT;
END IF;
END LOOP;
L_SQL_STMT1 := L_SQL_STMT1 || CHR(10) || 'COMMIT' || CHR(10) || '/';
--~$~#~$~#---------------------------------------
--DBMS_OUTPUT.put_line(l_sql_stmt);
DELETE FROM SEEDED_MERGE_SCRIPT WHERE TABLE_NAME = L_TABLENAME;
INSERT INTO SEEDED_MERGE_SCRIPT VALUES (L_TABLENAME, L_SQL_STMT1);
ERRPOS := 4;
EXCEPTION
WHEN NO_PRIMARY_KEY THEN
RAISE_APPLICATION_ERROR(-20001,
'No Primary Key found for the Table' ||
L_TABLENAME);
WHEN TABLE_DOES_NOT_EXIST THEN
RAISE_APPLICATION_ERROR(-20002,
'The table does not exist' || L_TABLENAME);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Reached :' || ERRPOS || ' --- ' || SQLERRM ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
/*RAISE_APPLICATION_ERROR(-20003,
'Some Error - ' || L_TABLENAME || ' - ' ||
ERRPOS);*/
END GEN_MERGE_WITH_COB_COL;
/*
##########################################
*****************************
*************
This Procedure will be used when there is no clob column in the table;
*************
*****************************
#########################################
*/
PROCEDURE GEN_MERGE_WITHOUT_CLOB_COL(P_TABLENAME VARCHAR2,
P_WHERE_CLAUSE VARCHAR2) AS
L_TABLENAME VARCHAR2(30) := NULL;
L_WHERE_CLAUSE CLOB := P_WHERE_CLAUSE;
L_TABLENAME1 CLOB;
ERRPOS NUMBER(5);
-- Cursors defined here -----
CURSOR CUR_CONSTRAINT_COLS IS
SELECT COLUMN_NAME
FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
WHERE UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
AND UCC.TABLE_NAME = L_TABLENAME
ORDER BY POSITION;
CURSOR CUR_COLUMNS IS
SELECT UT.COLUMN_NAME,
NVL(UCCC.CONSTRAINT_TYPE, 'X') CONSTRAINT_TYPE,
SUBSTR(UT.DATA_TYPE, 1, 9) DATATYPE
FROM USER_TAB_COLUMNS UT,
(SELECT UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
WHERE UCC.TABLE_NAME = L_TABLENAME
AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P') UCCC
WHERE UT.COLUMN_NAME = UCCC.COLUMN_NAME(+)
AND UT.TABLE_NAME = L_TABLENAME
ORDER BY COLUMN_ID;
CUR_DATA_SET SYS_REFCURSOR;
--- Cursor Definition Ends Here -----
RES_DATA_SET CLOB;
L_DATA_SET CLOB := NULL;
L_SQL_STMT CLOB := NULL;
L_DATA_PREP_SQL_STR CLOB := NULL;
L_DATA_PREP_SQL_STR1 CLOB := NULL;
L_COL_LIST CLOB := NULL;
L_JOIN_COND CLOB := NULL;
L_UPD_STR CLOB := NULL;
L_INSRT_VALUES CLOB := NULL;
L_TABLE_EXIST_IND NUMBER(1) := 0;
L_COL_FORMAT CLOB := NULL;
NO_PRIMARY_KEY EXCEPTION;
TABLE_DOES_NOT_EXIST EXCEPTION;
BEGIN
L_TABLENAME := UPPER(P_TABLENAME);
BEGIN
SELECT COUNT(1)
INTO L_TABLE_EXIST_IND
FROM USER_TABLES
WHERE TABLE_NAME = L_TABLENAME;
IF L_TABLE_EXIST_IND = 0 THEN
RAISE TABLE_DOES_NOT_EXIST;
END IF;
END;
BEGIN
FOR RES_CONSTRAINT_COLS IN CUR_CONSTRAINT_COLS LOOP
L_JOIN_COND := L_JOIN_COND || 'T.' ||
RES_CONSTRAINT_COLS.COLUMN_NAME || ' = ' || 'S.' ||
RES_CONSTRAINT_COLS.COLUMN_NAME || ' AND ';
END LOOP;
L_JOIN_COND := SUBSTR(L_JOIN_COND, 1, LENGTH(L_JOIN_COND) - 5);
IF L_JOIN_COND IS NULL THEN
--RAISE NO_PRIMARY_KEY;
IS_PRIMARY_KEY_THERE := 0;
END IF;
END;
FOR RES_COLUMNS IN CUR_COLUMNS LOOP
IF RES_COLUMNS.DATATYPE = 'TIMESTAMP' THEN
L_COL_FORMAT := '''to_timestamp(''' || '||' || '''''''''' || '||' ||
'to_char(' || RES_COLUMNS.COLUMN_NAME ||
',''dd-mm-yyyy hh24:mi:ss.ff'')' || '||' ||
'''''''''' || '||' || ''',''' || '||' ||
'''''''dd-mm-yyyy hh24:mi:ss.ff''' || ''')''';
ELSIF RES_COLUMNS.DATATYPE = 'DATE' THEN
L_COL_FORMAT := '''to_date(''' || '||' || '''''''''' || '||' ||
'to_char(' || RES_COLUMNS.COLUMN_NAME ||
',''dd-mm-yyyy'')' || '||' || '''''''''' || '||' ||
''',''' || '||' || '''''''dd-mm-yyyy''' || ''')''';
ELSE
L_COL_FORMAT := '''''''''' || '||' || 'Replace(' ||
RES_COLUMNS.COLUMN_NAME || ',''''''''' ||
',''''''''''''' || ')' || '||' || '''''''''';
END IF;
L_DATA_PREP_SQL_STR1 := L_DATA_PREP_SQL_STR1 || L_COL_FORMAT || '||' ||
''' ' || RES_COLUMNS.COLUMN_NAME || '''' || '||' ||
''',''' || '||';
L_COL_LIST := L_COL_LIST || RES_COLUMNS.COLUMN_NAME || ',';
IF RES_COLUMNS.CONSTRAINT_TYPE <> 'P' THEN
L_UPD_STR := L_UPD_STR || 'T.' || RES_COLUMNS.COLUMN_NAME || ' = ' || 'S.' ||
RES_COLUMNS.COLUMN_NAME || ',' || CHR(10) || ' ';
END IF;
L_INSRT_VALUES := L_INSRT_VALUES || 'S.' ||
RES_COLUMNS.COLUMN_NAME || ',';
X_INSERT_COLUMN_LIST := CASE WHEN X_INSERT_COLUMN_LIST IS NULL THEN RES_COLUMNS.COLUMN_NAME ELSE X_INSERT_COLUMN_LIST || ',' || RES_COLUMNS.COLUMN_NAME END;
END LOOP;
ERRPOS := 1;
SELECT REGEXP_REPLACE(L_UPD_STR, CHR(10), '', LENGTH(L_UPD_STR) - 7)
INTO L_UPD_STR
FROM DUAL;
L_DATA_PREP_SQL_STR1 := SUBSTR(L_DATA_PREP_SQL_STR1,
1,
LENGTH(L_DATA_PREP_SQL_STR1) - 7);
L_COL_LIST := SUBSTR(L_COL_LIST, 1, LENGTH(L_COL_LIST) - 1);
L_UPD_STR := SUBSTR(L_UPD_STR, 1, LENGTH(L_UPD_STR) - 6);
L_INSRT_VALUES := SUBSTR(L_INSRT_VALUES,
1,
LENGTH(L_INSRT_VALUES) - 1);
DBMS_OUTPUT.PUT_LINE(L_DATA_PREP_SQL_STR1);
L_TABLENAME1 := L_TABLENAME;
L_DATA_PREP_SQL_STR := 'SELECT ' || L_DATA_PREP_SQL_STR1 || ' FROM ' ||
L_TABLENAME1 || ' where ' || L_WHERE_CLAUSE;
ERRPOS := 2;
OPEN CUR_DATA_SET FOR L_DATA_PREP_SQL_STR;
LOOP
FETCH CUR_DATA_SET
INTO RES_DATA_SET;
EXIT WHEN CUR_DATA_SET%NOTFOUND;
L_DATA_SET := ' SELECT ' || RES_DATA_SET || ' FROM DUAL' || CHR(10);
SELECT REGEXP_REPLACE(L_DATA_SET, CHR(10), '', LENGTH(L_DATA_SET) - 1)
INTO L_DATA_SET
FROM DUAL;
--L_DATA_SET := SUBSTR(L_DATA_SET, 1, LENGTH(L_DATA_SET) - 8);
ERRPOS := 3;
IF IS_PRIMARY_KEY_THERE = 1 THEN
L_SQL_STMT := L_SQL_STMT || CHR(10) || 'MERGE INTO ' ||
L_TABLENAME1 || ' T ' || CHR(10) || 'USING ( ' ||
CHR(10) || L_DATA_SET || CHR(10) || ' ) S' || CHR(10) ||
'ON (' || L_JOIN_COND || ')' || CHR(10) ||
'WHEN MATCHED THEN ' || CHR(10) || ' UPDATE ' ||
CHR(10) || ' SET ' || L_UPD_STR || CHR(10) ||
'WHEN NOT MATCHED THEN ' || CHR(10) || ' INSERT ' ||
CHR(10) || ' (' || L_COL_LIST || ')' || CHR(10) ||
' VALUES' || CHR(10) || ' (' || L_INSRT_VALUES || ' )' ||
CHR(10) || '/';
ELSE
L_SQL_STMT := L_SQL_STMT || CHR(10) || 'BEGIN ' || CHR(10) ||
'INSERT INTO ' || L_TABLENAME1 || ' (' ||
X_INSERT_COLUMN_LIST || ') ' || CHR(10) || L_DATA_SET || ' ;' ||
CHR(10) || 'EXCEPTION WHEN OTHERS THEN NULL;' ||
CHR(10) || 'END;' || CHR(10) || '/';
END IF;
END LOOP;
L_SQL_STMT := L_SQL_STMT || CHR(10) || 'COMMIT' || CHR(10) || '/';
--DBMS_OUTPUT.put_line(l_sql_stmt);
DELETE FROM SEEDED_MERGE_SCRIPT WHERE TABLE_NAME = L_TABLENAME;
INSERT INTO SEEDED_MERGE_SCRIPT VALUES (L_TABLENAME, L_SQL_STMT);
ERRPOS := 4;
EXCEPTION
WHEN NO_PRIMARY_KEY THEN
RAISE_APPLICATION_ERROR(-20001,
'No Primary Key found for the Table' ||
L_TABLENAME);
WHEN TABLE_DOES_NOT_EXIST THEN
RAISE_APPLICATION_ERROR(-20002,
'The table does not exist' || L_TABLENAME);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20003,
'Some Error - ' || L_TABLENAME || ' - ' ||
ERRPOS);
END GEN_MERGE_WITHOUT_CLOB_COL;
/*
##########################################
*****************************
*************
The below block is the wrapper of the procedures.
This block will decide which Procedure to call.
*************
*****************************
############################################
*/
BEGIN
EXECUTE IMMEDIATE 'Select Count(1) from USER_TAB_COLUMNS where Table_name = TRIM(Upper(''' ||
P_TABLENAME || ''')) and DATA_TYPE=''CLOB'''
INTO X_CNT_CLOB;
IF X_CNT_CLOB > 0 THEN
GEN_MERGE_WITH_COB_COL(P_TABLENAME, P_WHERE_CLAUSE);
ELSE
GEN_MERGE_WITHOUT_CLOB_COL(P_TABLENAME, P_WHERE_CLAUSE);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || '--' ||
SQLERRM);
END PROC_GEN_MERGE_SCRIPT;
add to del.icio.us
saved by
0 users
