Didn't Venkatesh say he had a PL/SQL table with *millions* of rows in it? Bet ya it'll be slow whatever way you slice it or dice it. How do you get millions of rows into a PL/SQL table anyway... lots of spare RAM? Maybe a convential table, with appropriate indexing, might be better in this case...

Isn't it an idea to use temporary tables for this purpose? At Oracle in the Netherlands our Headstart group developed CDM Ruleframe. A framework for business rules in the application. It used to use pl/sql tables for building a rule-stack and a stack of 'touched records'. This was a perfomance issue for tables with many business-rules and for batch jobs. Now the pl/sql tables are replaced by temp-tables and de performance raised significantly.

Fine Tom,

you have also suggested the FOR LOOP for searching a string in PL/SQL table. Is it possible to transfer the records in the pl/sql table to REF CURSOR???

I was searching on this same problem and hence i thought to add to this instead of creating a new question.
By the way i am a first time user of this site.
So, i am not sure if i am doing the right way.
Again i am also dealing with millions of records and trying to achieve perf. using collections - tables. I had created some sample tables and code and tried running it for a million record of table having just 2 columns.
I wrote the code in 2 ways - 1) the conventional cursor and insert in loop and 2)the cursor bulk fetched into collection and insert using FORALL. Method 1 took 6 mins and method 2) took 26 secs. I know that dealing with million records and almost with 30 columns is highly impossible using collection. But what if i also use the LIMIT clause of BULK collect and do for 500 or 1000 records at a time. Is it still not better than Method 1 ? And i am put up with one more problem - not being able to use fields or %ROWTYPE as in the subject of this question. Please suggest.

and method three

insert into t2 select * from t1;

would take even less time!


you use limit with bulk collect to operate against lots of records -- yes, but if you can do it in a single SQL -- by all means erase that code.

ops$tkyte@ORA9IR2> drop table emp;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          type array is table of emp%rowtype index by binary_integer;
  3          l_data array;
  4          cursor c is select * from emp;
  5  begin
  6          open c;
  7          loop
  8                  fetch c bulk collect into l_data limit 5;
  9                  forall i in 1 .. l_data.count
 10                          insert into emp VALUES l_data(i);
 11                  exit when c%notfound;
 12          end loop;
 13          close c;
 14  end;
 15  /
 
PL/SQL procedure successfully completed.


<b>now, if that doesn't work for you -- that is because you are on significantly old software -- 8i.  the ability to do that via records is new in 9i</b>
 

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

I found this on the web there is workaround for above error

--
-- Create an object and collection to define a set of rows to update with...
--

CREATE TYPE emp_ot AS OBJECT
( empno NUMBER(4)
, ename VARCHAR2(10)
, sal NUMBER(7,2)
);
/

CREATE TYPE emp_ntt AS TABLE OF emp_ot;
/


--
-- Now we'll fetch a collection of EMP records and then perform a FORALL
-- UPDATE referencing some of the attributes in each record of the
-- collection (usually restricted)...
--

DECLARE

nt_emp emp_ntt;

BEGIN

/* Populate the demo collection... */
SELECT emp_ot(empno, ename, sal) BULK COLLECT INTO nt_emp
FROM emp;

/* FORALL with workaround... */
FORALL i IN nt_emp.FIRST .. nt_emp.LAST
UPDATE emp
SET ename = TREAT( nt_emp(i) AS emp_ot ).ename
, sal = TREAT( nt_emp(i) AS emp_ot ).sal * 1.1
WHERE empno = TREAT( nt_emp(i) AS emp_ot ).empno;

DBMS_OUTPUT.PUT_LINE(
TO_CHAR( SQL%ROWCOUNT ) || ' rows updated.'
);

END;
/

spool off
--
-- Cleanup...
--
DROP TYPE emp_ot FORCE;
DROP TYPE emp_ntt FORCE;

this worked for me and you can find the oringinal authors
http://mysite.wanadoo-members.co.uk/adriansoraclepages/demos/10g/scripts/forall_restriction_workaround.sql

Original author goes by the name of Mr.James Padfield.

Hi,

this is my code


TYPE RefCur IS REF CURSOR;
UPD_CUR RefCur;

