I am trying to pull together a script that will run through a given schema and output all of the table data as insert statements.
I have tested the script on a couple of tables and has completed successfully so started to run it on the full schema and get a SQLCODE 433 SQLSTATE 42806 error. SQLSTATE 42806 error looks like its when the variables are incompatible but the table it fails on looks like it completes successfully when I trigger the procedure with only that table specified in the SQL.
The table is made up of the following data types:
BIGINT NOT NULL,
INTEGER NOT NULL,
SMALLINT,
TIMESTAMP,
VARCHAR (255),
VARCHAR (255),
DATE,
DATE,
VARCHAR (1) DEFAULT 'Y',
VARCHAR (255) NOT NULL,
DATE,
BIGINT
I'm using UTL_FILE to write the insert statements to an output file and not sure whether I'm hitting a limit on what I can write out, but cannot see any restrictions from the documentation I'm reading online.
The code for the procedure is:
CREATE OR REPLACE PROCEDURE CREATE_INSERTS
(
IN in_schema VARCHAR(50),
IN in_output_dir VARCHAR(100),
IN in_output_file VARCHAR(100),
OUT out_message VARCHAR(100)
)
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_table_name VARCHAR(128);
DECLARE c_datatype CONDITION FOR SQLSTATE '20000';
DECLARE c_no_output_dir CONDITION FOR SQLSTATE '99901';
DECLARE c_no_output_file CONDITION FOR SQLSTATE '99902';
DECLARE c_no_output_dir_file CONDITION FOR SQLSTATE '99903';
DECLARE c_noselect_cols CONDITION FOR SQLSTATE '99904';
DECLARE v_filehandle UTL_FILE.FILE_TYPE;
DECLARE isOpen BOOLEAN;
DECLARE v_dirAlias VARCHAR(50) DEFAULT 'mydir';
DECLARE v_filename VARCHAR(20) DEFAULT 'myfile.sql';
DECLARE v_datSQL VARCHAR(1000);
DECLARE v_Column_Name VARCHAR(128);
DECLARE v_Data_Type VARCHAR(18);
DECLARE v_FirstTimeFlg INTEGER DEFAULT 1;
DECLARE v_dynSQL VARCHAR(10000);
DECLARE v_colstatement VARCHAR(10000) DEFAULT ' ';
DECLARE v_datstatement VARCHAR(10000) DEFAULT ' ';
DECLARE v_data VARCHAR(10000);
DECLARE v_start_quotes VARCHAR(20) DEFAULT '''''''''||';
DECLARE v_end_quotes VARCHAR(20) DEFAULT '||''''''''';
DECLARE v_cur_statement STATEMENT;
DECLARE v_col_statement STATEMENT;
DECLARE v_table_statement STATEMENT;
DECLARE c_table CURSOR FOR v_table_statement;
DECLARE c_column CURSOR FOR v_col_statement;
DECLARE c_data CURSOR FOR v_cur_statement;
-- Catch errors
DECLARE EXIT HANDLER FOR c_no_output_dir_file
SET out_message = 'There was no output directory or file specified';
DECLARE EXIT HANDLER FOR c_no_output_dir
SET out_message = 'There was no output directory specified';
DECLARE EXIT HANDLER FOR c_no_output_file
SET out_message = 'There was no output file specified for the insert statements';
DECLARE EXIT HANDLER FOR c_noselect_cols
SET out_message = 'There was no columns found in table: ' || v_table_name;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET out_message = 'Error, SQLSTATE : " '|| SQLSTATE ||'",SQLCODE=' || CHAR(SQLCODE);
-- Check if file and location has been provided before proceeding...
IF in_output_dir IS NULL AND in_output_file IS NULL THEN
SIGNAL c_no_output_dir_file;
ELSEIF in_output_dir IS NULL THEN
SIGNAL c_no_output_dir;
ELSEIF in_output_file IS NULL THEN
SIGNAL c_no_output_file;
END IF;
-- Open file
CALL UTL_DIR.CREATE_OR_REPLACE_DIRECTORY(v_dirAlias, in_output_dir);
SET v_filehandle = UTL_FILE.FOPEN(v_dirAlias, in_output_file, 'w');
SET isOpen = UTL_FILE.IS_OPEN(v_filehandle);
IF isOpen != TRUE THEN
RETURN -1;
END IF;
SET v_datSQL = 'SELECT TABLE.NAME'
||' FROM SYSIBM.SYSTABLES TABLE'
||' WHERE UPPER(TABLE.CREATOR) = UPPER(''' || in_schema || ''')'
||' AND TABLE.TYPE = ''T''';
PREPARE v_table_statement from v_datSQL;
OPEN c_table;
FETCH c_table INTO v_table_name;
WHILE (SQLSTATE = '00000') DO
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- #########################');
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- #');
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- # INSERT ' || v_table_name);
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- #');
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- #########################');
SET v_datSQL = 'SELECT colname, typename FROM '
||' syscat.columns'
||' WHERE UPPER(TabName) = UPPER('''||v_table_name||''')'
||' AND UPPER(TabSchema) = UPPER('''||in_schema||''')'
||' ORDER BY colno';
PREPARE v_col_statement from v_datSQL;
OPEN c_column;
FETCH c_column INTO v_Column_Name, v_Data_Type;
WHILE (SQLSTATE = '00000') DO
IF v_FirstTimeFlg = 1 THEN
SET v_colstatement = v_column_name ;
SET v_FirstTimeFlg = 2;
ELSE
SET v_colstatement = v_colstatement || ' ,' || v_column_name ;
END IF;
FETCH c_column INTO v_Column_Name, v_Data_Type;
END while;
IF v_FirstTimeFlg = 1 THEN
SIGNAL c_noselect_cols;
END IF;
CLOSE c_column;
SET v_FirstTimeFlg = 1;
OPEN c_column;
FETCH c_column INTO v_Column_Name, v_Data_Type;
WHILE (SQLSTATE = '00000') DO
IF v_Data_Type NOT IN ('BIGINT', 'INTEGER', 'DECIMAL', 'SMALLINT', 'CHARACTER','VARCHAR', 'BLOB', 'CLOB', 'DATE','TIME','TIMESTAMP') THEN
SIGNAL c_datatype;
END IF;
IF v_Data_Type NOT IN ('BLOB', 'CLOB') THEN
IF v_FirstTimeFlg = 1 THEN
SET v_datstatement = v_datstatement ||
CASE v_Data_Type
WHEN 'BIGINT' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'INTEGER' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'DECIMAL' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'SMALLINT' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'CHARACTER' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'VARCHAR' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'BLOB' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'CLOB' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'DATE' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
WHEN 'TIME' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
WHEN 'TIMESTAMP' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
ELSE CHR(9) -- this will never happen
END;
SET v_FirstTimeFlg = 2;
ELSE
SET v_datstatement = v_datstatement || ' || '' , '' || ' ||
CASE v_Data_Type
WHEN 'BIGINT' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'INTEGER' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'DECIMAL' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'SMALLINT' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'CHARACTER' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'VARCHAR' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'BLOB' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'CLOB' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'DATE' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
WHEN 'TIME' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
WHEN 'TIMESTAMP' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
ELSE CHR(9) -- this will never happen
END;
END IF;
END IF;
FETCH c_column INTO v_Column_Name, v_Data_Type;
END while;
CLOSE c_column;
SET v_dynSQL = 'SELECT '||TRIM(v_datstatement)|| ' FROM ' || UPPER(TRIM(in_schema)) || '.' || UPPER(TRIM(v_table_name)) || ' ';
PREPARE v_cur_statement from v_dynSQL;
OPEN c_data;
FETCH c_data INTO v_data;
WHILE (SQLSTATE = '00000') DO
CALL UTL_FILE.PUT_LINE(v_filehandle,'INSERT INTO '|| UPPER(v_table_name) || ' ( '||v_colstatement|| ') VALUES( ' || v_data ||');');
FETCH c_data INTO v_data;
END while;
SET v_datstatement = '';
SET v_dynSQL = '';
CLOSE c_data;
CALL UTL_FILE.NEW_LINE(v_filehandle, 2);
FETCH c_table INTO v_table_name;
END while;
CLOSE c_table;
CALL UTL_FILE.FCLOSE(v_filehandle);
SET out_message = 'Processing Success';
END
Thanks for any help
I found that it was a issue with FOPEN because the line was too big for the function. To fix the issue, I used the overloaded FOPEN function and used the max size of 32767 and it generates the INSERT statements correctly into one file.
The final code is below in case its of any use:
CREATE OR REPLACE PROCEDURE CREATE_INSERTS
(
IN in_schema VARCHAR(50),
IN in_output_dir VARCHAR(100),
IN in_output_file VARCHAR(100),
OUT out_message VARCHAR(100)
)
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_table_name VARCHAR(128);
DECLARE c_datatype CONDITION FOR SQLSTATE '20000';
DECLARE c_no_output_dir CONDITION FOR SQLSTATE '99901';
DECLARE c_no_output_file CONDITION FOR SQLSTATE '99902';
DECLARE c_no_output_dir_file CONDITION FOR SQLSTATE '99903';
DECLARE c_noselect_cols CONDITION FOR SQLSTATE '99904';
DECLARE v_filehandle UTL_FILE.FILE_TYPE;
DECLARE isOpen BOOLEAN;
DECLARE v_dirAlias VARCHAR(50) DEFAULT 'mydir';
DECLARE v_filename VARCHAR(20) DEFAULT 'myfile.sql';
DECLARE v_datSQL VARCHAR(1000);
DECLARE v_Column_Name VARCHAR(128);
DECLARE v_Data_Type VARCHAR(18);
DECLARE v_FirstTimeFlg INTEGER DEFAULT 1;
DECLARE v_dynSQL VARCHAR(10000);
DECLARE v_colstatement VARCHAR(10000) DEFAULT ' ';
DECLARE v_datstatement VARCHAR(10000) DEFAULT ' ';
DECLARE v_data VARCHAR(10000);
DECLARE v_start_quotes VARCHAR(20) DEFAULT '''''''''||';
DECLARE v_end_quotes VARCHAR(20) DEFAULT '||''''''''';
DECLARE v_cur_statement STATEMENT;
DECLARE v_col_statement STATEMENT;
DECLARE v_table_statement STATEMENT;
DECLARE c_table CURSOR FOR v_table_statement;
DECLARE c_column CURSOR FOR v_col_statement;
DECLARE c_data CURSOR FOR v_cur_statement;
-- Catch errors
DECLARE EXIT HANDLER FOR c_no_output_dir_file
SET out_message = 'There was no output directory or file specified';
DECLARE EXIT HANDLER FOR c_no_output_dir
SET out_message = 'There was no output directory specified';
DECLARE EXIT HANDLER FOR c_no_output_file
SET out_message = 'There was no output file specified for the insert statements';
DECLARE EXIT HANDLER FOR c_noselect_cols
SET out_message = 'There was no columns found in table: ' || v_table_name;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET out_message = 'Error, SQLSTATE : " '|| SQLSTATE ||'",SQLCODE=' || CHAR(SQLCODE);
-- Check if file and location has been provided before proceeding...
IF in_output_dir IS NULL AND in_output_file IS NULL THEN
SIGNAL c_no_output_dir_file;
ELSEIF in_output_dir IS NULL THEN
SIGNAL c_no_output_dir;
ELSEIF in_output_file IS NULL THEN
SIGNAL c_no_output_file;
END IF;
-- Open file
CALL UTL_DIR.CREATE_OR_REPLACE_DIRECTORY(v_dirAlias, in_output_dir);
SET v_filehandle = UTL_FILE.FOPEN(v_dirAlias, in_output_file, 'w', 32767);
SET isOpen = UTL_FILE.IS_OPEN(v_filehandle);
IF isOpen != TRUE THEN
RETURN -1;
END IF;
SET v_datSQL = 'SELECT TABLE.NAME'
||' FROM SYSIBM.SYSTABLES TABLE'
||' WHERE UPPER(TABLE.CREATOR) = UPPER(''' || in_schema || ''')'
||' AND TABLE.TYPE = ''T'''
||' ORDER BY TABLE.NAME ASC'
;
PREPARE v_table_statement from v_datSQL;
OPEN c_table;
FETCH c_table INTO v_table_name;
WHILE (SQLSTATE = '00000') DO
CALL DBMS_OUTPUT.PUT_LINE('Processing table: ' || v_table_name);
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- #########################');
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- #');
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- # INSERT ' || v_table_name);
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- #');
CALL UTL_FILE.PUT_LINE(v_filehandle, '-- #########################');
SET v_FirstTimeFlg = 1;
SET v_datSQL = 'SELECT colname, typename FROM '
||' syscat.columns'
||' WHERE UPPER(TabName) = UPPER('''||v_table_name||''')'
||' AND UPPER(TabSchema) = UPPER('''||in_schema||''')'
||' ORDER BY colno';
PREPARE v_col_statement from v_datSQL;
OPEN c_column;
FETCH c_column INTO v_Column_Name, v_Data_Type;
WHILE (SQLSTATE = '00000') DO
IF v_FirstTimeFlg = 1 THEN
SET v_colstatement = v_column_name ;
SET v_FirstTimeFlg = 2;
ELSE
SET v_colstatement = v_colstatement || ' ,' || v_column_name ;
END IF;
FETCH c_column INTO v_Column_Name, v_Data_Type;
END while;
IF v_FirstTimeFlg = 1 THEN
SIGNAL c_noselect_cols;
END IF;
CLOSE c_column;
SET v_FirstTimeFlg = 1;
OPEN c_column;
FETCH c_column INTO v_Column_Name, v_Data_Type;
WHILE (SQLSTATE = '00000') DO
IF v_Data_Type NOT IN ('BIGINT', 'INTEGER', 'DOUBLE', 'DECIMAL', 'SMALLINT', 'CHARACTER', 'LONG VARCHAR', 'VARCHAR', 'BLOB', 'CLOB', 'DATE','TIME','TIMESTAMP') THEN
SIGNAL c_datatype;
END IF;
IF v_FirstTimeFlg = 1 THEN
SET v_datstatement = v_datstatement ||
CASE v_Data_Type
WHEN 'BIGINT' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'INTEGER' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'DOUBLE' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'DECIMAL' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'SMALLINT' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'CHARACTER' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'LONG VARCHAR' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'VARCHAR' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'BLOB' THEN 'coalesce(' ||v_start_quotes || 'CAST (' || v_Column_Name || ' AS VARCHAR(32000))' || v_end_quotes ||',''NULL'')'
WHEN 'CLOB' THEN 'coalesce(' ||v_start_quotes || 'CAST (' || v_Column_Name || ' AS VARCHAR(32000))' || v_end_quotes ||',''NULL'')'
WHEN 'DATE' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
WHEN 'TIME' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
WHEN 'TIMESTAMP' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
ELSE CHR(9) -- this will never happen
END;
SET v_FirstTimeFlg = 2;
ELSE
SET v_datstatement = v_datstatement || ' || '' , '' || ' ||
CASE v_Data_Type
WHEN 'BIGINT' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'INTEGER' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'DOUBLE' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'DECIMAL' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'SMALLINT' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
WHEN 'CHARACTER' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'LONG VARCHAR' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'VARCHAR' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')'
WHEN 'BLOB' THEN 'coalesce(' ||v_start_quotes || 'CAST (' || v_Column_Name || ' AS VARCHAR(32000))' || v_end_quotes ||',''NULL'')'
WHEN 'CLOB' THEN 'coalesce(' ||v_start_quotes || 'CAST (' || v_Column_Name || ' AS VARCHAR(32000))' || v_end_quotes ||',''NULL'')'
WHEN 'DATE' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
WHEN 'TIME' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
WHEN 'TIMESTAMP' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')' ELSE CHR(9) -- this will never happen
END;
END IF;
FETCH c_column INTO v_Column_Name, v_Data_Type;
END while;
CLOSE c_column;
SET v_dynSQL = 'SELECT '||TRIM(v_datstatement)|| ' FROM ' || UPPER(TRIM(in_schema)) || '.' || UPPER(TRIM(v_table_name)) || ' ';
PREPARE v_cur_statement from v_dynSQL;
OPEN c_data;
FETCH c_data INTO v_data;
WHILE (SQLSTATE = '00000') DO
CALL UTL_FILE.PUT_LINE(v_filehandle,'INSERT INTO '|| UPPER(v_table_name) || ' ( '||v_colstatement|| ') VALUES( ' || v_data ||');');
FETCH c_data INTO v_data;
END while;
SET v_colstatement = '';
SET v_datstatement = '';
SET v_dynSQL = '';
CLOSE c_data;
CALL UTL_FILE.NEW_LINE(v_filehandle, 2);
FETCH c_table INTO v_table_name;
END while;
CLOSE c_table;
CALL UTL_FILE.FCLOSE(v_filehandle);
SET out_message = 'Processing Success';
END