Procedure fails to create executing from Urban Code Deploy

Question

I've run into problems with following script which I'm trying to execute. In SQLDeveloper or other tools like DataGrip, everything works fine, so in my understanding, this is UCD issue.

...more inserts
insert into M_MIGRATION_CONTROL values ('PRODUCTREL', 'M_' || 'PRODUCTREL');

CREATE OR REPLACE PROCEDURE MIGRATE_DATA_BEFORE_DEPLOY IS
  TABLE_NAME VARCHAR2(128);
  MIGRATION_TABLE_NAME VARCHAR2(128);
  CURSOR MIGRATION_CURSOR IS SELECT * FROM M_MIGRATION_CONTROL;
  BEGIN
    OPEN MIGRATION_CURSOR;
    LOOP
      FETCH MIGRATION_CURSOR INTO TABLE_NAME, MIGRATION_TABLE_NAME;
      EXIT WHEN MIGRATION_CURSOR%NOTFOUND;
      EXECUTE IMMEDIATE 'CREATE TABLE '||MIGRATION_TABLE_NAME||' AS SELECT * FROM '||TABLE_NAME||'';
    END LOOP;
    CLOSE MIGRATION_CURSOR;
  END MIGRATE_DATA_BEFORE_DEPLOY;
/

insert into A_ACCEPTEDINCOMETYPEOPTION select * from M_A_ACCEPTEDINCOMETYPEOPTION;
insert into A_INSTALMENT select * from M_A_INSTALMENT;
...more inserts

with errors:

[sql] Failed to execute:  MIGRATION_TABLE_NAME VARCHAR2(128)
      [sql] java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
      [sql] Failed to execute:  CURSOR MIGRATION_CURSOR IS SELECT * FROM M_MIGRATION_CONTROL

When I print procedure body from Oracle i get:

PROCEDURE MIGRATE_DATA_BEFORE_DEPLOY IS TABLE_NAME VARCHAR2(128)

So I think that client ends create procedure after first ;

Can you help me/advise me, how should this script look like for correct execution?

(Oracle 12.1)

Answer 1

You are using SQL-JDBC plugin for Urban Code Deploy.

According to their documentation/troubleshooting:

The SQL-JDBC plugin does not support deploying stored procedures.

There is a separate SQL*Plus plugin which you may be able to use instead.