Create Oracle packages

General Discussion
1

I found that if I do not add ‘/’ symbols but set splitStatements attribute in sqlfile tag all works fine.


  1.  
     

But it’s all ok if I split the package into two files.

Our developers use PL-SQL Developer IDE and it’s save all the packages (specifications and bodies) into single files like this:

  1. CREATE OR REPLACE PACKAGE ORAPACK IS
        PROCEDURE PROC1;
    END ORAPACK;
    /
    CREATE OR REPLACE PACKAGE BODY ORAPACK IS
       PROCEDURE PROC1 
        AS
        BEGIN
     

        DBMS_OUTPUT.PUT_LINE(‘SOMETHING USEFUL’);
        END;
    END ORAPACK;
    /

And in this case I cannot use splitStatements attibute because the package on Oracle server is created but it’s invalid.


What should I do to create the package from the single file with ‘/’ symbols as delimiters?

2

endDelimiter should be allowed on sqlFile. Are you referencing the correct XSD version in your XML root node? Also, what version of liquibase are you running?

Nathan

3

Aha!

I must be blind. I took xml changeset file from internet example and it has different namespaces.

I took namespaces from the site and everything works fine.


Thank you for your patience.

4

No problem, glad you got it working.


Nathan

5

Hello.


I’m trying to create an Oracle packages. I created two files - orapack.pcs as package specification and orapack.pcb as package body.


Very simple: 

  1. CREATE OR REPLACE PACKAGE ORAPACK IS
      PROCEDURE PROC1;
    END ORAPACK;

and

  1. CREATE OR REPLACE PACKAGE BODY ORAPACK IS

    PROCEDURE PROC1
    AS
    BEGIN
      DBMS_OUTPUT.PUT_LINE(‘SOMETHING USEFUL’);
    END;


  3. END ORAPACK;

Then here is my changeset:


 



But when I called the LiquiBase I get error as follows:

  1. Reason: liquibase.exception.DatabaseException: Error executing SQL END ORAPACK: ORA-00900:  invalid SQL statement.

I read here and about endDelimiter tag:

Delimiter to apply to the end of the statement. Defaults to ‘;’, may be set to ‘’.

I tried to set on the last lines in my script files the end delimiter as ‘/’ symbol:

  1. CREATE OR REPLACE PACKAGE ORAPACK IS
      PROCEDURE PROC1;
    END ORAPACK;

  2. /

and

  1. CREATE OR REPLACE PACKAGE BODY ORAPACK IS

    PROCEDURE PROC1
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(‘SOMETHING USEFUL’);
    END;

    END ORAPACK;

  2. /

And then I changed my changeset file as I found on forum - this link:


  1.  
     

But I get another error:

  1. Liquibase update Failed: Error parsing line 19 column 57 of : cvc-complex-type.3.2.2: Attribute 'endDelimiter' is not allowed to appear in element 'sqlFile'.

I cannot put package’s texts in xml section of changeset, only include them via sqlfile tag - the real packages are very complicated and a lot of developers work on them.


What should I do to make the LiquidBase to create y package?


Thank you.

6

I downloaded the last version from the site - liquibase-3.0.6-bin.zip.


This is the header of the changeset xml file:

  1. <databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.7"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.7

    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.7.xsd">