[Liquibase-user] Oracle anonymous blocks and triggers

[Starr, Thomas]

Hi folks - I need to confirm whether one should be able to use either
update or updateSQL where <sqlFile=> in the change log references a file
containing anonymous plsql block (or a trigger).



Also whether update and updateSQL should both work with the same
anonymous block (or trigger) instead enclosed in <sql></sql> tags in the
change log.



This is the environment:

CLASSPATH gets the jdbc driver here:
/xxx/app/oracle/product/10.2.0/client/jdbc/lib/classes12.zip

JAVA_HOME is: /usr/java/jdk1.5.0_16



I find it works fine to use either update or updateSQL to create tables
/ foreign keys etc via Liquibase internals.



And it works fine to use updateSQL where <sqlFile=> in the change log
points to a file containing an anonymous block (or trigger).



But using update with the same change log so Liquibase will execute
directly fails.



I.e. - With this change log
(anon_block_external_file_change_log_errors.xml):

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9


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

<changeSet id="9" author="tom">



<sqlFile path="anon_block_external_file.sql"/>



</changeSet>

</databaseChangeLog>



Referencing this file (anon_block_external_file.sql):

DECLARE

v_num NUMBER;

v_exc EXCEPTION;

PRAGMA EXCEPTION_INIT (v_exc,-01418);

BEGIN

EXECUTE IMMEDIATE 'DROP INDEX blah';

EXCEPTION

WHEN v_exc THEN

DBMS_OUTPUT.PUT_LINE('Caught exception printed message');

END;

/



This fails with Migration Failed: Error executing SQL DECLARE:



java -jar /xxxxxx/liquibase/liquibase-1.9.0/liquibase-1.9.0.jar \


--defaultsFile=/xxxxxx/liquibase/liquibase-1.9.0/samples/playpen/liquiba
se.oracle.properties.xxx.xxxxxx \

--changeLogFile=anon_block_external_file_change_log_errors.xml \

--url=jdbc:oracle:thin:@xxxxxx.dev:1234:xxx \

--driver=oracle.jdbc.OracleDriver \

update



But this migrates reliably using the same change log and executing the
resulting output file is successful:



java -jar /xxxxxx/liquibase/liquibase-1.9.0/liquibase-1.9.0.jar \


--defaultsFile=/xxxxxx/liquibase/liquibase-1.9.0/samples/playpen/liquiba
se.oracle.properties.xxx.xxxxxx \

--changeLogFile=anon_block_external_file_change_log_errors.xml \

--url=jdbc:oracle:thin:@xxxxxx:1234:xxx \

updateSQL > anon_block_external_file_change_log_errors.sql





On the second question, using either update or updateSQL with this
change log having the same anonymous block (or trigger) in <sql></sql>
fails:



<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9


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

<changeSet id="7" author="tom">

<sql>

DECLARE

v_num NUMBER;

v_exc EXCEPTION;

PRAGMA EXCEPTION_INIT (v_exc,-01418);

BEGIN

EXECUTE IMMEDIATE 'DROP INDEX blah';

EXCEPTION

WHEN v_exc THEN

DBMS_OUTPUT.PUT_LINE('Caught exception printed message');

END;

</sql>

</changeSet>

</databaseChangeLog>



Thanks in advance for any thoughts or clues!!



Tom

[Damian Gołda]

Hi
I suppose the reason s that sql and sqlFile split whole text to many
statements using ";" as delimiter.

So try:
- set splitStatements attribute to false, or
- use createProcedure element (yes, I used createProcedure for plsql
functions and packages).

[Starr, Thomas]

Thanks Damian!!

Adding splitStatements="false" as you suggested works both when executing an anonymous block using <sql> and when creating a trigger using <sqlFile>.

Cheers,
Tom

-----Original Message-----
From: Damian Gołda [mailto:***@gmail.com]
Sent: Friday, January 30, 2009 1:38 PM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] Oracle anonymous blocks and triggers

