?
Solved

sql*plus syntax

Posted on 2014-02-12
10
Medium Priority
?
585 Views
Last Modified: 2014-02-12
I have a list of SQL commands (to query an oracle database) in a text file, I would like to run them in batch via sql*plus. I want each query to write the results to an individual HTML or CSV file, how can this be done?

How do I need to seperate the queries in the text file , and how can I add a filename for the output to csv, html?

Advice most welcome.
0
Comment
Question by:pma111
[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
  • 5
  • 4
10 Comments
 
LVL 21

Accepted Solution

by:
Amitkumar Panchal earned 1000 total points
ID: 39852917
you can use spool command for each of the queries in your batch. You will need to specify path to the file where you want to spool the result and then need to execute the query.


Refer more on spooling at http://www.dba-oracle.com/t_sqlplus_spool.htm

Then write an sql as given template.

spool <path_to_file_001.csv>
sql_001;

spool <path_to_file_002.csv>
sql_002;

spool <path_to_file_003.csv>
sql_003;

and

so on

Open in new window


Hope this helps you.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 1000 total points
ID: 39852918
prepare the queries accordingly to generate the csv output and put it as shown in the into a sql script which you can then execute.

test.sql
---------------
spool emp.csv
select name || ',' || deptno from emp;
spool off

then from sql prompt, execute it
SQL>@test.sql

Thanks,
0
 
LVL 3

Author Comment

by:pma111
ID: 39852923
thanks
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 3

Author Comment

by:pma111
ID: 39852936
is "spool off" required after every query, as the top example didnt include spool off?

also what does || ',' || represent?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39852942
|| ',' || - is to concate output from 2 columns using a comma delimiter - just an example for you to understand.

not for every query but if you have 5 queries and if the output needed to go to 5 different csv files then it can be used.

or even without using it, if you start another spool command with a different file name then also it works.

Thanks,
0
 
LVL 3

Author Comment

by:pma111
ID: 39852948
what represents the end of a query so it moves on to the next, is that the ; character?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39852958
yes
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39852962
for sql queries like select, update, delete , insert etc.. you need ; at the end

But for sql*plus commands like spool, column , heading etc you do not need ; at the end and they take new line as the end

Thanks
0
 
LVL 3

Author Comment

by:pma111
ID: 39852970
thats interesting, so you need to write your sql in one block then, as some people add line breaks after the various clauses (i.e. select, from, where)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39852996
yeah.. you can leave a blank line as well for the sql query but it needs to have ; at th end
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

771 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