Hi Tom, You said that in 8i it would not be possible to bulk collect in arrays of records. Can we bulk bind arrays of records or arrays of some other data type using the forall statement in 8i. service_count 23 INSERT INTO onmanage.lang_services (id 24 ,description 25 ,service_name) 26 VALUES (service_(i) 27 ,service_list.description(i) 28 ,service_list.service_name(i)) 29 RETURNING id BULK COLLECT INTO serviceid_list; 30 END; 31 / VALUES (service_(i) * ERROR at line 26: ORA-06550: line 26, column 29: PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification ORA-06550: line 23, column 5: PL/SQL: SQL Statement ignored June 27, 2005 - pm UTC see the extra dot in there. then there is the fact that you defined service_list of type service_t -- but service_t is a record, not a table, so the .extend cannot work.
I'd be looking to REMOVE ALL procedural code, period. EXTEND; 15 := 1002000001; 16 service.service_name := 'XXX'; 17 service.description := 'YYY'; 18 19 -- =================== 20 -- Insert service data 21 -- =================== 22 FORALL i IN 1 .. These values must be used to update -- the records in table FACT_PREP_V_BONITA_small cursor c_update_vouchers is select * from fact_prep_v_tmp2_tb_small; TYPE t_update_vouchers is table of c_update_vouchers%ROWTYPE INDEX BY PLS_INTEGER; l_update_vouchers t_update_vouchers; -- cursor of the original table that must be updated cursor c_update_fact_vouchers is select FACT_PREP_V_BONITA_small.*, rowid from FACT_PREP_V_BONITA_small for update; type rowid Array is table of rowid index by binary_integer; type update_vouchers_rec is record ( SERIESNO NUMBER(3), SERIALNO INTEGER, BATCHNO INTEGER, PACKAGENO INTEGER, STATUS INTEGER, DATEUSED DATE, LOCKED NUMBER(1), LOCKEDREASON VARCHAR2(30), MSISDN VARCHAR2(20), PSTATUS NUMBER(1), PSTRANSACTION NUMBER, PLOCKED NUMBER(1), PLTRANSACTION NUMBER, LOADDATE DATE, VOUCHERID NUMBER ); l_update_vouchers update_vouchers_rec; -- other varaiables l_done boolean; BEGIN -- open cursor of records in fact table that must be updated open c_update_fact_vouchers; loop fetch c_update_fact_vouchers bulk collect into l_update_vouchers.
Note that we are fetching out the ROWID for updating (faster) and we are saving a changed flag as well [email protected] We update any empno with an ODD empno 20 procedure Quo To Pur Days( p_empno in number, p_ename in out NOCOPY varchar2, p_changed in out NOCOPY varchar2 ) 21 is 22 begin 23 if (mod(p_empno,2) = 1 ) 24 then 25 p_ename := initcap( p_ename ); 26 p_changed := 'Y'; 27 end if; 28 end; 29 now for the meat of the routine.
Here we Array fetch N records (set l_array_size to the number of records you want to process. P_COMM; * ERROR at line 12: ORA-06550: line 12, column 4: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list ORA-06550: line 12, column 4: PL/SQL: SQL Statement ignored . May 13, 2003 - pm UTC sorry -- but seems obvious to me? record(n) vs a record of arrays a single record with attributes that happen to be arrays.
The Update is dependent on results of 19 procedures which are quite complex.
I can not do a one hit update outside of the cursor loop.