Hi
I suppose the reason s that sql and sqlFile split whole text to many
statements using ";" as delimiter.

So try:
- set splitStatements attribute to false, or
- use createProcedure element (yes, I used createProcedure for plsql
functions and packages).

[Starr, Thomas]

If <sqlFile> references a file that uses the Oracle @ syntax to invoke another file should that work?

Using <sqlFile> repeatedly works fine but am trying to reuse existing files.

Thanks!!

-----Original Message-----
From: Starr, Thomas
Sent: Monday, February 02, 2009 2:51 PM
To: 'liquibase-***@lists.sourceforge.net'
Subject: RE: [Liquibase-user] Oracle anonymous blocks and triggers

Thanks Damian!!

Adding splitStatements="false" as you suggested works both when executing an anonymous block using <sql> and when creating a trigger using <sqlFile>.

Cheers,
Tom

-----Original Message-----
From: Damian Gołda [mailto:***@gmail.com]
Sent: Friday, January 30, 2009 1:38 PM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] Oracle anonymous blocks and triggers

Hi
I suppose the reason s that sql and sqlFile split whole text to many
statements using ";" as delimiter.

So try:
- set splitStatements attribute to false, or
- use createProcedure element (yes, I used createProcedure for plsql
functions and packages).

[Paul Keeble]

No it wont work because that isn't valid SQL in any way. It is in fact a sort of pragma to the oracle command line to allow it to import the file in that place and run it.

Paul


----- Original Message ----
From: "Starr, Thomas" <***@cobaltgroup.com>
To: liquibase-***@lists.sourceforge.net
Sent: Tuesday, 3 February, 2009 17:18:45
Subject: [Liquibase-user] Using Oracle @

If <sqlFile> references a file that uses the Oracle @ syntax to invoke another file should that work?

Using <sqlFile> repeatedly works fine but am trying to reuse existing files.

Thanks!!

-----Original Message-----
From: Starr, Thomas
Sent: Monday, February 02, 2009 2:51 PM
To: 'liquibase-***@lists.sourceforge.net'
Subject: RE: [Liquibase-user] Oracle anonymous blocks and triggers

Thanks Damian!!

Adding splitStatements="false" as you suggested works both when executing an anonymous block using <sql> and when creating a trigger using <sqlFile>.

Cheers,
Tom

-----Original Message-----
From: Damian Gołda [mailto:***@gmail.com]
Sent: Friday, January 30, 2009 1:38 PM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] Oracle anonymous blocks and triggers

Hi
I suppose the reason s that sql and sqlFile split whole text to many
statements using ";" as delimiter.

So try:
- set splitStatements attribute to false, or
- use createProcedure element (yes, I used createProcedure for plsql
functions and packages).
--
Damian

Hi folks - I need to confirm whether one should be able to use either update
or updateSQL where <sqlFile=> in the change log references a file containing
anonymous plsql block (or a trigger).
Also whether update and updateSQL should both work with the same anonymous
block (or trigger) instead enclosed in <sql></sql> tags in the change log.

------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Liquibase-user mailing list
Liquibase-***@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/liquibase-user

[Voxland, Nathan]

If you are looking to reuse files that you could load with the oracle loader, you could use the executeShellCommand tag to make a call to the oracle loader and have it run as it used to. That may or may not be of help to you depending on your setup and what you are trying to accomplish.

Nathan

-----Original Message-----
From: Paul Keeble [mailto:***@yahoo.co.uk]
Sent: Tuesday, February 03, 2009 3:42 PM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] Using Oracle @

No it wont work because that isn't valid SQL in any way. It is in fact a sort of pragma to the oracle command line to allow it to import the file in that place and run it.

Paul


----- Original Message ----
From: "Starr, Thomas" <***@cobaltgroup.com>
To: liquibase-***@lists.sourceforge.net
Sent: Tuesday, 3 February, 2009 17:18:45
Subject: [Liquibase-user] Using Oracle @

If <sqlFile> references a file that uses the Oracle @ syntax to invoke another file should that work?

