PL/SQL is not fully supported

[bence-takacs]

Currently the Oracle PL/SQL blocks are not fully supported by ScriptRunner.

There is a common workaround:

ALTER TABLE TAB1
  ADD CONSTRAINT PK_TAB1
PRIMARY KEY (TAB1_ID)
/

CREATE OR REPLACE PROCEDURE UPDATE_TAB1 AS
BEGIN
  UPDATE TAB1 SET NUMBER_FIELD = NUMBER_FIELD + 1;
  COMMIT;
END;
/

using:

send_full_script=false
delimiter=/
full_line_delimiter=true

...but this is not a clean solution:

• we need to modify a bunch of SQLs for the sake of a few PL/SQL
• not standard

There is a suggested solution that modifies the ScriptRunner (https://code.google.com/p/mybatis/source/diff?spec=svn4001&r=4001&format=side&path=/trunk/src/main/java/org/apache/ibatis/jdbc/ScriptRunner.java) but it has been rejected.

Is it possible to annotate the PL/SQLs instead of the SQLs and keep the common delimiters?

ALTER TABLE TAB1
  ADD CONSTRAINT PK_TAB1
PRIMARY KEY (TAB1_ID);

-- //@PLSQL
CREATE OR REPLACE PROCEDURE UPDATE_TAB1 AS
BEGIN
  UPDATE TAB1 SET NUMBER_FIELD = NUMBER_FIELD + 1;
  COMMIT;
END;
/

[bence-takacs]

Connected issues: https://code.google.com/p/mybatis/issues/list?can=1&cursor=mybatis%3A53&q=pl/sql

[emacarron]

Can this work for you?
#241

[bence-takacs]

Seems to be FINE
We would like to check it.

When will that change be available at mybatis-migrations?

Currently the fix is present in master (3.3.0-SNAPSHOT), and migrations uses 3.2.8

[bence-takacs]

It's working using:
mybatis-migrations [master]
using: mybatis 3.3.0-SNAPSHOT

The working code:

ALTER TABLE TAB1
  ADD CONSTRAINT PK_TAB1
PRIMARY KEY (TAB1_ID);

DELIMITER /
CREATE OR REPLACE PROCEDURE UPDATE_TAB1 AS
BEGIN
  UPDATE TAB1 SET NUMBER_FIELD = NUMBER_FIELD + 1;
  COMMIT;
END;
/
DELIMITER ;

One more suggestion is: putting the word 'DELIMITER' in SQL comment block like:

ALTER TABLE TAB1
  ADD CONSTRAINT PK_TAB1
PRIMARY KEY (TAB1_ID);

--DELIMITER /
CREATE OR REPLACE PROCEDURE UPDATE_TAB1 AS
BEGIN
  UPDATE TAB1 SET NUMBER_FIELD = NUMBER_FIELD + 1;
  COMMIT;
END;
/
--DELIMITER ;

or:
-- //@delimiter /
-- //@delimiter ;

[bence-takacs]

The current solution is working but not production-ready: we should remain between the frames of SQL-standard.
Please change the format of deimiter declaration to something starting with SQL comment.

Like one of:
-- DELIMITER
-- //DELIMITER
-- //@delimiter

[emacarron]

Good catch. Would you mind changing it yourself?

[bence-takacs]

Sure, I can do it.

Which solution do you prefer? (-- or --// or --//@)
Where should I commit (push) my changes?

[bence-takacs]

Pull request created:
#363

[bence-takacs]

there is a fix to be compliant with mybatis migrations

[bence-takacs]

I'm wondering when this is going to be released and downstreamed into migrations and maven-migrations-plugin...