A new topic, 'Oracle end delimiter issue', has been made on a board you are
watching.
You can see it at
http://liquibase.org/forum/index.php?topic=651.new#new
The text of the topic is shown below:
Hi Nathan,
In the migration process I'm doing I have several stored procedures and
packages in sql files, they have the oracle end delimiter / and I'm trying to
use them with liquibase. But I found an issue with this delimiter and here is
an example. For this procedure:
Code:
CREATE OR REPLACE PROCEDURE TEST_PROC(key_value in number)
AS
TABLENAME VARCHAR2(40);
BEGIN
SELECT 'TEST_TABLE' INTO TABLENAME FROM DUAL;
DBMS_OUTPUT.PUT_LINE('DELETE FROM ' || tablename || ' WHERE key_value = ' ||
to_char(key_value) || '');
END;
/
If I use this changeset it works well:
Code:
<changeSet id="test-oracle-delimiter" author="alexis" runOnChange="true">
<sqlFile path="test_end_delimiter.sql" endDelimiter="/"
splitStatements="true" />
<rollback>
DROP PROCEDURE TEST_PROC
</rollback>
</changeSet>
The problem appears when you have comments like this:
Code:
CREATE OR REPLACE PROCEDURE TEST_PROC(key_value in number)
AS
TABLENAME VARCHAR2(40);
BEGIN
SELECT 'TEST_TABLE' INTO TABLENAME FROM DUAL;
/* My Comment */
DBMS_OUTPUT.PUT_LINE('DELETE FROM ' || tablename || ' WHERE key_value = ' ||
to_char(key_value) || '');
END;
/
I tries to run * My Comment * SQL statement.
Of course I can replace the /* .. */ by --. But when you use oracle hints we
have a problem because we need the /*, for example
Code:
CREATE OR REPLACE PROCEDURE TEST_PROC(key_value in number)
AS
TABLENAME VARCHAR2(40);
BEGIN
SELECT /*+ PARALLEL */ 'TEST_TABLE' INTO TABLENAME FROM DUAL;
DBMS_OUTPUT.PUT_LINE('DELETE FROM ' || tablename || ' WHERE key_value = ' ||
to_char(key_value) || '');
END;
/
I tried something else like:
Code:
<changeSet id="test-oracle-delimiter" author="alexis" runOnChange="true">
<sqlFile path="test_end_delimiter.sql" />
<rollback>
DROP PROCEDURE TEST_PROC
</rollback>
<modifySql>
<regExpReplace replace="^/$" with=""/>
</modifySql>
</changeSet>
But it also fails, although I think this one should work fine...
Any help is more than welcome,
Thanks,
Alexis.
Unsubscribe to new topics from this board by clicking here:
http://liquibase.org/forum/index.php?action=notifyboard;board=1.0
Regards,
The Liquibase Community Forum Team.------------------------------------------------------------------------------
This SF.net email is sponsored by
Make an app they can't live without
Enter the BlackBerry Developer Challenge
http://p.sf.net/sfu/RIM-dev2dev
_______________________________________________
Liquibase-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/liquibase-user
