Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

create and execute sql script in sqlplus

Posted on 2011-03-01
6
Medium Priority
?
567 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

670 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