[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(
p_source_id IN NUMBER
,p_trans_type_id IN NUMBER
,p_program_name IN VARCHAR2
,p_version_name IN VARCHAR2
,p_real_time_sql IN VARCHAR2
,p_batch_total_sql IN VARCHAR2
,p_batch_incr_sql IN VARCHAR2
,p_batch_dea_sql IN VARCHAR2
,p_incr_reassign_sql IN VARCHAR2
,p_use_total_for_dea_flag IN VARCHAR2
,p_enabled_flag IN VARCHAR2
,retcode OUT NOCOPY VARCHAR2
,errbuf OUT NOCOPY VARCHAR2) IS
l_enabled_flag VARCHAR2(1);
l_real_time_insert VARCHAR2(32767);
l_real_time_select VARCHAR2(32000);
l_real_time_insert_clob CLOB;
SELECT column_name
FROM user_tab_columns
WHERE table_name = p_table_name
ORDER BY column_id;
SELECT REAL_TIME_SQL
FROM JTY_TRANS_USG_PGM_SQL
WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
FOR UPDATE OF REAL_TIME_SQL NOWAIT;
CURSOR CUR_REAL_TIME_INSERT(cl_trans_usg_pgm_sql_id IN NUMBER) IS
SELECT REAL_TIME_INSERT
FROM JTY_TRANS_USG_PGM_SQL
WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
FOR UPDATE OF REAL_TIME_INSERT NOWAIT;
SELECT BATCH_TOTAL_SQL
FROM JTY_TRANS_USG_PGM_SQL
WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
FOR UPDATE OF BATCH_TOTAL_SQL NOWAIT;
SELECT BATCH_INCR_SQL
FROM JTY_TRANS_USG_PGM_SQL
WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
FOR UPDATE OF BATCH_INCR_SQL NOWAIT;
SELECT BATCH_DEA_SQL
FROM JTY_TRANS_USG_PGM_SQL
WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
FOR UPDATE OF BATCH_DEA_SQL NOWAIT;
SELECT INCR_REASSIGN_SQL
FROM JTY_TRANS_USG_PGM_SQL
WHERE TRANS_USG_PGM_SQL_ID = cl_trans_usg_pgm_sql_id
FOR UPDATE OF INCR_REASSIGN_SQL NOWAIT;
SELECT
param_passing_mechanism
,real_time_enable_flag
,batch_enable_flag
,real_time_trans_table_name
,batch_trans_table_name
,batch_nm_trans_table_name
,batch_dea_trans_table_name
INTO
l_param_passing_mechanism
,l_real_time_enable_flag
,l_batch_enable_flag
,l_real_time_trans_table_name
,l_batch_trans_table_name
,l_batch_nm_trans_table_name
,l_batch_dea_trans_table_name
FROM jty_trans_usg_pgm_details
WHERE source_id = p_source_id
AND trans_type_id = p_trans_type_id
AND program_name = p_program_name;
UPDATE jty_trans_usg_pgm_sql
SET enabled_flag = 'N'
WHERE SOURCE_ID = p_source_id
AND TRANS_TYPE_ID = p_trans_type_id
AND PROGRAM_NAME = p_program_name;
/* If present, delete the old entries from jty_trnas_usg_pgm_sql */
BEGIN
DELETE FROM JTY_TRANS_USG_PGM_SQL
WHERE SOURCE_ID = p_source_id
AND TRANS_TYPE_ID = p_trans_type_id
AND PROGRAM_NAME = p_program_name
AND VERSION_NAME = p_version_name;
SELECT JTY_TRANS_USG_PGM_SQL_S.nextval
INTO l_trans_usg_pgm_sql_id
FROM DUAL;
/* Insert a record with all the SQLs as NULL */
INSERT INTO JTY_TRANS_USG_PGM_SQL (
TRANS_USG_PGM_SQL_ID
,SOURCE_ID
,TRANS_TYPE_ID
,PROGRAM_NAME
,VERSION_NAME
,USE_TOTAL_FOR_DEA_FLAG
,ENABLED_FLAG
,REAL_TIME_SQL
,REAL_TIME_INSERT
,BATCH_TOTAL_SQL
,BATCH_INCR_SQL
,BATCH_DEA_SQL
,INCR_REASSIGN_SQL
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
VALUES(
l_trans_usg_pgm_sql_id
,p_source_id
,p_trans_type_id
,p_program_name
,p_version_name
,l_use_total_for_dea_flag
,l_enabled_flag
,EMPTY_CLOB()
,EMPTY_CLOB()
,EMPTY_CLOB()
,EMPTY_CLOB()
,EMPTY_CLOB()
,EMPTY_CLOB()
,l_user_id
,l_sysdate
,l_user_id
,l_sysdate
,l_login_id
,1);
l_real_time_select := p_real_time_sql;
l_real_time_select := replace(l_real_time_select, 'l_txn_date', 'sysdate');
l_real_time_select := replace(l_real_time_select, 'l_trans_object_id1', '-999');
l_real_time_select := replace(l_real_time_select, 'l_trans_object_id2', '-999');
l_real_time_select := replace(l_real_time_select, 'l_trans_object_id3', '-999');
l_real_time_select := replace(l_real_time_select, 'l_trans_object_id4', '-999');
l_real_time_select := replace(l_real_time_select, 'l_trans_object_id5', '-999');
l_real_time_select;
l_real_time_insert := 'INSERT INTO ' || l_real_time_trans_table_name || ' ( ';
l_real_time_insert := l_real_time_insert || l_new_line || l_indent || column_names.column_name;
l_real_time_insert := l_real_time_insert || l_new_line || l_indent || ',' || column_names.column_name;
l_real_time_insert := l_real_time_insert || ')' || l_new_line || p_real_time_sql || ';';
OPEN CUR_REAL_TIME_INSERT(l_trans_usg_pgm_sql_id);
FETCH CUR_REAL_TIME_INSERT INTO l_real_time_insert_clob;
EXIT WHEN CUR_REAL_TIME_INSERT%NOTFOUND;
DBMS_LOB.OPEN(l_real_time_insert_clob, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.WRITEAPPEND(l_real_time_insert_clob,LENGTH(l_real_time_insert),l_real_time_insert);
DBMS_LOB.CLOSE(l_real_time_insert_clob);
CLOSE CUR_REAL_TIME_INSERT;
errbuf := 'Error while trying to update real_time_sql or real_time_insert' || ' : SQLCODE : ' || SQLCODE ||
' : SQLERRM : ' || SQLERRM;
' AS (SELECT A.*, 1 WORKER_ID FROM ( ' || p_batch_total_sql || ' ) A WHERE 1 = 2 )';
'LAST_UPDATE_DATE DATE, ' ||
'LAST_UPDATED_BY NUMBER, ' ||
'CREATION_DATE DATE, ' ||
'CREATED_BY NUMBER, ' ||
'LAST_UPDATE_LOGIN NUMBER, ' ||
'REQUEST_ID NUMBER, ' ||
'PROGRAM_APPLICATION_ID NUMBER, ' ||
'PROGRAM_ID NUMBER, ' ||
'PROGRAM_UPDATE_DATE DATE, ' ||
'TXN_DATE DATE, ' ||
'SECURITY_GROUP_ID NUMBER, ' ||
'OBJECT_VERSION_NUMBER NUMBER) ';
errbuf := 'Error while trying to update batch_total_sql' || ' : SQLCODE : ' || SQLCODE ||
' : SQLERRM : ' || SQLERRM;
l_create_tbl_stmt := 'CREATE TABLE ' || l_jtf_schema || '.' || l_batch_nm_trans_table_name || ' AS (SELECT * FROM ( ' ||
p_batch_total_sql || ' ) WHERE 1 = 2 ) ';
'LAST_UPDATE_DATE DATE, ' ||
'LAST_UPDATED_BY NUMBER, ' ||
'CREATION_DATE DATE, ' ||
'CREATED_BY NUMBER, ' ||
'LAST_UPDATE_LOGIN NUMBER, ' ||
'REQUEST_ID NUMBER, ' ||
'PROGRAM_APPLICATION_ID NUMBER, ' ||
'PROGRAM_ID NUMBER, ' ||
'PROGRAM_UPDATE_DATE DATE, ' ||
'TXN_DATE DATE, ' ||
'SECURITY_GROUP_ID NUMBER, ' ||
'OBJECT_VERSION_NUMBER NUMBER, ' ||
'WORKER_ID NUMBER) ';
errbuf := 'Error while trying to update batch_incr_sql' || ' : SQLCODE : ' || SQLCODE ||
' : SQLERRM : ' || SQLERRM;
' AS (SELECT A.*, 1 WORKER_ID FROM ( ' || p_batch_dea_sql || ' ) A WHERE 1 = 2 )';
'LAST_UPDATE_DATE DATE, ' ||
'LAST_UPDATED_BY NUMBER, ' ||
'CREATION_DATE DATE, ' ||
'CREATED_BY NUMBER, ' ||
'LAST_UPDATE_LOGIN NUMBER, ' ||
'REQUEST_ID NUMBER, ' ||
'PROGRAM_APPLICATION_ID NUMBER, ' ||
'PROGRAM_ID NUMBER, ' ||
'PROGRAM_UPDATE_DATE DATE, ' ||
'SECURITY_GROUP_ID NUMBER, ' ||
'OBJECT_VERSION_NUMBER NUMBER) ';
errbuf := 'Error while trying to update batch_dea_sql' || ' : SQLCODE : ' || SQLCODE ||
' : SQLERRM : ' || SQLERRM;
errbuf := 'Error while trying to update incr_reassign_sql' || ' : SQLCODE : ' || SQLCODE ||
' : SQLERRM : ' || SQLERRM;
End Insert_Row;