Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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 about the Monastery: (9)
As of 2014-12-18 12:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (51 votes), past polls