SQLPlus - spooling to multiple files from PL/SQL blocks

Posted by FrustratedWithFormsDesigner on Stack Overflow See other posts from Stack Overflow or by FrustratedWithFormsDesigner
Published on 2010-04-13T13:56:30Z Indexed on 2010/04/13 14:03 UTC
Read the original article Hit count: 692

Filed under:

sqlplus

|

plsql

|

spool

|

Oracle

I have a query that returns a lot of data into a CSV file. So much, in fact, that Excel can't open it - there are too many rows. Is there a way to control spool to spool to a new file everytime 65000 rows have been processed? Ideally, I'd like to have my output in files named in sequence, such as large_data_1.csv, large_data_2.csv, large_data_3.csv, etc...

I could use dbms_output in a PL/SQL block to control how many rows are output, but then how would I switch files, as spool does not seem to be accessible from PL/SQL blocks?

(Oracle 10g)

© Stack Overflow or respective owner

Related posts about sqlplus

Starting SQLPlus in linux

as seen on Server Fault - Search for 'Server Fault'
How do you start sqlplus in linux? I know this is a service in windows. >>> More
SQLPlus - spooling to multiple files from PL/SQL blocks

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a query that returns a lot of data into a CSV file. So much, in fact, that Excel can't open it - there are too many rows. Is there a way to control spool to spool to a new file everytime 65000 rows have been processed? Ideally, I'd like to have my output in files named in sequence, such as… >>> More
Running pl/sql in Korn Shell(AIX)

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a file to execute in Ksh written by someone. It has a set of commands to execute in sqlplus. It starts with, sqlplus -s $UP <<- END followed by a set of ddl commands such as create,drop,etc., When I execute the file in the shell, I get the error in the starting line quoted above… >>> More
Oracle SQLPlus: How to display the output of a sqlplus command without having to first issue the spo

as seen on Stack Overflow - Search for 'Stack Overflow'
Is there a way to display the output of a sqlplus command without having to first issue the spool off command? I am spooling the results of a sqlplus session to a file while at the same time tailing the file. The reason for this is that for table with very long rows the format is easier to look… >>> More
Broker vs. SQLPLUS to manage Data Guard configuration

as seen on Server Fault - Search for 'Server Fault'
Hi all, Can anyone tell me the link to the comparison between using SQLPLUS and Data Guard Broker to manage data guard configuration? Advantages and disadvantages of those two command line interfaces? Thanks, Sarith >>> More

Related posts about plsql

Iterating over files in PLSQL

as seen on Stack Overflow - Search for 'Stack Overflow'
Using a PLSQL script, is it possible to iterate over binary files in a folder and insert them as a BLOB in Oracle 10g? Any examples would be appreciated. >>> More
plsql show query

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi all, I have a small problem using oracle pl sql. I have a sql file with some cursor, etc, and the treatement fail but with no details. I have an idea about the problem (a function with parameters) but I would like to see the parameter for each call, to be able to debug, to see exactly with wich… >>> More
Get 2 recoeds from PLSQL Cursor

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi Guys... my Question is short, I create a cursor to get some values from my table I want to get the current record of cursor (the fetched record) and the next record from the cursor without fetching it, cause I want to make a calculation over the current record and the next record. in traditional… >>> More
Get two records from PLSQL cursor

as seen on Stack Overflow - Search for 'Stack Overflow'
My question is short. I create a cursor to get some values from my table. I want to get the current record of cursor (the fetched record) and the next record from the cursor without fetching it, because I want to make a calculation over the current record and the next record. In traditional programming… >>> More
PLSQL: How to go about it

as seen on Stack Overflow - Search for 'Stack Overflow'
I searched for this topic on SO but I did not found any recommendations and so am asking it as I am new to PLSQL and want to understand it: What is best way to get started with PLSQL ? What are best online resources or books available out there for understand PLSQL which you would recommend ? Do… >>> More