--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™: Keep your life in sync.
http://windowslive.com/explore?ocid=TXT_TAGLM_BR_life_in_synch_052009
