Batch statement in SQLPLUS 2

[ClaudiaFilliponi]

In PL/SQL I can do the following in a batch statement

1 declare v_num int := 0;
2 begin
3 select count(*) into v_num from test_frag where rownum < 100000;
4 update test_frag set big_column = big_column where rownum < 10;
5* end;
/

PL/SQL procedure successfully completed.

How can I do the same in SQLPLUS?

 

[SantaMufasa]

Claudia,

When you say, "I can do the following in a batch statement," what is the exact syntax you are using to execute the "batch statement"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Both statements make no sence to mee as select count(*) always returns exactly 1 row, thus you don't need limiting the result by rownum. The second statement produces nothing as you assign to column its own value.
So it's quite complex to get an idea of your question about batch statement, I see none.
But if you need to execute anonymous pl/sql block, just type it, put slash symbol after the last line and press Enter, exactly the same way you typed it here. Another way is to put that script to a file and then launch it using @full_file_name

Regards, Dima

http://seminihin.narod.ru

 

[ClaudiaFilliponi]

Thanks I just gave an exampple. I do not particularly worry about the SQL in each line. What I want to know if it is possible to have three lines of SQL statement in SQLPLUS (Not PL/SQL) by doing the following:

select ... from abc;
update ... set ... where ...;
select sysdate from dual;

Can you do this in SQPLUS or you have to use
begin
select ... from abc;
update ... set ... where ...;
select sysdate from dual;
end;

I suspect in SQLPLUS you cannot have multi transaction lines. Hope this is clearer

Thanks,

Claudia

 

[SantaMufasa]

Claudia,

All you need to do to make the commands execute sequentially is to 1) save them to a file 2) execute the file from the SQL*Plus prompt. Assuming you save the above three SQL commands to a file named Claudia.sql in the default directory of your SQL*Plus, you would then do the following (from SQL*Plus):

SQL> @Claudia

Does this satisfactorily answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ClaudiaFilliponi]

Thanks but that is not one batch. Because every line is executed separately. I gather the only way to do it will be in PL/SQL.
begin
select sysdate from dual;
update table a;
insert into table b;
select sysdate from dual;
end;

This will be one batch transaction. If one of the above DML fails, the whole batch will be aborted. Am I correct.

Thanks

 

[sem]

You may prefix your script with

whenever sqlerror exit rollback

A number of other options exists

Regards, Dima

http://seminihin.narod.ru

 

[ClaudiaFilliponi]

Thanks Dima.

My understanding is that a login to SQLPLUS or PL/SQL will be interpreted as an implicit start of transaction. You can put as many batch blocks in PL/SQL as you wish. They will not be commited unless you explicitely issue 'commit work' or 'rollback' However a DDL statement will be interpreted as a full transaction with commit at the end on its own. That is if I do in my PL/SQL the following:

begin
select sysdate from dual;
update table a;
insert into table b;
select sysdate from dual;
end;
create table abc (col1 int ...);

All the statements before 'create table' will be commited implicitely even if I do not issue 'commit' after end;. This is because A DDL is a full batch transaction on its own.

Am I correct?

Thanks

 

[sem]

Yes, you're right (unless you set AUTOCOMMIT ON) but this behaviour doesn't depend on whether you use pl/sql or plain sql: DDL statemens always commit transaction. Moreover, on error transaction is not rolled back by itself, but rather calling program makes it.

BTW which tool do you call PL/SQL?

Regards, Dima

http://seminihin.narod.ru

 

[ClaudiaFilliponi]

Thanks Again. Any tool which allows me to interact with Oracle in a batch format (allowing me to create SPs, functions and packages) as opposed to single line queries common with SQLPLUS I call it PL/SQL tool. The diffeence between two as I see it is that SQLPLUS is designed for single queries whereas PL/SQL is for application/multi batch/multi transaction build.

 

[sem]

Quite strange as sql*plus obviously allows creating SPs, functions and packages as well as executing them

Regards, Dima

http://seminihin.narod.ru

 

[ClaudiaFilliponi]

So what do you see as major differences between these two?

 

[ClaudiaFilliponi]

Actually I just differentiate between these two as SQLPLUS being a command line language and PLSQL as block based development tool. You cannot have block based coding in SQLPLUS.

 

[sem]

I can not understand which 2 you mean. I know sql*plus as a tool that allows both single query and pl/sql block execution. I don't know such tool as PL/SQL as this is a name of a language that has no GUI. I know PL/SQL Developer by Allaround Automations, but not sure you told about it. Again, sql*plus may be the only tool for any kind of sql and pl/sql development and testing, it's selfsufficient

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

...as I see it...SQLPLUS is designed for single queries whereas PL/SQL is for application/multi batch/multi transaction build.

Claudia, once you code the block with correct syntax, your PL/SQL block...

begin
 select sysdate from dual;
 update table a;
 insert into table b;
 select sysdate from dual;
end;

...does precisely the same thing as would a SQL script that has the "begin" and "end;" stripped off. As Dima mentions, there is no commit in either PL/SQL or SQL until you either explicitly issue a "COMMIT;" or you issue either a DDL or DCL SQL command.

Does this differ from your understanding?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ClaudiaFilliponi]

Santa,

IN SQLPLUS if I put all the below

select sysdate from dual;
update table a;
insert into table b;
select sysdate from dual;

in a file every line will be executed separetly and the result will be displayed per SQL statement. If one fails the other still goes through. However, if I try in a block with begin and end, it will be treated as one batch transaction. If any one of the SQL statements in a block returns an error (syntaxtual or otherwise), the grouped statements between begin and end will be invalidated in totality. Correct?

 

[SantaMufasa]

Claudia,

Under the circumstances you present, yes, SQL processes each statement independent of the others, while PL/SQL processess the statements as a unit.

You can, however, equalise the behaviours if, as Sem/Dima suggests, you use the SQL*Plus command at the beginning of your SQL script:

whenever sqlerror continue rollback

(or)

whenever sqlerror exit rollback

...depending upon whether you wish to continue your script or exit your script following an error.

The "whenever sqlerror..." specification causes the entire SQL transaction to rollback if Oracle throws an error at any time during the transaction (i.e., before COMMIT or ROLLBACK occurs explicitly).

Does this resolve your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ClaudiaFilliponi]

To summarize:

I connect to Oracle via SQLPLUS. I issue single line transaction via SQLPLUS. If I want to issue batched transactions (i.e. multiple SQL statement in one "BUSINESS UNIT" as you put it correctly), I will have to use PLSQL construct. That is the major difference between SQL*Plus and PL/SQL. The first one for single line queries and the latter for business transactions.

Claudia

 

[SantaMufasa]

No, Claudia...if you execute the following SQL script:

whenever sqlerror continue rollback
select sysdate from dual;
update table a...;
insert into table b...;
select sysdate from dual;
commit;

...it will have the same effect, risks, and protections as this PL/SQL block:

begin
 select sysdate into <some variable> from dual;
 update table a...;
 insert into table b...;
 select sysdate into <some variable> from dual;
 commit;
end;

(Note: ellipses ["..."] represent additional code that must exist for Oracle to successfully execute the code.)

In either case, if an error occurs during execution of any step in either the SQL script or the PL/SQL block, everything back to the beginning of the transaction is undone (rolls back). If there is no error, then the explicit "COMMIT;" takes effect.

Does this help explain things?


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Just to clarify: SQL - is a LANGUAGE, PL/SQL is a LANGUAGE, SQL*PLUS is NOT A LANGUAGE, it's a TOOL for writing and executing both SQL and PL/SQL code.

Regards, Dima

http://seminihin.narod.ru