We help IT Professionals succeed at work.

oracle anonymous block , unix shell script

1,965 Views
Last Modified: 2014-08-18
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

Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
Have you tried a redirect?

sqlplus us/Pwd@abc < blk.sql

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

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 "/"

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.