CODEWITHSUNDEEP
oracle-databaseoracle11gsqlplus
bitoolean
bitoolean

Reputation: 131

Avoid repeating the same WITH in multiple SPOOLs in SQL*Plus script without using CREATE

I'm spooling from a SQL*Plus script many SELECTs to separate CSVs, most of which JOIN on the same SELECT, which I've placed as a WITH before the SELECT in each SPOOL in order to at least be able to find&replace easily.

Now I'm trying to rid the script of the code repetition and to optimize for performance by maybe storing the results of the repeated SELECT and reusing them in each SPOOL.

I've tried using variables to store the results of the SELECT that is repeated as WITH, but I can't seem to be able to mix SQL*Plus SPOOLs with PL/SQL code (using SPOOL inside DECLARE BEGIN/END block fails with only the number "28" being output on the command-line). My first thought was something like temporary tables were needed, but there's only read-only access, so no DDL like CREATE/ALTER, etc.

SET MARKUP CSV ON
-- omitting delimiter and other SQL*Plus options set

SPOOL C:\test\1.csv

WITH Y AS ( SELECT * FROM Z )
SELECT * FROM X JOIN Y ON Y.W = X.W
;
SPOOL OFF

SPOOL C:\test\2.csv

WITH Y AS ( SELECT * FROM Z )
SELECT * FROM W JOIN Y ON Y.X = W.X
;
SPOOL OFF

-- many other different spools joining on the same WITH SELECT

exit;

This code works fine, but I'd prefer to not have to repeat the same WITH as it kills performance and makes the code a chore to maintain / work on. However, the main concern here is the repetition of code and not necessarily the performance hit, at least for now, although it is an ugly thing to have the server repeat the same queries over and over.

Obviously, I'm not literate in Oracle / PL/SQL.

Upvotes: 0

Views: 438

Answers (2)

Popeye
Popeye

Reputation: 35900

To give an answer to the following:

but I can't seem to be able to mix SQL*Plus SPOOLs with PL/SQL code (using SPOOL inside DECLARE BEGIN/END block fails with only the number "28" being output on the command-line)

You can use the Spool and PL/SQL block using the following technique:

-- test.sql file

spool tejash_1.txt

declare
 cursor c_emp is 
 select seq, req from table1;

begin
  for r_emp in c_emp loop
    dbms_output.put_line(r_emp.seq || ' ' || r_emp.req);
  end loop;

end;
/

spool off

-- end of test.sql file


-- Command to execute in SQL*Plus
SQL> set serverout on
SQL> SET echo off
SQL> SET feedback off
SQL> SET term off
SQL> @test.sql

-- output in tejash_1.txt file
001 X1 
002 X1 
003 X1 
004 X1 
-- End of output in tejash_1.txt file

Cheers!!

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

It would help if you (or someone else) could create a view as

create view v_y
as
SELECT * FROM Z       --> this is your "Y" CTE

I presume that SELECT * FROM Z is more complex than this simple statement so - if nothing else - your code would look prettier. See if a DBA or such could create a view for you.

It also means that you'd avoid CTE completely and join tables to the v_y view directly.

... fails with only the number "28" being output on the command-line

It means that you should terminate that PL/SQL block with a slash. Or, simply enter slash now and press enter:

28  /            --> now press enter

Anyway, SPOOL - which is a SQL*Plus command - won't work in PL/SQL procedures. But, you could create a file using UTL_FILE package. It isn't as simple as SPOOL as you should be granted to use that package, DBA should create a directory (Oracle object) which points to a directory on database server's hard disk, grant you privileges to access it (the directory). That is far from being impossible, but you can't do it yourself.

Perhaps you'd rather ask them to create that view for you, eh?

Upvotes: 1

Related Questions