oracle anonymous block , unix shell script

digs developer
digs developer used Ask the Experts™
on
How to run oracle anonymous block  through unix shell script.

I have created one anonymous block i.e.
declare
v number:=9;
begin
insert into a values (v);
end ;
/

I have created one sql file blk.sql  for above statement.

then i hve created 1 shell script

sqlplus us/Pwd@abc  @blk.sql

Then
I have excueted the above shell script but data is not inserted into table.

Is it right way to call the sql block using @
Please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
Have you tried a redirect?

sqlplus us/Pwd@abc < blk.sql

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Try a commit inside the block.
Steve WalesSenior Database Administrator

Commented:
As written when you finish the SQLPlus statement, you don't automatically exit sqlplus.

Are you querying the table from the same session or another session ?

If the same session - the data is there.  I tested using exactly your statements as provided, and they work.

From another session - you won't see anything, because you haven't committed the change to the database.  Issue a commit from the sqlplus prompt, or quit to commit the change to the database.

To make it happen automatically, put "quit" in a line by itself in blk.sql after the "/"
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I want to run 3 different anonymous blocks of files .
sqlplus us/Pwd@abc  @blk_1.sql
@blk_2.sql
@blk_3.sql

How can I run it? Please suggest.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Either 3 sqlplus calls or create blk_all.sql with the contents:
@blk_1.sql
@blk_2.sql
@blk_3.sql
exit;

Then:
sqlplus us/Pwd@abc  @blk_all
Senior Oracle DBA
Commented:
Another option:

sqlplus us/Pwd@abc << EOF
@blk_1.sql
@blk_2.sql
@blk_3.sql
exit;
EOF

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today