migration succeeds even if compiling procedures failed

[jmodi1982]

hi,

we are using MyBatis for applying DB changes and in 1 of the script there was a procedure which we were creating. MyBatis removed the comments while performing migraitons and that lead to procedure not compiling successfully. However Mybatis migraiton did not fail the execution but it provided Success status.

When we went to Database to check the procedure, it was broken.

Please can you confirm if MyBatis migrations can be used to Create/Replace Procedures,Functions, Packages in Oracle database.

Thanks

[harawata]

Please provide sample scripts to reproduce the problem.

[jmodi1982]

All the comments from the below script gets removed, I don't want the comments inside stored procedure to be removed. Please find below a sample script -

--*****************************************************************************
--Request                       : 1548
--Date                          : 26/04/2016
--Onsite Anchor                 : John March
--******************************************************************************/

CREATE OR REPLACE PROCEDURE CheckPassword
 --**********************************************************************
 -- TYPE               : Procedure
 -- Orginal Procedure  : inet_pwrup_qgpn_price
 -- NAME               : DP_inet_pwrup_qgpn_price
 -- INPUT Parameters   : None
 -- PURPOSE            :This procedure is used to populate the quasi generic
                         part price and other details
 -- ------ ---------- ---   ---------------------------------------------
    @username VARCHAR(20),
    @password varchar(20)
AS
BEGIN

SET NOCOUNT ON

IF EXISTS(SELECT * FROM usertable WHERE username = @username AND password = @password)
    SELECT 'true' AS UserExists
ELSE
    SELECT 'false' AS UserExists

END

[harawata]

I couldn't create that procedure even in SQL*Plus.
The below script worked on Oracle 11g (ojdbc7 driver version 12.1.0.2).

-- // First migration.
-- Migration SQL that makes the change goes here.

-- @DELIMITER |
CREATE OR REPLACE PROCEDURE myproc IS
/*
Comments
Comments
Comments
*/
BEGIN
    INSERT INTO person (id, name1) VALUES (999, 'John');
END;
|
-- @DELIMITER ;

-- See if the procedure has an error.
SELECT * FROM SYS.USER_ERRORS WHERE name='MYPROC' AND type='PROCEDURE';

-- //@UNDO
-- SQL to undo the change goes here.

DROP PROCEDURE myproc;

Some comments:

• Even when the procedure has compile error, Oracle driver creates the procedure and throws no exception. So, to Migrations, it's still SUCCESS.
  In the above script, I added a SELECT statement to check the error. It might be better than nothing.
• To leave comments in procedure, you have to use multi-line format /* .... */.
• The @DELIMITER syntax requires MyBatis 3.3.0 or later.

Here is the environment properties:

time_zone=GMT+0:00

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@192.168.57.101:1521:ORCL
username=xxx
password=xxx

send_full_script=false
delimiter=;
full_line_delimiter=false
auto_commit=false
changelog=CHANGELOG

I close this issue because creating procedure works :-)

[jmodi1982]

Thanks for the info, we also changed source code to use ## instead of -- as script comments as SQL. Everything looks good for now, will keep you posted if i come across any new issues

[harawata]

Good to know that ## works, too.
Thanks for the update!

[jmodi1982]

Hi,

I see a big issue and I need some immediate remediation based on your experience.

Even when the procedure has compile error, Oracle driver creates the procedure and throws no exception. So, to Migrations, it's still SUCCESS. Due to this the changelog table is updated and I cannot run the script again. So I have to create another script with correct procedure code and then again rerun it. Is there a way, using user_errors table we can fail the script so that the Changelog table is not updated with success. The developer can then go back correct the procedure code and rerun the same script file via mybatis. I agree the database however would be having errored stored procedure, but I want to avoid creating new files for every error in my pl/sql codes.

Looking forward for your guidance on this issue.

Thanks

[harawata]

Hi,

After the incorrectly succeeded 'up' command, you just need to execute the 'down' command.
Of course, you need to write DROP PROCEDURE in the 'UNDO' part (see my sample script above).
The 'down' command will remove the row from the changelog table as well.

I had expected that Oracle has some kind of configuration option for this behavior, but couldn't find it.
There seems to be some trick to achieve this in SQL*Plus, but it wouldn't work with JDBC (even if it did, I wouldn't recommend it).

Hope this helps,
Iwao

[jmodi1982]

Hi,

Please do let us know if you find any oracle configuration which can solve this issue. At least we should get the compilation error and the script should be exited since we usually run a large no of scripts and a non-compiled procedure could cause serious dB inconsistency

Also DBAY don't like the idea of going to sql+ to see if the stored procedure has thrown any compilation errors. They want to see it automatically after procedure is updated and exit the script if there is ano error.

Thanks.

[harawata]

I totally understand what you and your team want. Strange implementation indeed 😕

Anyway, I think I have found a solution to this problem.
I will open a new issue and let you know.

[harawata]

Well, I should just reopen this one.