CODEWITHSUNDEEP
oracle-databasesqlplusspool
Dom
Dom

Reputation: 1

oracle sqlplus spool to multiple files by row number

Wanting to output results into multiple csv files and cutoff at a specific row number for each file. Tried the following but dont want to create multiple queries.

set colsep ,   
DEFINE SPOOL_BASE_NAME = "spool"
DEFINE PAGE_SIZE = 10
DEFINE PAGE_NO = 1
DEFINE SPOOL_EXT=".csv"
SPOOL &SPOOL_BASE_NAME.&PAGE_NO.&SPOOL_EXT

SELECT *
      FROM   (SELECT a.*,rownum rnum
FROM   (SELECT username, account_status,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM dba_users order by username) a
             WHERE  rownum <= &PAGE_SIZE * &PAGE_NO)
      WHERE  rnum >= &PAGE_SIZE * (&PAGE_NO - 1) + 1;
SPOOL OFF

DEFINE PAGE_NO = 2
SPOOL &SPOOL_BASE_NAME.&PAGE_NO
SELECT *
      FROM   (SELECT a.*,rownum rnum
FROM   (SELECT username, account_status,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM dba_users order by username) a
             WHERE  rownum <= &PAGE_SIZE * &PAGE_NO)
      WHERE  rnum >= &PAGE_SIZE * (&PAGE_NO - 1) + 1;
SPOOL OFF

Upvotes: 0

Views: 3921

Answers (1)

Patrick Marchand
Patrick Marchand

Reputation: 3445

SQLPlus doesn't have the concept of loops but you can create a SQLPlus script that accepts a parameter so you could have a script with the core SQL, say extract.sql, that looks like the following:

DEFINE SPOOL_BASE_NAME = "spool"
DEFINE PAGE_SIZE = 10
DEFINE PAGE_NO = &1
DEFINE SPOOL_EXT=".csv"
SPOOL &SPOOL_BASE_NAME.&PAGE_NO.&SPOOL_EXT
SET TERM OFF

SELECT *
      FROM   (SELECT a.*,rownum rnum
FROM   (SELECT username, account_status,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM dba_users order by username) a
             WHERE  rownum <= &PAGE_SIZE * &PAGE_NO)
      WHERE  rnum >= &PAGE_SIZE * (&PAGE_NO - 1) + 1;
SPOOL OFF
EXIT

Note that when running scripts from the command line, SET TERM OFF will prevent the data from scrolling by on screen and include an EXIT at the end so SQLPlus doens't hang around waiting for more input.

With this you could call it from the command line as:

sqlplus username/password@database @extract.sql <pagenum>

That is:

sqlplus username/password@database @extract.sql 1
sqlplus username/password@database @extract.sql 2
sqlplus username/password@database @extract.sql 3
...

The bonus of this is that you can run a bunch of them in parallel in different terminal/cmd windows so one doesn't have wait for the other to finish.

With an external loop to generate the numbers and call the script as above,

[Disclaimer: No guarantees this will generate consistent results if used on a volatile source.]

Upvotes: 1

Related Questions