Using <sqlFile> repeatedly works fine but am trying to reuse existing files.

Thanks!!

-----Original Message-----
From: Starr, Thomas
Sent: Monday, February 02, 2009 2:51 PM
To: 'liquibase-***@lists.sourceforge.net'
Subject: RE: [Liquibase-user] Oracle anonymous blocks and triggers

Thanks Damian!!

Adding splitStatements="false" as you suggested works both when executing an anonymous block using <sql> and when creating a trigger using <sqlFile>.

Cheers,
Tom

-----Original Message-----
From: Damian Gołda [mailto:***@gmail.com]
Sent: Friday, January 30, 2009 1:38 PM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] Oracle anonymous blocks and triggers

Hi
I suppose the reason s that sql and sqlFile split whole text to many
statements using ";" as delimiter.

So try:
- set splitStatements attribute to false, or
- use createProcedure element (yes, I used createProcedure for plsql
functions and packages).

--
Damian

Hi folks - I need to confirm whether one should be able to use either update
or updateSQL where <sqlFile=> in the change log references a file containing
anonymous plsql block (or a trigger).
Also whether update and updateSQL should both work with the same anonymous
block (or trigger) instead enclosed in <sql></sql> tags in the change log.

------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Liquibase-user mailing list
Liquibase-***@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/liquibase-user

------------------------------------------------------------------------------
Create and Deploy Rich Internet Apps outside the browser with Adobe(R)AIR(TM)
software. With Adobe AIR, Ajax developers can use existing skills and code to
build responsive, highly engaging applications that combine the power of local
resources and data with the reach of the web. Download the Adobe AIR SDK and
Ajax docs to start building applications today-http://p.sf.net/sfu/adobe-com
_______________________________________________
Liquibase-user mailing list
Liquibase-***@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/liquibase-user





------------------------------------------------------------------------------
Create and Deploy Rich Internet Apps outside the browser with Adobe(R)AIR(TM)
software. With Adobe AIR, Ajax developers can use existing skills and code to
build responsive, highly engaging applications that combine the power of local
resources and data with the reach of the web. Download the Adobe AIR SDK and
Ajax docs to start building applications today-http://p.sf.net/sfu/adobe-com
_______________________________________________
Liquibase-user mailing list
Liquibase-***@lists.sourceforge.net
https://lists.sourceforge.n

[Starr, Thomas]

Thanks Nathan and Paul for the level-set on (not) using @!!

Actually I now sqlFile working great for a table, indexes, a trigger, a
view, an mv and a package so who needs it :)

But how to see DBMS_OUTPUT.PUTL_LINE output?

It doesn't even show up with logLevel="finest"

Thanks again!!

--Tom

[Paul Keeble]

Alas another SQLPlus specific command. The DBMS actually has no way to provide logging output to JDBC, its just not specified in the drivers (Either the standard drivers or using the Oracle specific output). I've tried to get this working in the last month or two for one of my clients and I gave up, its just impossible to get hold of it.

The only workaround I have used to achieve a similar thing is to create a logging table, insert the output into that table and then select (then delete) it. Its an ugly workaround but it gets you the necessary critical information.

In my opinion (helf strongly from a position of ignorance on your situation) using logging is a crutch of the old way of migrating databases where you need to see the output to confirm it worked. Liquibase fails and rolls back if it doesn't work and gives you an ugly error. Its a different approach for sure but a reliable one.

Paul K


----- Original Message ----
From: "Starr, Thomas" <***@cobaltgroup.com>
To: liquibase-***@lists.sourceforge.net
Sent: Tuesday, 3 February, 2009 22:52:44
Subject: [Liquibase-user] DBMS_OUTPUT??

Thanks Nathan and Paul for the level-set on (not) using @!!

Actually I now sqlFile working great for a table, indexes, a trigger, a
view, an mv and a package so who needs it :)

But how to see DBMS_OUTPUT.PUTL_LINE output?

It doesn't even show up with logLevel="finest"

Thanks again!!

--Tom