Solved

create and execute sql script in sqlplus

Posted on 2011-03-01
6
559 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

831 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