[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT status
INTO l_status
FROM ad_long_column_conversions
WHERE schema_name = p_Schema
AND table_name = p_Table_Name
AND old_column_name = p_Old_Column_Name;
PROCEDURE update_table_status( p_Schema IN VARCHAR2 ,
p_Table_Name IN VARCHAR2 ,
p_Old_Column_Name IN VARCHAR2 ,
p_Status IN VARCHAR2 )
IS
l_current_status VARCHAR2 (30);
G_UPDATE_ROWS_STATUS,
G_COMPLETE_STATUS,
G_DROP_OLD_COLUMN_STATUS))
THEN
RAISE_APPLICATION_ERROR (-20001,
'update_table_status() - invalid status : '||p_Status);
(p_Status = l2l_pack.G_UPDATE_ROWS_STATUS AND
l_current_status <> l2l_pack.G_ADD_TRIGGER_STATUS)
OR
(p_Status = l2l_pack.G_SWAP_STATUS1 AND
l_current_status <> l2l_pack.G_UPDATE_ROWS_STATUS)
OR
(p_Status = l2l_pack.G_SWAP_STATUS2 AND
l_current_status <> l2l_pack.G_SWAP_STATUS1)
OR
(p_Status = l2l_pack.G_COMPLETE_STATUS AND
l_current_status <> l2l_pack.G_SWAP_STATUS2)
)
THEN
RAISE_APPLICATION_ERROR (-20001,
'update_table_status() - invalid status change : '||
l_current_status||' to '||p_Status);
UPDATE AD_LONG_COLUMN_CONVERSIONS
SET status = p_Status
WHERE schema_name = p_Schema
AND table_name = p_Table_Name
AND old_column_name = p_Old_Column_Name;
END update_table_status;
INSERT INTO AD_LONG_COLUMN_CONVERSIONS (
schema_name, table_name, old_column_name,
old_data_type,
new_column_name, new_data_type,
action, status
)
SELECT l_Schema, p_Table_Name, p_Old_Column_Name,
p_Old_Data_Type,
l_New_Column_Name, p_New_Data_Type,
p_Action, l_Status
FROM dual
WHERE NOT EXISTS (
SELECT 'x'
FROM AD_LONG_COLUMN_CONVERSIONS l
WHERE l.schema_name = l_Schema
AND l.table_name = p_Table_Name
AND l.old_column_name = p_Old_Column_Name);
register_table('AMS', 'AMS_EXP_MODIFIED_TEMPLATE', 'USER_SELECT_CLAUSE', 'LONG', 'USER_SELECT_CLAUSE', 'CLOB', G_WITH_DATA );
register_table('AMS', 'AMS_EXP_TEMPLATE', 'SEEDED_SELECT_CLAUSE', 'LONG', 'SEEDED_SELECT_CLAUSE', 'CLOB', G_WITH_DATA );
register_table('AZ', 'AZ_SELECTION_SET_APIS', 'FILTERING_PARAMETER', 'LONG', 'FILTERING_PARAMETER', 'CLOB', G_WITH_DATA );
register_table('AZ', 'AZ_SELECTION_SET_ENTITIES_B', 'FILTERING_PARAMETERS', 'LONG', 'FILTERING_PARAMETERS', 'CLOB', G_WITH_DATA );
register_table('BOM', 'BOM_DELETE_SQL_STATEMENTS', 'SQL_STATEMENT', 'LONG', 'SQL_STATEMENT', 'CLOB', G_WITH_DATA );
register_table('DDD', 'DDD_UPDATES_DATA', 'DATA', 'LONG RAW', 'DATA', 'BLOB', G_WITH_DATA );
register_table('OFA', 'FA_RX_LOV', 'SELECT_STATEMENT', 'LONG', 'SELECT_STATEMENT', 'VARCHAR2', G_WITH_DATA );
register_table('PA', 'PA_RULES', 'SELECT_STATEMENT', 'LONG', 'SELECT_STATEMENT', 'CLOB', G_WITH_DATA );
update_table_status(p_Schema,
p_Table_Name,
p_Old_Column_Name,
l_New_Status );
' AFTER INSERT OR UPDATE OF '||p_Old_Column_Name||
' ON '||p_Schema||'.'||p_Table_Name||
' FOR EACH ROW'||
' BEGIN'||
' INSERT INTO AD_LONG_CONV_TEMP(table_name, apps_rowid) '||
' VALUES ('''||p_Table_Name||''', :new.rowid);'||
' AFTER INSERT OR UPDATE OF '||p_Old_Column_Name||
' ON '||p_Schema||'.'||p_Table_Name||
''||
' DECLARE '||
' CURSOR c_tmp IS '||
' SELECT apps_rowid, '||l_lob_value_col||
' FROM AD_LONG_CONV_TEMP2'||
' WHERE table_name = '''||p_Table_Name||''';'||
' INSERT INTO AD_LONG_CONV_TEMP2('||
' table_name, apps_rowid, '||l_lob_value_col ||' ) '||
' SELECT '''||p_Table_Name||''', t.apps_rowid, '||
' to_lob(f.'||p_Old_Column_Name ||')'||
' FROM AD_LONG_CONV_TEMP t, '||p_Schema||
'.'||p_Table_Name||' f '||
' WHERE f.rowid = t.apps_rowid;'||
' UPDATE '||p_Schema||'.'||p_Table_Name ||
' SET '||p_New_Column_Name ||' = c_rec.'||l_lob_value_col||
' WHERE rowid = c_rec.apps_rowid;'||
update_table_status( p_Schema ,
p_Table_Name ,
p_Old_Column_Name ,
G_ADD_TRIGGER_STATUS);
PROCEDURE update_new_data(p_Schema IN VARCHAR2 ,
p_Old_Table_Name IN VARCHAR2 ,
p_Old_Column_Name IN VARCHAR2 ,
p_Old_Data_Type IN VARCHAR2 ,
p_New_Column_Name IN VARCHAR2 ,
p_Batch_Size IN NUMBER DEFAULT 1000)
IS
l_stmt VARCHAR2 (10000);
' select rowid'||
' from '||p_Schema||'.'||p_Old_Table_Name||
' where '||p_Old_Column_Name||' is not null '||
' and '||p_New_Column_Name||' is null; '||
' select apps_rowid, '||l_lob_value_col_name||
' from ad_long_conv_temp2;'||
' insert into ad_long_conv_temp2(table_name,'||
' apps_rowid, '||l_lob_value_col_name||')'||
' select '''||p_Old_Table_Name||''','||
' rowid, to_lob('||p_Old_Column_Name||')'||
' from '||p_Schema||'.'||p_Old_Table_Name||
' where rowid = rowtab(i);'||
' update '||p_Schema||'.'||p_Old_Table_Name||
' set '||p_New_Column_Name||' = c_rec2.'||
l_lob_value_col_name||
' where rowid = c_rec2.apps_rowid;'||
update_table_status( p_Schema,
p_Old_Table_Name,
p_Old_Column_Name,
G_UPDATE_ROWS_STATUS);
END update_new_data;
-- value to be selected
l_offset INTEGER; -- the byte position in the LONG column at which
l_stmt := 'SELECT '||p_Long_Column_Name||' FROM '||p_Table_Name||' WHERE ROWID = '''||p_Rowid||'''';
SELECT table_name, column_name, a.owner, data_type, bytes/1024/1024 t_size
FROM dba_tab_columns a, dba_segments b
WHERE data_type IN ('LONG', 'LONG RAW')
AND table_name = p_Table_name
AND segment_name = table_name
AND a.owner NOT IN ('SYS','SYSTEM','OUTLN')
AND b.owner = a.owner ;
SELECT table_name, NEW_DATA_TYPE
BULK COLLECT
INTO l_TableNames_Tbl, l_To_DataType_Tbl
FROM ad_long_column_conversions;
l_query := 'select count(*) from '||rec.owner||'.'||rec.table_name;
l_query := 'select count(*) from '||rec.owner||'.'||rec.table_name||' where '||rec.column_name||' is not null';
OPEN l_cursor FOR 'SELECT ROWID FROM '||rec.table_name;
SELECT COUNT(*)
INTO l_Count
FROM ad_long_column_conversions
WHERE schema_name = p_Schema
AND table_name = p_Table_Name ;
SELECT status
INTO l_Current_Status
FROM ad_long_column_conversions
WHERE schema_name = p_Schema
AND table_name = p_Table_Name
FOR UPDATE NOWAIT ;
UPDATE ad_long_column_conversions
SET status = G_DEFERRED_STATUS||'_'||status
WHERE schema_name = p_Schema
AND table_name = p_Table_Name;
SELECT LTRIM (status,G_DEFERRED_STATUS||'_')
INTO l_Prev_Status
FROM ad_long_column_conversions
WHERE schema_name = p_Schema
AND table_name = p_Table_Name
AND status LIKE G_DEFERRED_STATUS||'_%'
FOR UPDATE NOWAIT ;
UPDATE ad_long_column_conversions
SET status = l_Prev_Status
WHERE schema_name = p_Schema
AND table_name = p_Table_Name;