CREATE OR REPLACE PROCEDURE convert_byte_to_char IS
v_module_name SGA_W_LOG.procedure_name%TYPE;
v_msg_text SGA_W_LOG.data%TYPE;
v_sql VARCHAR2(1000);
-- Cursor to find all VARCHAR2 columns defined as BYTE
CURSOR byte_cols_cur IS
SELECT table_name, column_name, data_length
FROM user_tab_columns
WHERE data_type = 'VARCHAR2'
AND char_used = 'B'
AND virtual_column = 'NO'
ORDER BY table_name, column_name;
BEGIN
v_module_name := 'convert_byte_to_char';
-- Log the start of the procedure
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, 'Procedure started.', SYSDATE);
COMMIT;
FOR byte_cols_rec IN byte_cols_cur LOOP
BEGIN
-- Construct the dynamic DDL statement
-- e.g., ALTER TABLE MY_TABLE MODIFY (MY_COL VARCHAR2(50 CHAR))
v_sql := 'ALTER TABLE ' || DBMS_ASSERT.enquote_name(byte_cols_rec.table_name) ||
' MODIFY (' || DBMS_ASSERT.enquote_name(byte_cols_rec.column_name) ||
' VARCHAR2(' || byte_cols_rec.data_length || ' CHAR))';
-- Execute the DDL
EXECUTE IMMEDIATE v_sql;
-- Log successful modification
v_msg_text := 'Successfully altered ' || byte_cols_rec.table_name || '.' || byte_cols_rec.column_name || ' to VARCHAR2(' || byte_cols_rec.data_length || ' CHAR).';
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, v_msg_text, SYSDATE);
EXCEPTION
WHEN OTHERS THEN
-- Log any specific errors encountered per table/column so the loop can continue
v_msg_text := 'ERROR altering ' || byte_cols_rec.table_name || '.' || byte_cols_rec.column_name ||': ' || SQLERRM;
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, SUBSTR(v_msg_text, 1, 1500), SYSDATE);
END;
-- Commit the log entry for the current column
COMMIT;
END LOOP;
-- Log completion
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, 'Procedure finished successfully.', SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Catch-all for unexpected global failures
ROLLBACK;
v_msg_text := 'Unexpected Error: CRITICAL FAILURE: ' || SQLERRM;
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, SUBSTR(v_msg_text, 1, 1500), SYSDATE);
COMMIT;
RAISE;
END convert_byte_to_char;
/
v_module_name SGA_W_LOG.procedure_name%TYPE;
v_msg_text SGA_W_LOG.data%TYPE;
v_sql VARCHAR2(1000);
-- Cursor to find all VARCHAR2 columns defined as BYTE
CURSOR byte_cols_cur IS
SELECT table_name, column_name, data_length
FROM user_tab_columns
WHERE data_type = 'VARCHAR2'
AND char_used = 'B'
AND virtual_column = 'NO'
ORDER BY table_name, column_name;
BEGIN
v_module_name := 'convert_byte_to_char';
-- Log the start of the procedure
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, 'Procedure started.', SYSDATE);
COMMIT;
FOR byte_cols_rec IN byte_cols_cur LOOP
BEGIN
-- Construct the dynamic DDL statement
-- e.g., ALTER TABLE MY_TABLE MODIFY (MY_COL VARCHAR2(50 CHAR))
v_sql := 'ALTER TABLE ' || DBMS_ASSERT.enquote_name(byte_cols_rec.table_name) ||
' MODIFY (' || DBMS_ASSERT.enquote_name(byte_cols_rec.column_name) ||
' VARCHAR2(' || byte_cols_rec.data_length || ' CHAR))';
-- Execute the DDL
EXECUTE IMMEDIATE v_sql;
-- Log successful modification
v_msg_text := 'Successfully altered ' || byte_cols_rec.table_name || '.' || byte_cols_rec.column_name || ' to VARCHAR2(' || byte_cols_rec.data_length || ' CHAR).';
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, v_msg_text, SYSDATE);
EXCEPTION
WHEN OTHERS THEN
-- Log any specific errors encountered per table/column so the loop can continue
v_msg_text := 'ERROR altering ' || byte_cols_rec.table_name || '.' || byte_cols_rec.column_name ||': ' || SQLERRM;
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, SUBSTR(v_msg_text, 1, 1500), SYSDATE);
END;
-- Commit the log entry for the current column
COMMIT;
END LOOP;
-- Log completion
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, 'Procedure finished successfully.', SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Catch-all for unexpected global failures
ROLLBACK;
v_msg_text := 'Unexpected Error: CRITICAL FAILURE: ' || SQLERRM;
INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
VALUES (v_module_name, SUBSTR(v_msg_text, 1, 1500), SYSDATE);
COMMIT;
RAISE;
END convert_byte_to_char;
/
