SQLCODE 433 SQLSTATE 42806 при запуске процедуры DB2, которая генерирует операторы вставки
Я пытаюсь собрать сценарий, который будет проходить через данную схему и выводить все данные таблицы в виде операторов вставки.
Я протестировал скрипт на нескольких таблицах и успешно завершил его, поэтому начал запускать его по полной схеме и получил ошибку SQLCODE 433 SQLSTATE 42806. Ошибка SQLSTATE 42806 выглядит так, как будто переменные несовместимы, но таблица, в которой происходит сбой, выглядит так, как будто она успешно завершается, когда я запускаю процедуру только с той таблицей, которая указана в SQL.
Таблица состоит из следующих типов данных:
BIGINT NOT NULL,
INTEGER NOT NULL,
SMALLINT,
TIMESTAMP,
VARCHAR (255),
VARCHAR (255),
DATE,
DATE,
VARCHAR (1) DEFAULT 'Y',
VARCHAR (255) NOT NULL,
DATE,
BIGINT
Я использую UTL_FILE, чтобы записать операторы вставки в выходной файл, и не уверен, достигну ли я предела тому, что я могу выписать, но не вижу никаких ограничений в документации, которую я читаю онлайн.
Код для процедуры:
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
Спасибо за любую помощь
1 ответ
Решение
Я обнаружил, что это проблема с FOPEN, потому что линия была слишком большой для функции. Чтобы решить эту проблему, я использовал перегруженную функцию FOPEN и использовал максимальный размер 32767, и он правильно генерирует операторы INSERT в один файл.
Окончательный код приведен ниже в случае его использования:
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