Does anyone know what the size limitation for files is using xml db functionality.
What would be the advanatge performance wise using xml db as opposed to building your own parser.
I have posted a few questions recently using xml db. I rewrote a stored procedure using xml db the old stored procedure used instr, substr etc to manually parse and xml file.
I was wondering why he did this? Hence the question size limitation or performance limitation.
I will be using my stored procedure to load 64 million records each file will be no greater then 1.5 mb
Any other suggestions to improve performance in stored procedure would be appreciated.
Thanks.
/* Formatted on 2006/07/10 16:10 (Formatter Plus v4.8.5) */
CREATE OR REPLACE PACKAGE BODY parse_xml_pkg_7
AS
xmldata XMLTYPE;
DATA VARCHAR2 (2000);
evh_data VARCHAR2 (2000);
evd_data VARCHAR2 (2000);
concat_data_parent_str VARCHAR2 (2000);
pin_detail_delim VARCHAR2 (10) := '||';
pin_delim VARCHAR2 (10) := '~';
counter NUMBER := 0;
idx_counter NUMBER := 0;
count_occurence NUMBER := 0;
FUNCTION parse_str_fnc (pin_str IN VARCHAR2, pin_delim IN VARCHAR2)
RETURN str_tab
AS
l_str VARCHAR2 (2000) DEFAULT pin_str || pin_delim;
l_i NUMBER;
l2_i NUMBER;
l_tab str_tab := str_tab ();
l_len_of_pin_delim NUMBER;
BEGIN
--get the last val back add extra delim, without last val is not returend
--l_str := l_str||pin_delim;
l_len_of_pin_delim := LENGTH (pin_delim) - 1;
LOOP
l_i := INSTR (l_str, pin_delim);
l2_i := INSTR (l_str, '||');
EXIT WHEN (NVL (l_i, 0) = 0);
l_tab.EXTEND;
l_tab (l_tab.COUNT) := SUBSTR (l_str, 1, l_i - 1);
--Add length of pin_delimiter to account correct value retrieved in substr function.
l_str := SUBSTR (l_str, l_i + 1 + l_len_of_pin_delim);
END LOOP;
RETURN l_tab;
END parse_str_fnc;
PROCEDURE parse_xml_import_prc (
pin_price_event_msg IN CLOB,
pout_error OUT NUMBER,
pout_error_msg OUT VARCHAR,
pout_time_stamp OUT VARCHAR,
pout_source_id OUT VARCHAR
)
AS
BEGIN
-- convert clob into xmltype instance
xmldata := XMLTYPE.createxml (pin_price_event_msg);
BEGIN
SELECT EXTRACTVALUE (xmldata, '/PDATA/HDR') "HDR"
INTO DATA
FROM DUAL
WHERE EXISTSNODE (xmldata, '/PDATA/HDR') = 1;
DBMS_OUTPUT.put_line ('HDR Data: ' || DATA);
hdr_val_tab := parse_str_fnc (DATA, pin_delim);
pout_time_stamp := hdr_val_tab (1);
pout_source_id := hdr_val_tab (2);
FOR j IN 1 .. hdr_val_tab.COUNT
LOOP
DBMS_OUTPUT.put_line ('--HDR Value: ' || hdr_val_tab (j));
END LOOP;
SELECT COUNT (1)
INTO idx_counter
FROM TABLE (XMLSEQUENCE (EXTRACT (xmldata, '/PDATA/EVH')));
<<outer_0>>
FOR v IN 1 .. idx_counter
LOOP
SELECT EXTRACTVALUE (xmldata, '/PDATA/EVH[' || v || ']') "EVH"
INTO evh_data
FROM DUAL
WHERE EXISTSNODE (xmldata, '/PDATA/EVH') = 1;
SELECT EXTRACTVALUE (xmldata, '/PDATA/EVD[' || v || ']') "EVD"
INTO evd_data
FROM DUAL
WHERE EXISTSNODE (xmldata, '/PDATA/EVD') = 1;
evd_parent_val_tab := parse_str_fnc (evd_data, pin_detail_delim);
<<pin_detail_delim_1>>
FOR j IN 1 .. evd_parent_val_tab.COUNT
LOOP
concat_data_parent_str := evh_data || pin_delim || evd_data;
--DBMS_OUTPUT.put_line ('PRE-parsed concat_data_parent_str : ' || concat_data_parent_str);
concat_data_parent_str := evh_data || pin_delim || evd_parent_val_tab (j);
--DBMS_OUTPUT.put_line ('parsed concat_data_parent_str : ##' || j || '## ' || concat_data_parent_str);
concat_data_multi_parent_t
ab := parse_str_fnc (concat_data_parent_str, pin_delim);
upsert_rec_prc (concat_data_multi_parent_
tab, pout_error, concat_data_parent_str);
END LOOP pin_detail_delim_1;
END LOOP outer_0;
EXCEPTION
WHEN OTHERS
THEN
pout_error := 1;
pout_error_msg := display_error_fnc (concat_data_multi_parent_
tab, 'XML PARSE', SQLCODE, SQLERRM);
END;
END parse_xml_import_prc;
PROCEDURE upsert_rec_prc (raw_rec_tab IN str_tab, pout_error OUT NUMBER, pout_error_msg IN OUT VARCHAR2)
IS
BEGIN
g_concat_evh_evd_row := pout_error_msg;
BEGIN
BEGIN
g_evh_transaction_type := raw_rec_tab (1);
g_evh_store_id := raw_rec_tab (2);
g_evh_event_id := raw_rec_tab (3);
g_evh_event_type := raw_rec_tab (4);
g_evh_event_sign_flag := raw_rec_tab (5);
g_evh_event_override_flag := raw_rec_tab (6);
g_evh_event_begin_time_sta
mp := TO_DATE (raw_rec_tab (7), g_date_fmt);
g_evh_event_end_time_stamp
:= TO_DATE (raw_rec_tab (8), g_date_fmt);
g_evh_event_name := raw_rec_tab (9);
g_evd_item_id := raw_rec_tab (11);
g_evd_item_price := raw_rec_tab (12);
g_evd_item_status_code := raw_rec_tab (13);
g_evd_sale_type := raw_rec_tab (14);
EXCEPTION
WHEN OTHERS
THEN
pout_error := 1;
pout_error_msg := display_error_fnc (raw_rec_tab, 'OTHERS', SQLCODE, SQLERRM);
NULL;
END;
IF g_evh_transaction_type NOT IN ('18', '32', '33')
THEN
INSERT INTO ps_itm_loc_prc
(id_str_rt,
id_evt_prc_chn,
ty_prc_chn,
fl_sgn_evt_prc,
fl_ovrd,
dc_eff_cur_sls,
dc_exp_cur_sls,
nm_evt_prc_chn,
id_itm,
rp_cur_sls,
sc_itm_loc_prc,
ty_sls,
de_rsn_evt_prc_chn,
sc_eff,
sc_exp,
sc_upd,
ts_crt_rcrd,
ts_mdf_rcrd
)
VALUES (g_evh_store_id,
g_evh_event_id,
g_evh_event_type,
g_evh_event_sign_flag,
g_evh_event_override_flag,
g_evh_event_begin_time_sta
mp,
g_evh_event_end_time_stamp
,
g_evh_event_name,
g_evd_item_id,
g_evd_item_price,
g_evd_item_status_code,
g_evd_sale_type,
NULL,
0,
0,
g_ins_flag,
c_sysdate,
c_sysdate
);
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
pout_error := 1;
pout_error_msg := display_error_fnc (raw_rec_tab, 'DUP_VAL_ON_INDEX', SQLCODE, SQLERRM);
UPDATE ps_itm_loc_prc
SET ty_prc_chn = g_evh_event_type,
fl_sgn_evt_prc = g_evh_event_sign_flag,
fl_ovrd = g_evh_event_override_flag,
dc_eff_cur_sls = g_evh_event_begin_time_sta
mp,
dc_exp_cur_sls = g_evh_event_end_time_stamp
,
nm_evt_prc_chn = g_evh_event_name,
id_itm = g_evd_item_id,
rp_cur_sls = g_evd_item_price,
sc_itm_loc_prc = g_evd_item_status_code,
ty_sls = g_evd_sale_type,
ts_mdf_rcrd = c_sysdate,
sc_upd = g_upd_flag
WHERE ps_itm_loc_prc.id_itm = g_evd_item_id
AND ps_itm_loc_prc.id_str_rt = g_evh_store_id
AND ps_itm_loc_prc.id_evt_prc_
chn = g_evh_event_id;
END;
BEGIN
IF g_evh_transaction_type IN ('18', '32')
THEN
UPDATE ps_itm_loc_prc
SET ts_mdf_rcrd = c_sysdate,
sc_upd = g_upd_flag,
dc_exp_cur_sls = g_evh_event_end_time_stamp
WHERE ps_itm_loc_prc.id_itm = g_evd_item_id
AND ps_itm_loc_prc.id_str_rt = g_evh_store_id
AND ps_itm_loc_prc.id_evt_prc_
chn = g_evh_event_id;
IF SQL%ROWCOUNT = 0
THEN
RAISE NO_DATA_FOUND;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
pout_error := 1;
pout_error_msg := display_error_fnc (raw_rec_tab, 'NO_DATA_FOUND', SQLCODE, SQLERRM);
END;
BEGIN
IF g_evh_transaction_type IN ('33')
THEN
UPDATE ps_itm_loc_prc
SET ts_mdf_rcrd = c_sysdate,
sc_upd = g_upd_flag,
dc_exp_cur_sls = g_evh_event_end_time_stamp
WHERE ps_itm_loc_prc.id_str_rt = g_evh_store_id AND ps_itm_loc_prc.id_evt_prc_
chn = g_evh_event_id;
IF SQL%ROWCOUNT = 0
THEN
RAISE NO_DATA_FOUND;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
pout_error := 1;
pout_error_msg := display_error_fnc (raw_rec_tab, 'NO_DATA_FOUND', SQLCODE, SQLERRM);
END;
END upsert_rec_prc;
FUNCTION display_error_fnc (
raw_rec_tab IN str_tab,
pin_msg_type IN VARCHAR2,
pin_error_sqlcode IN VARCHAR2,
pin_error_sqlerm IN VARCHAR2
)
RETURN VARCHAR2
IS
l_error_msg VARCHAR2 (2000);
BEGIN
/*
DBMS_OUTPUT.put_line (' Transaction Type,' || raw_rec_tab (1));
DBMS_OUTPUT.put_line (' Store ID ID_STR_RT,' || raw_rec_tab (2));
DBMS_OUTPUT.put_line (' Event ID ID_EVT_PRC_CHN,' || raw_rec_tab (3));
DBMS_OUTPUT.put_line (' Event Type TY_PRC_CHN,' || raw_rec_tab (4));
DBMS_OUTPUT.put_line (' Event Sign Flag FL_SGN_EVT_PRC,' || raw_rec_tab (5));
DBMS_OUTPUT.put_line (' Override Indicator FL_OVRD,' || raw_rec_tab (6));
DBMS_OUTPUT.put_line (' Event Begin Timestamp DC_EFF_CUR_SLS,' || raw_rec_tab (7));
DBMS_OUTPUT.put_line (' Event End Timestamp DC_EXP_CUR_SLS,' || raw_rec_tab (8));
DBMS_OUTPUT.put_line (' Event Name NM_EVT_PRC_CHN,' || raw_rec_tab (9));
--DBMS_OUTPUT.put_line ('Event ID ID_EVT_PRC_CHN (10),' || raw_rec_tab (10));
DBMS_OUTPUT.put_line (' Item ID ID_ITM,' || raw_rec_tab (11));
DBMS_OUTPUT.put_line (' Item Price RP_CUR_SLS,' || raw_rec_tab (12));
DBMS_OUTPUT.put_line (' Item Status Code SC_ITM_LOC_PRC,' || raw_rec_tab (13));
DBMS_OUTPUT.put_line (' Sale type TY_SLS,' || raw_rec_tab (14)); */
l_error_msg :=
pin_msg_type
|| ' '
|| ' slqcode: '
|| pin_error_sqlcode
|| 'sqlerm: '
|| pin_error_sqlerm
|| ' Transaction Type=>'
|| raw_rec_tab (1)
|| CHR (10)
|| ' ATTRIBUTE COLUMN_NAME VALUE'
|| CHR (10)
|| ' Store ID ID_STR_RT=>'
|| raw_rec_tab (2)
|| CHR (10)
|| ' Event ID ID_EVT_PRC_CHN=>'
|| raw_rec_tab (3)
|| CHR (10)
|| ' Event Type TY_PRC_CHN=>'
|| raw_rec_tab (4)
|| CHR (10)
|| ' Event Sign Flag FL_SGN_EVT_PRC=>'
|| raw_rec_tab (5)
|| CHR (10)
|| ' Override Indicator FL_OVRD=>'
|| raw_rec_tab (6)
|| CHR (10)
|| ' Event Begin Timestamp DC_EFF_CUR_SLS=>'
|| raw_rec_tab (7)
|| CHR (10)
|| ' Event End Timestamp DC_EXP_CUR_SLS=>'
|| raw_rec_tab (8)
|| CHR (10)
|| ' Event Name NM_EVT_PRC_CHN=>'
|| raw_rec_tab (9)
|| CHR (10)
|| ' Item Id ID_ITM,||'
|| raw_rec_tab (11)
|| CHR (10)
|| ' Item Price RP_CUR_SLS,||'
|| raw_rec_tab (12)
|| CHR (10)
|| ' Item Status Code SC_ITM_LOC_PRC=>'
|| raw_rec_tab (13)
|| CHR (10)
|| ' Sale type TY_SLS=>'
|| raw_rec_tab (14)
|| CHR (10);
DBMS_OUTPUT.put_line (l_error_msg);
RETURN l_error_msg;
END display_error_fnc;
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
END parse_xml_pkg_7;
/
