Скрипт sql не запускается при старте приложения

Question

Работаю над приложением на Spring Boot. Написал SQL скрипт для Oracle БД. Суть скрипта в том, чтобы при старте приложения проверялось наличие конкретного столбца в таблице. Если столбца нет, то необходимо его создать и скопировать в него данные из старого столбца из этой таблице.

Когда пробую запустить скрипт из DBeaver, то все отрабатывает нормально, но когда вставляю в .sql файл и запускаю приложение, то скрипт не срабатывает. Но необходимо, чтобы скрипт запускался сам каждый раз при старте приложения, так как приложение пишется для клиентов, а у них там некому запускать скрипты вручную после старта.

Приложению уже несколько лет, писал его не я.

Подскажите, пожалуйста, почему скрипт из .sql файла может не запускаться?

Вот сам скрипт:

DECLARE
  v_column_exists NUMBER := 0;
BEGIN
  SELECT COUNT(*) INTO v_column_exists
    FROM user_tab_cols
    WHERE UPPER(column_name) = 'password_clob'
      AND UPPER(table_name) = 'my_table';

  IF (v_column_exists = 0) THEN
  EXECUTE IMMEDIATE
  'ALTER TABLE my_table ADD (password_clob CLOB)';
  EXECUTE IMMEDIATE
  'UPDATE my_table SET password_clob = password';
  END IF;
END;

Update

Внёс правки, которые посоветовал @0xdb, но все равно пока не добился желаемого результата. Сыпятся ошибки:

2021-08-30 18:40:01.659DEBUG [main] (ScriptUtils.java:618) - Failed to execute SQL script statement #498 of class path resource [oracle/oracleScripts.sql]: DECLARE column_exists NUMBER
java.sql.SQLException: ORA-06550: line 1, column 28:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( @ % ; not null range default character

Caused by: Error : 6550, Position : 27, Sql = DECLARE column_exists NUMBER, OriginalSql = DECLARE column_exists NUMBER, Error Msg = ORA-06550: line 1, column 28:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( @ % ; not null range default character

2021-08-30 18:40:01.662DEBUG [main] (ScriptUtils.java:618) - Failed to execute SQL script statement #499 of class path resource [oracle/oracleScripts.sql]: BEGIN SELECT COUNT(*) INTO column_exists FROM user_tab_cols WHERE table_name = UPPER('my_table') AND column_name = UPPER('col2')
java.sql.SQLException: ORA-06550: line 1, column 154:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;

Caused by: Error : 6550, Position : 153, Sql = BEGIN SELECT COUNT(*) INTO column_exists FROM user_tab_cols WHERE table_name = UPPER('my_table') AND column_name = UPPER('col2'), OriginalSql = BEGIN SELECT COUNT(*) INTO column_exists FROM user_tab_cols WHERE table_name = UPPER('my_table') AND column_name = UPPER('col2'), Error Msg = ORA-06550: line 1, column 154:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;

2021-08-30 18:40:01.668DEBUG [main] (ScriptUtils.java:618) - Failed to execute SQL script statement #500 of class path resource [oracle/oracleScripts.sql]: IF (column_exists = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE my_table ADD (col2 CLOB)'
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

Caused by: Error : 900, Position : 0, Sql = IF (column_exists = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE my_table ADD (col2 CLOB)', OriginalSql = IF (column_exists = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE my_table ADD (col2 CLOB)', Error Msg = ORA-00900: invalid SQL statement

2021-08-30 18:40:01.669DEBUG [main] (ScriptUtils.java:618) - Failed to execute SQL script statement #501 of class path resource [oracle/oracleScripts.sql]: EXECUTE IMMEDIATE 'UPDATE my_table SET col2 = col1'
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

Caused by: Error : 900, Position : 0, Sql = EXECUTE IMMEDIATE 'UPDATE my_table SET col2 = col1', OriginalSql = EXECUTE IMMEDIATE 'UPDATE my_table SET col2 = col1', Error Msg = ORA-00900: invalid SQL statement

2021-08-30 18:40:01.671DEBUG [main] (ScriptUtils.java:618) - Failed to execute SQL script statement #502 of class path resource [oracle/oracleScripts.sql]: END IF
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

Caused by: Error : 900, Position : 0, Sql = END IF, OriginalSql = END IF, Error Msg = ORA-00900: invalid SQL statement

2021-08-30 18:40:01.674DEBUG [main] (ScriptUtils.java:618) - Failed to execute SQL script statement #503 of class path resource [oracle/oracleScripts.sql]: END
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

Caused by: Error : 900, Position : 0, Sql = END, OriginalSql = END, Error Msg = ORA-00900: invalid SQL statement

Answer 1

Анонимный блок в вопросе почти рабочий. Устранил некоторые логичские ошибки и добавил вывод для диагностики (на db<>fiddle):

DECLARE
    column_exists NUMBER;
BEGIN
    SELECT COUNT(*) INTO column_exists
    FROM user_tab_cols
    WHERE table_name  = UPPER('my_table')
    AND   column_name = UPPER('col2');
    dbms_output.put_line ('column_exists='||column_exists); 
    IF (column_exists = 0) THEN
        EXECUTE IMMEDIATE 'ALTER TABLE my_table ADD (col2 CLOB)';
        EXECUTE IMMEDIATE 'UPDATE my_table SET col2 = col1';
        dbms_output.put_line ('column added rowcount='||sql%rowcount); 
    ELSE 
        dbms_output.put_line ('nothing to do'); 
    END IF;
END;
/

Хочу, чтобы скрипт находился именно в файле .sql

Прямого способа отправить на выполнение файл .sql на сервер БД не существует. Любой инструмент или любая библиотека, которые могут обработать файлы .sql, делают нечего иного, как:

считывают отдельные SQL запросы или PL/SQL блоки из файла
отправляют их "поштучно" на выполнение серверу БД

Update

Класс ScriptUtils, которым ТС пытается запустить файл .sql, расчитан на "чистые" SQL скрипты, т.е. состоящие только из SQL выражений, коим PL/SQL блок не является.

По умолчанию метод executeSqlScript использует ";" как разделитель отдельных SQL выражений, но в PL/SQL блоке этот символ является частью синтаксиса. Как следствие, на сервер БД отправляются обрывки PL/SQL блока:

DECLARE column_exists NUMBER
                        ----^ здесь ";" был воспринят как разделитель и удалён  
ORA-06550: line 1, column 28:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( @ % ; not null range default character
          ----^ синтаксически здесь ожидается этот символ

БЛОГ НА HUSL

Скрипт sql не запускается при старте приложения

Ответы (1 шт):