Oracle Spool using Dynamic SQL

Question

I'm trying to pass a dynamic SQL statement to spool out to a text file using SQL*Plus, but I can't seem to execute the select statement I'm generating.

set linesize 10000 pagesize 0 embedded on
set heading off feedback off verify off trimspool on trimout on  termout off
set underline off

COLUMN gen_sql   NEW_VALUE gen_sql_
SELECT 'SELECT * FROM USER_TAB_COLS WHERE ROWNUM < 10' gen_sql_ FROM DUAL;

SPOOL 'myfilename.csv'

EXECUTE IMMEDIATE &gen_sql_

SPOOL OFF
/

I can't seem to use EXECUTE IMMEDIATE. Is there another way to execute the results of the select statement??

MORE DETAIL:

I have a set of views whose output I'd like to generate as formatted CSV files. I'm using dynamic SQL to create the formatting essentially. I generate something similar to:

SELECT TRIM(col1)||','||TRIM(col2)...FROM {myview}

I'm using the following to generate it this way:

COLUMN gen_sql   NEW_VALUE gen_sql_
SELECT 'SELECT ' || LISTAGG ('TRIM('||COLUMN_NAME||')', '||'',''|| ') 
     WITHIN GROUP (ORDER BY COLUMN_ID) gen_sql FROM...

Anyway, I'm able generate this SQL statement and store into a SQL*PLUS variable, but I just need to execute it after the SPOOL statement so that it will print to the file. I'm not sure how to execute it. Normal statements work, such as:

SPOOL 'myfilename.csv'
SELECT 1 col1 FROM DUAL;
SPOOL OFF
/

So, it would seem reasonable that I could something similar but executing the contents of my variable like:

SPOOL 'myfilename.csv'
--- RUN MY DYNAMIC SQL ----
SPOOL OFF
/

Boneist · Accepted Answer · 2015-09-11 16:24:07Z

2

I think this is what you're trying to achieve:

set linesize 10000 pagesize 0 embedded on
set heading off feedback off verify off trimspool on trimout on  termout off
set underline off

SPOOL myfilename.sql

SELECT 'SELECT table_name||'',''||column_name FROM USER_TAB_COLS WHERE ROWNUM < 10;' gen_sql_ FROM DUAL;

SPOOL OFF

spool results.csv

@myfilename.sql

SPOOL OFF

I.e. first you spool the results of your query into a file, and then once the spool is complete, you call the script you just created, spooling the results of that into a separate file.

edited Sep 11, 2015 at 16:24

answered Sep 11, 2015 at 16:15

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

codedawg82 Over a year ago

I need the results of the generated dynamic sql into a CSV file, though. Here, "myfilename.sql" will contain `SELECT * FROM USER_TAB_COLS WHERE ROWNUM < 10;' but the final output I want is the result of this in a CSV file.

Ditto · Accepted Answer · 2015-09-11 16:08:38Z

0

I believe you'll find EXECUTE IMMEDIATE is a PL/SQL command, so cannot be used directly in SQL or SQL*plus.

http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems017.htm

Also, SPOOL is a SQL*Plus command, and cannot be used in PL/SQL ..

So you have a problem there ;)

Can you step back a bit and explain what it is you're trying to do ? What is the requirements you have?

edited Sep 11, 2015 at 16:08

answered Sep 11, 2015 at 15:32

Ditto

3,3693 gold badges16 silver badges31 bronze badges

6 Comments

Shannon Severance Over a year ago

Spool is a SQL*Plus command, not SQL or PL/SQL. docs.oracle.com/cd/B19306_01/server.102/b14357/…

Ditto Over a year ago

yeah, sorry, meant that :) my bad, shorthand .. :) I'll fix that.

codedawg82 Over a year ago

Added more detail in the original post, so hopefully it'll help clear things up.

Ditto Over a year ago

I see your updates, and based on that, I'd recommend reading over Boneist's answer ... That's likely the direction you'll want to go. There are other "More complex" solutions .. which gain you absolutely nothing over what he's got .. ;)

Boneist Over a year ago

{cough}she{cough} *{;-)

|

RoshP · Accepted Answer · 2015-09-13 12:46:10Z

-1

First you need to construct a sql dynamically using spool. And then execute it.

set echo off
set verify off
set feedback off
set linesize 256
set pagesize 0
set term off
SPOOL 'myscript.sql'
SELECT * FROM USER_TAB_COLS WHERE ROWNUM < 10;
SPOOL OFF

set colsep ,
SPOOL myfile.csv
@myscript.sql
SPOOL OFF

edited Sep 13, 2015 at 12:46

answered Sep 11, 2015 at 15:44

RoshP

191 silver badge5 bronze badges

3 Comments

codedawg82 Over a year ago

How do I get the contents of @myscript.sql into a CSV file, though, because that's what I'm ultimately after.

RoshP Over a year ago

the use of set colsep , should do the trick. Check out the edited answer

miracle173 Over a year ago

That does not make sense. you should test your answer before you post it. What is the content of myscript.sql?

Collectives™ on Stack Overflow

Oracle Spool using Dynamic SQL

3 Answers 3

1 Comment

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related