How can I run a query with iteration or as a function in SQL Plus/SQL-PL?

Question

New to SQL Plus and a little confused on how iteration and functions work with it. Hoping someone could show me how it would be done with the following script:

set embedded on
set echo off
set colsep ,
set pagesize 0
set linesize 1000
set numwidth 10
set headsep off
spool <my_file_path>.csv;
select * from (select /*csv*/ row_number() over (order by t.objectid) as id, t.objectid as transformer_id, t.gps_x as lon, t.gps_y as lat, s.electricmeternumber, s.serviceindex, s.accountnumber,
case 
     when t.phasedesignation = 0 then 'unknown'
     when t.phasedesignation = 1 then 'C'
     when t.phasedesignation = 2 then 'B'
     when t.phasedesignation = 3 then 'BC'
     when t.phasedesignation = 4 then 'A'
     when t.phasedesignation = 5 then 'AC'
     when t.phasedesignation = 6 then 'AB'
     when t.phasedesignation = 7 then 'ABC'
     end as phase_designation
from mvtransformer t, vservicepoint_meterinfo s 
where t.objectid = s.transformerobjectid) where id > some value x and id <= some value x;
spool off; 

So what I want to do is repeat this script multiple times, varying x and the file path each time. What would be the best way to go about this in SQL Plus?

Thanks!

Answer 1

You could declare some substitution variables, place your query in a script of its own, and then call that script multiple times having populated the relevant variables.

E.g. something like:

Script to call multiple times:

spool &&filename..csv;

SELECT *
FROM   (SELECT /*csv*/
               row_number() over(ORDER BY t.objectid) AS id,
               t.objectid AS transformer_id,
               t.gps_x AS lon,
               t.gps_y AS lat,
               s.electricmeternumber,
               s.serviceindex,
               s.accountnumber,
               CASE
                 WHEN t.phasedesignation = 0 THEN 'unknown'
                 WHEN t.phasedesignation = 1 THEN 'C'
                 WHEN t.phasedesignation = 2 THEN 'B'
                 WHEN t.phasedesignation = 3 THEN 'BC'
                 WHEN t.phasedesignation = 4 THEN 'A'
                 WHEN t.phasedesignation = 5 THEN 'AC'
                 WHEN t.phasedesignation = 6 THEN 'AB'
                 WHEN t.phasedesignation = 7 THEN 'ABC'
               END  AS phase_designation
              FROM   mvtransformer           t,
                     vservicepoint_meterinfo s
              WHERE  t.objectid = s.transformerobjectid)
WHERE  id > &&val1
AND    id <= &&val2;
spool off; 

Calling Script

set embedded on
set echo off
set colsep ,
set pagesize 0
set linesize 1000
set numwidth 10
set headsep off

define val1=10
define val2=20
define filename=your_filename1

@script_to_loop_over

define val1=30
define val2=40
define filename=your_filename2

@script_to_loop_over

...

N.B. . is used to indicate the end of the substitution variable name in SQL*Plus. It looks like you're using SQL Developer/SQLCli, which may or may not treat this the same as SQL*Plus. If it doesn't you'd need to use spool &&filename.csv instead.