how to create a batch of sql in oracle?

Posted on 2011-04-28
Last Modified: 2012-05-11
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,


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

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

Question by:wasabi3689
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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?
    LVL 7

    Accepted Solution


    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.


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now