Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Pass perl array to SQL oracle

by arunshankar.c (Initiate)
on Jan 19, 2012 at 09:08 UTC ( #948723=perlquestion: print w/replies, xml ) Need Help??
arunshankar.c has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

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 type varcharlist is table of varchar2(20); v_array varcharlist; 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;

Please suggest if you can think of any way.

Thanks, Arun

Replies are listed 'Best First'.
Re: Pass perl array to SQL oracle
by roboticus (Chancellor) on Jan 19, 2012 at 11:18 UTC


    Several things:

    • If you're planning to use the sqlplus tool as your interface, you're not going to be able to pass arrays in and out of Oracle. You'll have to do a bunch of text munging to create the appropriate scripts and parse the returned data files. That's just too much work. I expect that you'll have better luck if you try using DBI and DBD::Oracle (or one of the other modules that let you work with Oracle databases.)
    • I've not directly used the array types in Oracle, nor have I played with nested tables. While perusing the documentation for DBD::Oracle, there's a section named "SYS.DBMS_SQL datatypes" where they show some examples that you may be able to adapt.
    • If you try DBI and DBD::Oracle, be sure to read section "Placeholders and Bind Values" in the DBI documentation. Using placeholders is pretty easy, and it's the best way to interface with the database: You avoid various quoting issues, security issues, etc.


    When your only tool is a hammer, all problems look like your thumb.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://948723]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (2)
As of 2018-02-24 10:59 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (310 votes). Check out past polls.