Solved

create and execute sql script in sqlplus

Posted on 2011-03-01
6
561 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
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 

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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax Issue with SSIS module 26 103
Redirect 301 from one address  to another 5 25
Oracle 12c Default Isolation Level 17 41
Migration from sql server to oracle 5 24
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…
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 connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

856 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