Solved

create and execute sql script in sqlplus

Posted on 2011-03-01
6
565 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 77

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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35011324
sqlplus username/password @myscriptfile.txt
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 77

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

Stressed Out?

Watch some penguins on the livecam!

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…
It is possible to boost certain documents at query time in Solr. Query time boosting can be a powerful resource for finding the most relevant and "best" content. Of course the more information you index, the more fields you will be able to use for y…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

729 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