TYPE t_ROWIDS IS TABLE OF UROWID INDEX BY PLS_INTEGER;
col_ROWIDS t_ROWIDS;

s_TableNm VARCHAR2(200) := Upper(RTrim(p_TableNm));
s_FSQLText t_SQLText;
s_USQLText t_SQLText;

BEGIN
CASE
-- Special Process for ACCOUNT_DIM Table
WHEN s_TableNm = 'ACCOUNT_DIM'
OR s_TableNm = 'DW.ACCOUNT_DIM' THEN
s_TableNm := s_TableNm || ' PARTITION(ACCT_DIM_ACTIV)';
s_USQLText := 'UPDATE ' || s_TableNm || ' SET RECORD_STATUS = 1 WHERE RECORD_STATUS IN (8, 9)';
EXECUTE IMMEDIATE s_USQLText;
COMMIT;

-- Special Process for BBT RTL Table
WHEN s_TableNm = 'ACCOUNT_DIM_RTL_BBT'
OR s_TableNm = 'DWBBT.ACCOUNT_DIM_RTL_BBT' THEN
s_TableNm := s_TableNm || ' PARTITION(ACTDM_BBT_RTL_ACTIV)';
s_USQLText := 'UPDATE ' || s_TableNm || ' SET RECORD_STATUS = 1 WHERE RECORD_STATUS IN (8, 9)';
EXECUTE IMMEDIATE s_USQLText;
COMMIT;

-- Special Process for BBT COM Table
WHEN s_TableNm = 'ACCOUNT_DIM_COM_BBT'
OR s_TableNm = 'DWBBT.ACCOUNT_DIM_COM_BBT' THEN
s_TableNm := s_TableNm || ' PARTITION(ACTDM_BBT_COM_ACTIV)';
s_USQLText := 'UPDATE ' || s_TableNm || ' SET RECORD_STATUS = 1 WHERE RECORD_STATUS IN (8, 9)';
EXECUTE IMMEDIATE s_USQLText;
COMMIT;

-- Special Process for BBT CHH Table
WHEN s_TableNm = 'CARDHOLDER_HIST_DIM'
OR s_TableNm = 'DWBBT.CARDHOLDER_HIST_DIM' THEN
s_TableNm := s_TableNm || ' PARTITION(CH_HIST_ACTIV)';
s_USQLText := 'UPDATE ' || s_TableNm || ' SET RECORD_STATUS = 1 WHERE RECORD_STATUS IN (8, 9)';
EXECUTE IMMEDIATE s_USQLText;
COMMIT;

ELSE
s_FSQLText := 'SELECT ROWID FROM ' || p_TableNm || ' WHERE RECORD_STATUS IN (8, 9)';
s_USQLText := 'UPDATE ' || p_TableNm || ' SET RECORD_STATUS = 1 WHERE ROWID = :r';

-- READS ALL STATUS 8/9
OPEN UPD_CUR FOR s_FSQLText;
i_RowCount := 0;
LOOP
FETCH UPD_CUR
BULK COLLECT INTO col_ROWIDS
LIMIT 50000;

EXIT WHEN col_ROWIDS.COUNT = 0;
FORALL indx IN 1 .. col_ROWIDS.COUNT SAVE EXCEPTIONS
EXECUTE IMMEDIATE s_USQLText USING col_ROWIDS (indx);
COMMIT;
i_RowCount := i_RowCount + col_ROWIDS.COUNT;
i_RowCount := i_RowCount - SQL%BULK_EXCEPTIONS.COUNT;
END LOOP;
COMMIT;
CLOSE UPD_CUR;
END CASE;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL;
WHEN OTHERS THEN
IF SQLCODE = -24381 THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (
SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ': '
|| SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
END LOOP;
ELSE
RAISE_APPLICATION_ERROR(-20001, 'Error in package PKG_ETL_RECSTAT_MERGE Procedure SP_UPD_RECSTAT_9to1 error_id = ' || SQLCODE || '-' || SQLERRM);
END IF;
END SP_UPD_RECSTAT_9to1;




when i m running it in 11g, it is running fine but i am getting error in 10g

implementation restriction: cannot reference fields of bulk in-bind table of records error. Please help. I am very new in PL/SQL. How to modify the code to avoid this error. Thanks.