Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

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
[choroba]: ovedpo15 Hard to tell without context. You probalby used $dir outside of its scope, which is the body of the "unless".

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (5)
As of 2018-04-22 19:14 GMT
Find Nodes?
    Voting Booth?