Shell Programming and Scripting

#!/usr/bin/perl

	@v_array = (1,2,4,5,6,8);
	$db_userid = 'ni71/ni711';
	$bufTPO = qx{ sqlplus -s << EOF
	$db_userid
		set verify off
		set feedback off
		set serveroutput on
		set linesize 32766
		DECLARE
		BEGIN
			for idx in v_array.first..v_array.last loop
				DBMS_OUTPUT.PUT_LINE('v_array'||v_array(idx));
			end loop;
		END;
		/
		exit
		EOF };
	print $bufTPO;

$
$
$ # show the contents of the Perl program
$
$ cat -n varrays.pl
     1  #!/usr/bin/perl -w
     2  use strict;
     3
     4  my @v_array   = ( 1, 2, 4, 5, 6, 8 );
     5  my $num_str   = join (",", @v_array);
     6  my $db_userid = 'test/test';
     7  my $bufTPO    = qx{
     8  sqlplus -s << EOF
     9  $db_userid
    10  set feedback off serveroutput on
    11  DECLARE
    12    TYPE nbr_array IS VARRAY(6) OF NUMBER;
    13    arr_num  nbr_array := nbr_array();
    14    v_nums   VARCHAR2(20) := '$num_str';
    15    v_chunk  VARCHAR2(20);
    16    n_indx   NUMBER := 1;
    17  BEGIN
    18    WHILE v_nums IS NOT NULL
    19    LOOP
    20      v_chunk := NVL (SUBSTR(v_nums, 1, INSTR(v_nums, ',') - 1), v_nums);
    21      arr_num.EXTEND(1);
    22      arr_num (n_indx) := v_chunk;
    23      n_indx := n_indx + 1;
    24      v_nums  := CASE v_chunk
    25                   WHEN v_nums THEN NULL
    26                   ELSE SUBSTR(v_nums, INSTR(v_nums, ',') + 1)
    27                 END;
    28    END LOOP;
    29    -- the varray "arr_num" has now been populated; use it as you wish...
    30    -- as an example, i'll loop through it
    31    FOR i IN arr_num.FIRST .. arr_num.LAST
    32    LOOP
    33      DBMS_OUTPUT.PUT_LINE ('Element = ' || arr_num(i));
    34    END LOOP;
    35  END;
    36  /
    37  exit
    38  EOF
    39  };
    40  print $bufTPO;
    41
$
$ # run the program
$
$ perl varrays.pl
Element = 1
Element = 2
Element = 4
Element = 5
Element = 6
Element = 8
$
$