Run script sentences atomically

[rogerdielrton]

How can I run all sentences contained in a SQL script file atomically?
I have a file "myscript.sql" with several SQL sentences (insert, update, etc.) inside.
And I want that it will be executed atommically (all sentences inside a transaction).
I use the option:
--run=/path/to/file (run one script and then exit)
But, maybe it's needed to use any of the following options?:
--isolation=LEVEL (set the transaction isolation level)
--autoCommit=[true/false] (enable/disable automatic transaction commit)

[rogerdielrton]

I forgot that I'm working with an Oracle 11g database.

[snuyanzin]

By default it is used autocommit=true
You need to set it to false. However in that case your scripts should end with commit or rollback

[rogerdielrton]

Thank you, @snuyanzin, but it seems tha doesn't works.

Step 1: Create a test table an populate it with some values.

create table MyTable(
	id numeric(9,0) not null,
	field varchar(200) not null,
	constraint MyTable_PK primary key(id)
);
insert into MyTable(id, field) values (1, 'val1');
insert into MyTable(id, field) values (2, 'val2');
insert into MyTable(id, field) values (3, 'val3');

Step 2: In the next steps, I use the following command:

java \
-classpath "/opt/sqlline/sqlline-1.9.0-jar-with-dependencies.jar:/opt/jdbc-drivers/ojdbc6-11.2.0.3.jar" \
sqlline.SqlLine \
-u "jdbc:oracle:thin:@(...)" \
-n "myusername" \
-p "mypassword" \
-d "oracle.jdbc.driver.OracleDriver" \
--run="/tmp/myscript.sql" \
--isolation=TRANSACTION_SERIALIZABLE \
--autoCommit=false \
--showWarnings=true \
--showNestedErrs=true \
--strictJdbc=true \
--force=false

Step 3a: execute script and evaluate results

When /tmp/myscript.sql has this content:

update MyTable set field = 'new1' where id = 1;
update MyTable set field = null where id = 2;
update MyTable set field = 'new3' where id = 3;
commit;

then, this is the result:

Error: ORA-01407: cannot update ("MYSCHEME"."MYTABLE"."FIELD") to NULL (state=72000,code=1407)
java.sql.SQLException: ORA-01407: cannot update ("MYSCHEME"."MYTABLE"."FIELD") to NULL
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
	...
	at sqlline.SqlLine.main(SqlLine.java:206)
Aborting command set because "force" is false and command failed: "update MyTable set field = null where id = 2;"

and "field" is modified with "new1" value for "id=1" row!

Step 3b: execute script and evaluate results

When /tmp/myscript.sql has this content:

begin
	update MyTable set field = 'new1' where id = 1;
	update MyTable set field = null where id = 2;
	update MyTable set field = 'new3' where id = 3;
	commit;
exception
	when others then
		rollback;
end;

this is the result:

java.sql.SQLException: ORA-06550: line 2, column 47:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
	...
	at sqlline.SqlLine.main(SqlLine.java:206)
Aborting command set because "force" is false and command failed: "begin 
	update MyTable set field = 'new1' where id = 1;"

[julianhyde]

I think that begin ... end is the right solution. But then the problem is the semi-colons at the end of lines, because SQLLine does not know how to parse begin ... end. (SQLPlus has the same problem with PL/SQL blocks.)

[snuyanzin]

merged as 4a7f323