• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

how to create a batch of sql in oracle?

I have several select statements and two delete statements, I want to run as a batch, no need to run one by one.

Here is my batch,

begin

select * from A where ID=2;
select * from B where ID=2;
...
Delete from A where ID=2;
Delete from B where ID=2
End;

But, it doesn't work, what is wrong with it?

0
wasabi3689
Asked:
wasabi3689
1 Solution
 
slightwv (䄆 Netminder) Commented:
Begin and end signify a pl/sql block.

You cannot select in pl/sql without selecting into something.

You can remove the begin/end and run this as a sqlplus script.  What exactly are you wanting to do with the output of the select before the deletes?
0
 
PilouteCommented:
Hi,

If you only want to run all those sql commands from one single command, all you have to do is put them in a file and call that file from sqlplus.

To call a file from sqlplus, you use '@' like this :

---------

sqlplus / as sysdba
[...]
SQL>@/path/to /your/file.txt

---------

If your file is a '.sql' you can call it even without the extension (I don't know if this works also for windows though, but sure for any *nix) :

@/path/to /your/file

Only make sure your file is a text file.

---------

If you want to connect to sqlplus AND call the file from a single line (I don't know if this works also for windows though, but sure for any *nix) :

sqlplus "/ as sysdba" @file.sql

You put the right user/password in the connection string. And make sure you close the session : add an 'exit' command at the end of your file.

Cheers,
P
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now