asked on

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.

ASKER CERTIFIED SOLUTION

Am P

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pau Lo

ASKER

thanks

Pau Lo

ASKER

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

also what does || ',' || represent?

Naveen Kumar

|| ',' || - 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,

Pau Lo

ASKER

what represents the end of a query so it moves on to the next, is that the ; character?

Naveen Kumar

yes

Naveen Kumar

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

Pau Lo

ASKER

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)

Naveen Kumar

yeah.. you can leave a blank line as well for the sql query but it needs to have ; at th end