# Oracle end delimiter issue **URL:** \ **Category:** General Discussion\ **Created:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730 "2010-08-17T13:59:00Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![alexis\_lb](https://avatars.discourse-cdn.com/v4/letter/a/eada6e/32.png) [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)\ **Post date:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730/1 "2010-08-17T13:59:00Z")
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: 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:                                             DROP PROCEDURE TEST\_PROC             The problem appears when you have comments like this: 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 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:                                                 DROP PROCEDURE TEST\_PROC                                         But it also fails, although I think this one should work fine… Any help is more than welcome, Thanks, Alexis. ---
**Author:** ![alexis\_lb](https://avatars.discourse-cdn.com/v4/letter/a/eada6e/32.png) [@alexis\_lb](https://forum.liquibase.org/u/alexis_lb)\ **Post date:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730/2 "2010-08-17T13:59:00Z")
Something to add: This was working before and now with revision 1718 it is failling: 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;                                                 DROP PROCEDURE TEST\_PROC             ---
**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\ **Post date:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730/3 "2010-08-17T13:59:00Z")
I’m going to be on on vacation until Aug 29th, so I’ll get back to you after then.  I’ll look into it when I get back, sorry for the delay.  Nathan ---
**Author:** ![rowens\_lb](https://avatars.discourse-cdn.com/v4/letter/r/cab0a1/32.png) [@rowens\_lb](https://forum.liquibase.org/u/rowens_lb)\ **Post date:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730/4 "2010-08-17T13:59:00Z")
I’m experiencing a very similar problem with Oracle and stored procedures. Is there any news on this topic? Ryan ---
**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\ **Post date:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730/5 "2010-08-17T13:59:00Z")
I’ve made some changes and bug fixes with the end delimiters, the end delimiter is actually a regular expression, so you should be able to use endDelimiter="^/$" and it will only split on /'s that are on their own line. Nathan ---
**Author:** ![rowens\_lb](https://avatars.discourse-cdn.com/v4/letter/r/cab0a1/32.png) [@rowens\_lb](https://forum.liquibase.org/u/rowens_lb)\ **Post date:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730/6 "2010-08-17T13:59:00Z")
After pulling down the source code and stepping through it with a debugger, I came to that understanding (about using regex). The expression you suggest doesn’t work for some reason, but borrowing from the code in StringUtils.java, I came up this this combination which seems to work perfectly: Thanks for your assistance and thanks for a great tool. Ryan ---
**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\ **Post date:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730/7 "2010-08-17T13:59:00Z")
Glad you found the right delimiter, and that liquibase has been helping you out. Nathan ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730/8 "2010-08-17T13:59:00Z")
This is just what I was looking for ![](http://forum.liquibase.org/ze/images/smile.gif), the delimiter for PL/SQL, incredible how something so basic in Oracle was so hard to find ![](http://forum.liquibase.org/ze/images/surprised.gif). It works great when executing from Liquibase, but when trying to get SQL (executeSQL), as a DBA would, to execute the code in production environment, it shows the text below. The forward-slash (/) is replaced by the regex in endDelimiter, is that the expected behavior?   Please help, the tool is great, but I’m lost here   1. INFO 23/06/11 12:47 PM:liquibase: Successfully acquired change log lock INFO 23/06/11 12:47 PM:liquibase: Reading from DATABASECHANGELOG INFO 23/06/11 12:47 PM:liquibase: Reading from DATABASECHANGELOG INFO 23/06/11 12:47 PM:liquibase: ChangeSet test.xml::6::admin ran successfully in 0ms INFO 23/06/11 12:47 PM:liquibase: Successfully released change log lock – \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* – Update Database Script – \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* – Change Log: test.xml – Ran at: 23/06/11 12:47 PM – Against: [PORTA@jdbc:oracle:thin:@130.2.17.15:1521:AXISD](mailto:PORTA@jdbc:oracle:thin:@130.2.17.15:1521:AXISD) – Liquibase version: 2.0.1 – \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* _- – Lock Database – Changeset test.xml::6::admin::(Checksum: 3:b68a4e5f5289107c5db491db643a449a) CREATE OR REPLACE Package PORTA.a Is /holax/    --Function b(Edad Number) Return Varchar2;    Function b(Telefono Number) Return Number;    Function b(Nombre Varchar2) Return Number;_ 2. _End; /\s | /\s_$ 3. INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXEC TYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES (‘admin’, ‘’, SYSTI MESTAMP, ‘SQL From File’, ‘EXECUTED’, ‘test.xml’, ‘6’, ‘2.0.1’, ‘3:b68a4e5f52891 07c5db491db643a449a’, 5356); 4. – Release Database Lock INFO 23/06/11 12:47 PM:liquibase: Successfully released change log lock ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forum.liquibase.org/u/Guest)\ **Post date:** [August 17, 2010, 1:59pm UTC](https://forum.liquibase.org/t/oracle-end-delimiter-issue/730/9 "2010-08-17T13:59:00Z")
  I forgot to show my database changelog file, and I didn’t stress out what the error in the generated code was   Generated code (endDelimiter in xml file is added instead of the actual delimiter from the sql file which is a forward-slash (/) 1. CREATE OR REPLACE Package PORTA.a Is /_holax_/    --Function b(Edad Number) Return Varchar2;    Function b(Telefono Number) Return Number;    Function b(Nombre Varchar2) Return Number; 2. End; **/\s\* | /\s\*$** 3. INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXEC TYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES (‘admin’, ‘’, SYSTI MESTAMP, ‘SQL From File’, ‘EXECUTED’, ‘test.xml’, ‘6’, ‘2.0.1’, ‘3:b68a4e5f52891 07c5db491db643a449a’, 5356); My XML file 1. \ - \< databaseChangeLog xmlns=_“[http://www.liquibase.org/xml/ns/dbchangelog](http://www.liquibase.org/xml/ns/dbchangelog)”_ - xmlns:xsi=_“[http://www.w3.org/2001/XMLSchema-instance](http://www.w3.org/2001/XMLSchema-instance)”_ - xsi:schemaLocation=_“[http://www.liquibase.org/xml/ns/dbchangelog](http://www.liquibase.org/xml/ns/dbchangelog) [http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd](http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd)”_\> -   - \ - \ - -   - \