oracle 10 g
how do i assing values to l_outbound_rec. i cannot use bulk collect. since i have to calculate each and every field seperately and assign it to l_outbound_rec.
and then i can use l_outbound_rec in bulk inserts.
SET serveroutput on;
DECLARE
/*
CREATE OR REPLACE TYPE init_queue AS OBJECT (
seq_num_q NUMBER,
source_system_name VARCHAR2 (50 BYTE),
milestone_id NUMBER,
milestone_date DATE,
downstream_order_number VARCHAR2 (175 BYTE),
leg_id VARCHAR2 (175 BYTE),
statuspro_captured_date DATE,
pon VARCHAR2 (26 BYTE),
milestone_source VARCHAR2 (26 BYTE),
session_id NUMBER,
reconfirmation_flag CHAR (1 BYTE),
vec_flag CHAR (1 BYTE)
);
CREATE OR REPLACE TYPE tab_init_queue AS TABLE OF init_queue;
*/
v_list tab_init_queue := tab_init_queue ();
TYPE outbound_rec IS RECORD (
seq_num_q DBMS_SQL.number_table,
tin DBMS_SQL.varchar2_table,
nasp_id DBMS_SQL.varchar2_table,
nasp_par_id DBMS_SQL.varchar2_table,
customer_name DBMS_SQL.varchar2_table,
downstream_order_number DBMS_SQL.varchar2_table,
lead_order_number DBMS_SQL.varchar2_table,
milestone_id DBMS_SQL.number_table,
milestone_date DBMS_SQL.date_table,
source_system_name DBMS_SQL.varchar2_table
);
l_outbound_rec outbound_rec;
BEGIN
v_list.EXTEND;
v_list (v_list.COUNT) :=
init_queue (1,
'1',
11,
SYSDATE,
'1',
'1',
SYSDATE,
'1',
'1',
1,
'1',
'1'
);
v_list.EXTEND;
v_list (v_list.COUNT) :=
init_queue (2,
'2',
11,
SYSDATE,
'2',
'2',
SYSDATE,
'2',
'2',
1,
'2',
'2'
);
FOR i IN (SELECT seq_num_q, source_system_name, milestone_id,
milestone_date, downstream_order_number, leg_id,
statuspro_captured_date, pon, milestone_source,
session_id, reconfirmation_flag, vec_flag
FROM TABLE (CAST (v_list AS tab_init_queue)) a
WHERE milestone_id = 11)
LOOP
l_outbound_rec.seq_num_q(i) := i.seq_num_q; -- ****Failing here****
DBMS_OUTPUT.put_line ( i.seq_num_q
|| ','
|| i.source_system_name
|| ','
|| i.milestone_id
|| ','
|| i.milestone_date
|| ','
|| i.downstream_order_number
|| ','
|| i.leg_id
|| ','
|| i.statuspro_captured_date
|| ','
|| i.pon
|| ','
|| i.milestone_source
|| ','
|| i.session_id
|| ','
|| i.reconfirmation_flag
|| ','
|| i.vec_flag
);
END LOOP;
END;
/
thanks