Oracle SQL*Plus -- Implementation of "FOR EACH" construct

Problem

SQLPlus lacks execution control constructs such as loops, for example, particularly "FOR EACH" loops

Solution

The below script is the implementation of "FOR EACH" construct found in most modern procedural languages.

The script accepts two mandatory parameters:

1. comma delimited list of values. the length of the parameter should not exceed 4000 bytes. the number of values cannot exceed 20.

2. a script to be executed against each value provided with the first parameter

Usage:

@@for_each "<val1>, <val2>, ... ,<valN>" "<script.sql>"

Save the script in the working directory:

set echo off
set term off
set null ""
set verify off

whenever sqlerror exit sql.sqlcode

spool null.sql append
rem a placeholer for a null operation
spool off

col 1 new_val 1
col 2 new_val 2
select '' as "1", '' as "2" from dual where 1=2;

def p_input_params="&1"
def p_script="&2"
def l_this_value=""
def l_this_script="for_each"
def l_quote_pos=0

col l_this_value   new_val l_this_value
col p_input_params new_val p_input_params
col l_this_script  new_val l_this_script
col l_quote_pos    new_val l_quote_pos
col p_script       new_val p_script

begin
   if '&p_script' is null then
      raise_application_error(-20001, 'Script parameter cannot be empty');
   end if;
end;
/

set term off
select trim('&p_input_params') p_input_params from dual;
select trim(trailing ',' from '&p_input_params') p_input_params from dual;
select nvl(instr('&p_input_params', ','), 0) l_quote_pos from dual;

select
   substr('&p_input_params', 1, case &l_quote_pos when 0 then 4000 else &l_quote_pos-1 end) l_this_value,
   substr('&p_input_params', &l_quote_pos+1, case &l_quote_pos when 0 then &l_quote_pos else 4000 end) p_input_params
from dual;

select case when '&l_this_value' is null then 'null' else '&p_script' end p_script from dual;

set term off
prompt &p_script &l_this_value
@@&p_script &l_this_value

set echo off
set null ""
set verify off
set term off
select case when '&p_input_params' is NULL then 'null' else 'for_each' end l_this_script from dual;

@@&l_this_script "&p_input_params"