In Cold Fusion I have a large array of struct I'm trying to pass into ORACLE
10g using stored procedures. I am hitting a performance wall inside ORACLE
(probably self inflicted).
I don't know of a way to transfer a complex array directly to ORACLE -- if
there is, I'd love to hear it.
So what I've done is translate the array into a CLOB of delimited values where
each array element is separated by a semicolon and each property value by a
comma like:
Prop1,prop2,prop3;Prop1,prop2,prop3;Prop1,prop2,prop3;Prop1,prop2,prop3;
I can then pass the CLOB into ORACLE where I wrote a little parsing procedure
that pulls the data elements out into a Global Temporary table which I then can
merge into other data tables. The issue seems to be on the ORACLE end. It is
taking too long to parse when the array gets large (10k rows takes about 4
mins).
What I need is either a different method to move the array into ORACLE or to
improve my parsing routine (see attached). Ideas?
Warren Koch
314-323-2298
PROCEDURE get_items
IS
/* This routine extracts the item values from the input string
into a global temporary table RENT_IMPORT_ITEMS
It uses two delimiters:
; to separate items
, to separate values
*/
instring CLOB;
data_string VARCHAR2(2000);
data_item VARCHAR2(100);
data_type VARCHAR2(100);
data_rev VARCHAR2(100);
data_sht VARCHAR2(100);
data_location INTEGER;
BEGIN
instring := UPPER(TRIM(in_list_items)) || ';';
data_location := INSTR(instring, ';');
EXECUTE IMMEDIATE 'TRUNCATE TABLE RENT_IMPORT_ITEMS ';
WHILE data_location > 0
LOOP
data_string := SUBSTR(instring, 1, data_location - 1);
data_string := REPLACE(data_string || ',,,,', ',', ' ,');
BEGIN
SELECT SUBSTR(TRIM(REGEXP_SUBSTR(data_string, '[^,]+', 1, 1)),
1, 35)
INTO data_item
FROM DUAL;
SELECT SUBSTR(TRIM(REGEXP_SUBSTR(data_string, '[^,]+', 1, 2)),
1, 5)
INTO data_type
FROM DUAL;
SELECT SUBSTR(TRIM(REGEXP_SUBSTR(data_string, '[^,]+', 1, 3)),
1, 4)
INTO data_rev
FROM DUAL;
SELECT SUBSTR(TRIM(REGEXP_SUBSTR(data_string, '[^,]+', 1, 4)),
1, 20)
INTO data_sht
FROM DUAL;
INSERT INTO rent_import_items
(item, item_type, item_rev, item_sht
)
VALUES (data_item, data_type, data_rev, data_sht
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
instring := SUBSTR(instring, data_location + 1);
data_location := INSTR(instring, ';');
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334807
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
