Pass perl array to SQL oracle


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Pass perl array to SQL oracle
# 1  
Old 01-19-2012
Pass perl array to SQL oracle

Hello,

Could someone please suggest if there is a way to pass an perl array(pass @v_array) to SQL as in below eg :

Code:
#!/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 ----------

Can anyone please help me on this ?
# 2  
Old 01-21-2012
Quote:
Originally Posted by arunshankar.c
...
Could someone please suggest if there is a way to pass an perl array(pass @v_array) to SQL as in below eg :

Code:
#!/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.
...
Code:
$
$
$ # 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
$
$

tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need to pass Oracle SQL output to Linux and back... Help!

Hi all, Hopefully you can help. This is what I'm trying to achieve: Obtain a list of usernames out of an Oracle Database Based on this list, link each username with an Oracle Internet Directory (OID) GUID Using the username and GUID perform a database update for all users Here are the... (7 Replies)
Discussion started by: exm
7 Replies

2. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

3. Programming

pass value from Oracle sql to Korn shell

Hi All , I am trying to pass a value from sqlplus to korn shell . There is a table tab1 in Oracle that has a column userdate. I need to pass the userdate to the korn shell . This is what I am doing . VALUE=`sqlplus -silent username/password << END set pagesize 0 feedback off verify off... (14 Replies)
Discussion started by: megha2525
14 Replies

4. Shell Programming and Scripting

perl- oracle sql query

Hi, I am new to perl.How to query oracle database with perl??? Thanks (1 Reply)
Discussion started by: tdev457
1 Replies

5. UNIX and Linux Applications

Free UNIX with ksh, perl, Oracle and SQL

Is there a UNIX with ksh, perl, Oracle an SQL to download for free? If not, how can I "build" one? (9 Replies)
Discussion started by: Sygonion
9 Replies

6. Shell Programming and Scripting

PERL : SQL array bind issue while grouping

Hi, I have an array of strings. Each string has 4 comma separated values. I am binding this array to a SQL where I am required to do an INSERT after grouping. The binding is done as : $insertADWSth->bind_param_array(1,A_CONSTANT_STRING);... (1 Reply)
Discussion started by: sinpeak
1 Replies

7. Shell Programming and Scripting

PERL : Bind 2D array to SQL

Hi, I am in the need of doing a bulk insert via : SQL - INSERT INTO <table> (SELECT..) OR PLSQL Block - FORALL i IN 1 .. count INSERT INTO <table>(arrayname(i)) I have a 2D array in my perl code which has the rows to be bulk inserted. Is there a way to bind the 2D array to the SQL... (4 Replies)
Discussion started by: sinpeak
4 Replies

8. Shell Programming and Scripting

Can we pass an array of strings from a Perl Program to a Shell Script?

Hi Folks, The subject is my question: Can we pass an array of strings from a Perl Program to a Shell Script? Please provide some sample code. Thanks ---------- Post updated at 11:52 PM ---------- Previous update was at 11:43 PM ---------- I got it. Its here:... (0 Replies)
Discussion started by: som.nitk
0 Replies

9. Shell Programming and Scripting

How to pass ksh array to oracle

Hi all.. Does anyone know have an example of passing the contents of a ksharray to oracle? basically I am looking to loop through the contents of a file and store each line into a bash ksh. Once i have this I can then pass the array into an oracle procedure that accepts an array as an... (1 Reply)
Discussion started by: kiranlalka
1 Replies

10. UNIX for Dummies Questions & Answers

how to pass values from oracle sql plus to unix shell script

how to pass values from oracle sql plus to unix shell script (2 Replies)
Discussion started by: trichyselva
2 Replies
Login or Register to Ask a Question

Featured Tech Videos