Could someone please suggest if there is a way to pass an perl array(pass @v_array) to SQL as in below eg :
#!/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;
I get errors in above scenario.
Thanks
Arun
---------- Post updated at 04:38 PM ---------- Previous update was at 01:21 PM ----------
$
$
$ # 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
$
$