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

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.
0
digs developer
Asked:
digs developer
1 Solution
 
nemws1Commented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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