[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

sql*plus syntax

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
pma111
Asked:
pma111
  • 5
  • 4
2 Solutions
 
Amitkumar PSr. ConsultantCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pma111Author Commented:
thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pma111Author Commented:
is "spool off" required after every query, as the top example didnt include spool off?

also what does || ',' || represent?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
|| ',' || - 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
 
pma111Author Commented:
what represents the end of a query so it moves on to the next, is that the ; character?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
yes
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pma111Author Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
yeah.. you can leave a blank line as well for the sql query but it needs to have ; at th end
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now