Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

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

Comment on Pass perl array to SQL oracle
Download Code
Re: Pass perl array to SQL oracle
by roboticus (Canon) on Jan 19, 2012 at 11:18 UTC

    arunshankar.c:

    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.

    ...roboticus

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://948723]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2014-11-23 20:17 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (134 votes), past polls