This is my problem: I have a Stored Procedure which receives as parameter a List of Objects, this is my object
In Oracle to create a new instance of this kind of object, the default Contructor is given by definition of the object, for example in this case to create a new instance of CUST_ACCNT_MTN_PRE_ORDER, my contructor will be something like this
And this is my Collection/List/Table of Objects
My SP is something like
this (As you'll see I'm using the same objects I defined in that entry)
And my solution is something like this
The secret is in this line:
SELECT CUST_ACCNT_MTN_PRE_ORDER(CUST_ID, ACCT_NUM, NPA || NXX || TLN, -1, 'X', MTN_ORDER_TYPE, SYSDATE, NVL(EO_ALT_NPA||EO_ALT_NXX||EO_ALT_TLN,0))
BULK COLLECT INTO p_list
I'm creating for each row an object of type CUST_ACCNT_MTN_PRE_ORDER and then the ResultSet is set INTO p_list variable using
BULK COLLECT, which fetch all rows once
IF you want to see my Stored Proc, this is what I did
CREATE OR REPLACE PROCEDURE record_ship_temp_orders(
out_orders_processed OUT NUMBER
)
IS
my_cust_id CUST_ACCT_ORDER.cust_id%TYPE;
my_acct_num CUST_ACCT_ORDER.acct_num%TYPE;
my_pos_order_id CUST_ACCT_ORDER.pos_order_id%TYPE;
my_pos_pre_order_id CUST_ACCT_ORDER.pos_pre_order_id%TYPE;
my_pos_loc_cd CUST_ACCT_ORDER.pos_loc_cd%TYPE;
my_total_order_lines CUST_ACCT_ORDER.total_order_lines%TYPE;
my_ord_add_lines_cnt CUST_ACCT_ORDER.add_lines_cnt%TYPE;
my_ord_upg_lines_cnt CUST_ACCT_ORDER.upg_lines_cnt%TYPE;
p_list CUST_ACCNT_MTN_PRE_ORDER_LIST;
OUT_CIDB_ORDER_ID NUMBER;
--This is the main cursor
CURSOR cur_test_init_load
IS
SELECT CUST_ID_NO AS CUST_ID,
ACCT_NO AS ACCT_NUM,
EQ_ORD_NO AS POS_ORDER_ID,
NULL AS POS_PRE_ORDER_ID,
NETACE_LOC_ID AS POS_LOC_CD
FROM TEST_INIT_LOAD
GROUP BY CUST_ID_NO, ACCT_NO, EQ_ORD_NO, NULL, NETACE_LOC_ID
ORDER BY CUST_ID_NO, ACCT_NO, EQ_ORD_NO, NETACE_LOC_ID;
/******************************************************************************
NAME: record_ship_temp_orders
PURPOSE: Retrieve data loaded into TEST_INIT_LOAD to be processed by
Record_Pre_Order and then shipped by Ship_Pre_Order.
DATE: Oct 2010
Referenced by: To be executed internally
******************************************************************************/
BEGIN
DBMS_OUTPUT.put_line('Starting prcess '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
OPEN cur_test_init_load;
IF cur_test_init_load%NOTFOUND THEN
DBMS_OUTPUT.put_line('No data found on TEST_INIT_LOAD');
CLOSE cur_test_init_load;
raise_application_error(-20102, 'No data found on TEST_INIT_LOAD');
END IF;
out_orders_processed := cur_test_init_load%ROWCOUNT; --Before the first fetch, %ROWCOUNT returns 0
DBMS_OUTPUT.put_line('Start record '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
LOOP --Iterate cursor
FETCH cur_test_init_load INTO my_cust_id, my_acct_num, my_pos_order_id, my_pos_pre_order_id, my_pos_loc_cd;
IF out_orders_processed = 0 THEN
out_orders_processed := cur_test_init_load%ROWCOUNT; --Set a value to the OUT param
END IF;
EXIT WHEN cur_test_init_load%NOTFOUND;--Exits when process is completed
DBMS_OUTPUT.put_line('Processing '||my_cust_id||'-'||my_acct_num||', my_pos_order_id='||my_pos_order_id||', my_pos_pre_order_id='||my_pos_pre_order_id||', my_pos_loc_cd='||my_pos_loc_cd);
--Calculate total_order_lines, add_lines_cnt & upg_lines_cnt
SELECT count(1) AS TOTAL_ODER_LINES INTO my_total_order_lines
FROM TEST_INIT_LOAD
WHERE CUST_ID_NO = my_cust_id
AND ACCT_NO = my_acct_num
AND EQ_ORD_NO = my_pos_order_id;
SELECT count(1) AS ADD_LINES_CNT INTO my_ord_add_lines_cnt
FROM TEST_INIT_LOAD
WHERE CUST_ID_NO = my_cust_id
AND ACCT_NO = my_acct_num
AND EQ_ORD_NO = my_pos_order_id
AND ESN_CHG_RSN_CD IS NULL;
SELECT count(1) AS UPG_LINES_CNT INTO my_ord_upg_lines_cnt
FROM TEST_INIT_LOAD
WHERE CUST_ID_NO = my_cust_id
AND ACCT_NO = my_acct_num
AND EQ_ORD_NO = my_pos_order_id
AND ESN_CHG_RSN_CD IS NOT NULL;
DBMS_OUTPUT.put_line('my_total_order_lines '||my_total_order_lines||', my_ord_add_lines_cnt='||my_ord_add_lines_cnt||', my_ord_upg_lines_cnt='||my_ord_upg_lines_cnt);
--Generate List of Items to be inserted into CUST_ACCT_MTN_ORDER
SELECT CUST_ACCNT_MTN_PRE_ORDER(CUST_ID, ACCT_NUM, NPA || NXX || TLN, -1, 'X', MTN_ORDER_TYPE, SYSDATE, NVL(EO_ALT_NPA||EO_ALT_NXX||EO_ALT_TLN,0)) BULK COLLECT INTO p_list
FROM (
SELECT CUST_ID_NO AS CUST_ID,
ACCT_NO AS ACCT_NUM,
NPA , NXX , TLN,
NVL(ESN_CHG_RSN_CD,'A') AS MTN_ORDER_TYPE,
EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN
FROM TEST_INIT_LOAD
WHERE CUST_ID_NO = my_cust_id
AND ACCT_NO = my_acct_num
AND EQ_ORD_NO = my_pos_order_id
AND ESN_CHG_RSN_CD IS NULL
UNION
SELECT CUST_ID_NO AS CUST_ID,
ACCT_NO AS ACCT_NUM,
NPA , NXX , TLN,
'U' AS MTN_ORDER_TYPE,
EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN
FROM TEST_INIT_LOAD
WHERE CUST_ID_NO = my_cust_id
AND ACCT_NO = my_acct_num
AND EQ_ORD_NO = my_pos_order_id
AND ESN_CHG_RSN_CD IS NOT NULL
);
FOR i in 1 .. p_list.count
LOOP
DBMS_OUTPUT.put_line ('STRUCT '||i||'=> mtn='||p_list(i).mtn||', mtn_order_type='||p_list(i).mtn_order_type||', bdy_mtn='||p_list(i).bdy_mtn);
END LOOP;
--Call SP's
BEGIN
DBMS_OUTPUT.put_line('Calling Record_Pre_Order');
RECORD_PRE_ORDER ( my_cust_id, my_acct_num, my_pos_order_id, my_pos_pre_order_id, my_pos_loc_cd, my_total_order_lines, my_ord_add_lines_cnt, my_ord_upg_lines_cnt, 0, p_list, OUT_CIDB_ORDER_ID );
DBMS_OUTPUT.put_line('Calling Ship_Pre_Order');
SHIP_PRE_ORDER ( my_cust_id, my_acct_num, my_pos_order_id, my_pos_loc_cd, OUT_CIDB_ORDER_ID );
DBMS_OUTPUT.put_line('End record '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
DBMS_OUTPUT.put_line('-------');
p_list := NULL;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('***ERROR*** '||SQLERRM);
out_orders_processed := out_orders_processed - 1; --Decrease failed orders
END;
END LOOP;
DBMS_OUTPUT.put_line('Process completed');
DBMS_OUTPUT.put_line('End of process '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
CLOSE cur_test_init_load;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
ROLLBACK;
RAISE;
END;
/
