Thanks Martin. I was inclining that same way but since I am going to send bunch of file contents [a very big file], I didn't think it would be enough to put it in a string for Oracle. For small files [with less data] I could proceed in that way but for large files it will fail. Looks like PERL doesn't have any thing to match Oracle's Associative array type.
Thanks, Srikanth (Sree) Turlapati Divison of Technology Services(DTS) (502) 564 4961 ________________________________ From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: Tuesday, May 19, 2009 1:34 PM To: Turlapati, Srikanth (Education Cabinet); dbi-users@perl.org Subject: RE: Arrays to Oracle Stored Procedures --you can pass in a huge string then in your Oracle Procedure --then split the pieces by known delimiter --Here is an Oracle Procedure which uses a Oracle array type to refer to HUGE varchar2 --Used when we want an array of a custom-defined type --in this case it is a huge string of length 100 CREATE TYPE array is TABLE OF VARCHAR2(100); --an Array of Table consisting of VARCHAR2(100) is created --Create a Proc which will read in the array and aggregate correct results to --BB_PRODUCT CREATE OR REPLACE PROCEDURE dyn_query2_sp (p_col IN ARRAY, --an array but its actually one gigantic String p_op IN ARRAY, --an array but its actually one gigantic String p_value IN ARRAY) --another array IS lv_query LONG; lv_status INTEGER; lv_cursor INTEGER; lv_key VARCHAR2(8) := ' WHERE '; lv_col1 NUMBER(2); lv_col2 VARCHAR2(25); lv_col3 NUMBER(6,2); lv_col4 NUMBER(5,1); --change this to be initialised to any delimiter you deem suitable DELIMITER CHAR(1) := ','; BEGIN --Open cursor lv_cursor := DBMS_SQL.OPEN_CURSOR; --Build first part of query lv_query := 'SELECT idProduct, productname, price, stock FROM bb_product'; --Loop through p_col array and add each criteria provided in the parameter arrays -- to complete the query -- reconstruct the query with pieces which are delimited --A,B,C... FOR i IN 1..p_col.COUNT LOOP IF pcol(i) = DELIMITER lv_query := lv_query || p_col(i); END LOOP; --ABC --Parse your statement DBMS_SQL.PARSE(lv_cursor, lv_query, DBMS_SQL.NATIVE); --Assuming the 3rd column vars are properly populated --Identify datatypes of data items returned to the cursor DBMS_SQL.DEFINE_COLUMN(lv_cursor, 1, lv_col1); DBMS_SQL.DEFINE_COLUMN(lv_cursor, 2, lv_col2, 25); DBMS_SQL.DEFINE_COLUMN(lv_cursor, 3, lv_col3); DBMS_SQL.DEFINE_COLUMN(lv_cursor, 4, lv_col4); --each placeholder (?) with a parameter for the WHERE clause values FOR i IN 1..p_col.COUNT LOOP DBMS_SQL.BIND_VARIABLE(lv_cursor, ':ph_value'||i, p_value(i)); END LOOP; --Execute the query lv_status := DBMS_SQL.EXECUTE(lv_cursor); --Fetch all rows returned and put values in PL/SQL variables -- Output data to screen to verify WHILE (DBMS_SQL.FETCH_ROWS(lv_cursor) > 0) LOOP DBMS_SQL.COLUMN_VALUE(lv_cursor, 1, lv_col1); DBMS_SQL.COLUMN_VALUE(lv_cursor, 2, lv_col2); DBMS_SQL.COLUMN_VALUE(lv_cursor, 3, lv_col3); DBMS_SQL.COLUMN_VALUE(lv_cursor, 4, lv_col4); --we will now have all the variables defined in lv_col1,lv_col2,lv_col3,lv_col4 DBMS_OUTPUT.PUT_LINE(lv_col1||' '||lv_col2||' '||lv_col3||' '||lv_col4); END LOOP; --Close cursor DBMS_SQL.CLOSE_CURSOR(lv_cursor); END; / With Warm Regards, Martin USCitizen(Contractor) ______________________________________________ Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Subject: Arrays to Oracle Stored Procedures > Date: Tue, 19 May 2009 11:48:52 -0400 > From: srikanth.turlap...@ky.gov > To: dbi-users@perl.org > > Is it possible with Latest DBI modules to pass an array [string or > integer] to Oracle stored procedure which accepts an array? > > Procedure specification is like this: > > > > Create or replace procedure p_getarray_perl(p_arr_value > dbms_sql.varchar2_table) -- String array. > > > > How do I pass an array to this from PERL? My main requirement is to read > a file and send this file contents as an array [since file is comma > separated, procedure can take care of stripping commas and inserting > into tables] > > Does any one have any working example of this? > > > > Thanks, > > Srikanth (Sree) Turlapati > > > > > ________________________________ Windows Live(tm): Keep your life in sync. Check it out. <http://windowslive.com/explore?ocid=TXT_TAGLM_BR_life_in_synch_052009>
