Solved

create and execute sql script in sqlplus

Posted on 2011-03-01
6
557 Views
Last Modified: 2012-05-11
attention oracle dba's, i've got a lob ball question that yall can smash out of the park.  I've got about 500 update statements aready formatted.  example:

UPDATE KUAF SET NAME="AG:SERF:IND75951:GST" WHERE NAME="SERF-IND75951"

I just need to put all of these update statements in a correctly formatted sql script, and execute it via sql plus (with a log file piped to the file system).  
0
Comment
Question by:bmsande
  • 3
  • 2
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35011275
add a semi-colon to the end.  change the double-quotes to single quotes.

at the top of the script add:
set echo on
spool mylog.log
UPDATE KUAF SET NAME='AG:SERF:IND75951:GST' WHERE NAME='SERF-IND75951';
...


at the end:
spool off
0
 

Author Comment

by:bmsande
ID: 35011309
excellent, now how do i call it from sql plus
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35011324
sqlplus username/password @myscriptfile.txt
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:bmsande
ID: 35011414
im not able to connect via cmd prompt.  But i can log into sql plus via the application.

i'm looking at a SQL> prompt.  how to do excute the script from here?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35011430
>>But i can log into sql plus via the application.
What application?  the commands I provided are sqlplus only.  Any other tool that allows you to execute a sql script might not work properly.


anyway, form sqlplus:

@myscriptfile.txt

or if in a different location:
@path\to\file\myscriptfile.txt
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 35014697
You can also use a batch script which can call your sqlplus ffrom oracle home and can run the sql scripts.
@xyz.sql

But as you said you cannot login through cmd ...then I have a small question.

Is you database on the system you are using or in remotely ?



0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by 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…
In Solr 4.0 it is possible to atomically (or partially) update individual fields in a document. This article will show the operations possible for atomic updating as well as setting up your Solr instance to be able to perform the actions. One major …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 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

12 Experts available now in Live!

Get 1:1 Help Now