oracle anonymous block , unix shell script

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.
digs developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
Have you tried a redirect?

sqlplus us/Pwd@abc < blk.sql

Open in new window

0
slightwv (䄆 Netminder) Commented:
Try a commit inside the block.
0
Steve WalesSenior Database AdministratorCommented:
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 "/"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

digs developerAuthor 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.
0
slightwv (䄆 Netminder) 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
0
johnsoneSenior Oracle DBACommented:
Another option:

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.