FETCH BULK COLLECT from cursor to table of records

Greetings experts,

I'm trying to set up a simple bulk update using a record rowtype and a table of that record.  I'm getting errors in the line where I do the fetch saying the SQL statement was ignored and that the table variable is "inappropriate as the left side of a statement".  It's also telling me that COUNT and DELETE need to be delcared.

The purpose of this code is to simply update one table (Product) with rollups from another (performance_data) where there is a common column (ProductID is ID in Product, CIID in performance_data) - I've never used the BULK COLLECT and FORALL features before and have heard they're "really cool" so I wanted to practice doing it this way.

Should this approach work?  If so, what am I doing wrong?

Many thanks,
Joe


Here's the code:



CREATE OR REPLACE PROCEDURE fsp_practice_bulk_update (p_error OUT NUMBER)
IS

   TYPE r_fspperf IS RECORD (
      ProdID INTEGER, ProdDBKey INTEGER, date3 DATE, value1 FLOAT, value2 FLOAT);
           
   type t_perfdata is table of r_fspperf index by binary_integer;
   
   cursor c_perfdata is
      SELECT  prod.id, min(prod.DBKey), MAX (edwweek),
         ROUND (AVG (perf.avgunits1wk), 2), ROUND (SUM (perf.totalunits1wk), 2)
      FROM performance_data perf
      inner join Product prod
         on CAST (perf.ciid AS VARCHAR (16)) = prod.ID
      GROUP BY perf.ciid;
     
      v_tablecount   integer;

begin

   DBMS_OUTPUT.put_line (   'Product table update begins '
                         || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH:MI:SS')
                        );  
   
   open c_perfdata;
   fetch c_perfdata bulk collect into t_perfdata;
   v_tablecount := t_perfdata.COUNT;
   if v_tablecount > 0 then
         FORALL idx IN 1..t_perfdata.COUNT
         update Product set
            date3 = t_perfdata(idx).Date3, value1 = t_perfdata(idx).Value1, value2 = t_perfdata(idx).value2
         where DBKey = t_perfdata(idx).ProdDBKey;
   end if;  

      t_perfdata.DELETE;

   CLOSE c_perfdata;
   
   DBMS_OUTPUT.put_line (   'Product table update complete '
                         || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH:MI:SS')
                        );

   p_error := 0;
     
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'Run Date: '
                            || TO_CHAR (SYSDATE, 'yyyymmdd')
                            || '  Error: '
                            || SQLCODE
                            || '-'
                            || SUBSTR (SQLERRM, 1, 200)
                           );
      p_error := SQLCODE;
                           
end;
LVL 2
DalTXColtsFanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MohanKNairCommented:
It is not possible to BULK COLLECT into INDEX BY tables.

CREATE OR REPLACE PROCEDURE fsp_practice_bulk_update (p_error OUT NUMBER) IS
 cursor c_perfdata is  SELECT prod.id, min(prod.DBKey), MAX (edwweek),
 ROUND (AVG (perf.avgunits1wk), 2), ROUND (SUM (perf.totalunits1wk), 2)
 FROM performance_data perf inner join Product prod
 on CAST (perf.ciid AS VARCHAR (16)) = prod.ID GROUP BY perf.ciid;
 type t_perfdata is table of c_perfdata;
 v_tablecount integer;
begin

 DBMS_OUTPUT.put_line ( 'Product table update begins '|| TO_CHAR (SYSDATE, 'MM/DD/YYYY HH:MI:SS') );
 
 open c_perfdata;
 fetch c_perfdata bulk collect into t_perfdata;
 v_tablecount := t_perfdata.COUNT;
 if v_tablecount > 0 then
 FORALL idx IN 1..t_perfdata.COUNT
 update Product set
 date3 = t_perfdata(idx).Date3, value1 = t_perfdata(idx).Value1, value2 = t_perfdata(idx).value2
 where DBKey = t_perfdata(idx).ProdDBKey;
 end if;
 t_perfdata.DELETE;
 CLOSE c_perfdata;
 
 DBMS_OUTPUT.put_line ( 'Product table update complete ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH:MI:SS') );
 p_error := 0;
 
EXCEPTION
 WHEN OTHERS  THEN  DBMS_OUTPUT.put_line ( 'Run Date: ' || TO_CHAR (SYSDATE, 'yyyymmdd') || ' Error: ' || SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200) );
 p_error := SQLCODE;
end;
/
0
DalTXColtsFanAuthor Commented:
Thanks Mohan.

Is it the fact that the table is a table of records that's screwing it up?  I could swear I've seen coworkers create tables of integers or VARCHARs and index them binary_integer and use BULK COLLECT, i.e.

TYPE typ_dbkey IS TABLE OF IAmATable.dbkey%TYPE INDEX BY BINARY_INTEGER;
TYPE typ_week IS TABLE OF fiscal_week.wk_idnt%TYPE INDEX BY BINARY_INTEGER;
arr_dbkey typ_dbkey;
arr_week typ_week;

cursor c1 is select dbkey, week from some table

begin

open c1
fetch c1 bulk collect into arr_dbkey, arr_week



I swear I've seen that work - is it the INDEX BY that's messing me up or the fact that I'm using a table of records?
0
DalTXColtsFanAuthor Commented:
Also it's still not liking the fact that I have COUNT and DELETE in there - it's telling me they "must be declared".

I'm working in Oracle 10g if it makes any difference.

Any thoughts?

Thanks again
Joe
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MohanKNairCommented:
Try this code

CREATE OR REPLACE PROCEDURE fsp_practice_bulk_update (p_error OUT NUMBER) IS
 cursor c_perfdata is  SELECT prod.id, min(prod.DBKey), MAX (edwweek),
 ROUND (AVG (perf.avgunits1wk), 2), ROUND (SUM (perf.totalunits1wk), 2)
 FROM performance_data perf inner join Product prod
 on CAST (perf.ciid AS VARCHAR (16)) = prod.ID GROUP BY perf.ciid;
 type t_perfdata_tab is table of c_perfdata;
 t_perfdata t_perfdata_tab;
 v_tablecount integer;
begin

 DBMS_OUTPUT.put_line ( 'Product table update begins '|| TO_CHAR (SYSDATE, 'MM/DD/YYYY HH:MI:SS') );
 
 open c_perfdata;
 fetch c_perfdata bulk collect into t_perfdata;
 v_tablecount := t_perfdata.COUNT;
 if v_tablecount > 0 then
 FORALL idx IN 1..t_perfdata.COUNT
 update Product set
 date3 = t_perfdata(idx).Date3, value1 = t_perfdata(idx).Value1, value2 = t_perfdata(idx).value2
 where DBKey = t_perfdata(idx).ProdDBKey;
 end if;
 t_perfdata.DELETE;
 CLOSE c_perfdata;
 
 DBMS_OUTPUT.put_line ( 'Product table update complete ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH:MI:SS') );
 p_error := 0;
 
EXCEPTION
 WHEN OTHERS  THEN  DBMS_OUTPUT.put_line ( 'Run Date: ' || TO_CHAR (SYSDATE, 'yyyymmdd') || ' Error: ' || SQLCODE || '-' || SUBSTR (SQLERRM, 1, 200) );
 p_error := SQLCODE;
end;
/
0
DalTXColtsFanAuthor Commented:
You may have been right the first time.  Here are the errors I get.  Check the last one especially:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00801: internal error [pht_decl_type: input node kind]
0/0      PLS-00801: internal error [pht_decl_type: input node kind]
13/5     PL/SQL: Item ignored
13/37    PLS-00488: invalid variable declaration:  object 'C_PERFDATA'
         must be a type or subtype

24/4     PL/SQL: SQL Statement ignored
28/10    PL/SQL: SQL Statement ignored
29/86    PL/SQL: ORA-06544: PL/SQL: internal error, arguments:
         [pht_decl_type: input node kind], [], [], [], [], [], [], []


LINE/COL ERROR
-------- -----------------------------------------------------------------
29/86    PLS-00436: implementation restriction: cannot reference fields of
         BULK In-BIND table of records
0
DalTXColtsFanAuthor Commented:
Please ignore the previous post, I fixed the c_perfdata error.  Here are the error messages:


Errors for PROCEDURE FSP_PRACTICE_BULK_UPDATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
31/10    PL/SQL: SQL Statement ignored
32/21    PLS-00382: expression is of wrong type
32/21    PLS-00436: implementation restriction: cannot reference fields of
         BULK In-BIND table of records

32/53    PLS-00382: expression is of wrong type
32/53    PLS-00436: implementation restriction: cannot reference fields of
         BULK In-BIND table of records

32/86    PL/SQL: ORA-22806: not an object or REF
32/86    PLS-00382: expression is of wrong type

LINE/COL ERROR
-------- -----------------------------------------------------------------
32/86    PLS-00436: implementation restriction: cannot reference fields of
         BULK In-BIND table of records

33/24    PLS-00382: expression is of wrong type
33/24    PLS-00436: implementation restriction: cannot reference fields of
         BULK In-BIND table of records
0
MohanKNairCommented:
Change the following line

>>  type t_perfdata_tab is table of c_perfdata;

 type t_perfdata_tab is table of c_perfdata%ROWTYPE;
0
DalTXColtsFanAuthor Commented:
----------
Change the following line

>>  type t_perfdata_tab is table of c_perfdata;

 type t_perfdata_tab is table of c_perfdata%ROWTYPE;
-----------

Tried that.  Same errors.
0
MohanKNairCommented:
I think this is implementation restriction. Instead of creating a table of RECORD or ROWTYPE, create several tables of <COLUMN_NAME>%TYPE.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DalTXColtsFanAuthor Commented:
I've had a couple other people tell me that you have to create several tables of <column_name>%type as well.
0
RustCommented:
Tested on Oracle 11g (11.1.0.7.0)

It should be like that:

Suppose, we have table t (a integer, b varchar2(15));
and table z (c integer, d date);

declare
  type t_rec is record (
     a t.a%type,
     b t.b%type
  );

  type t_tab is table of t_rec index by binary integer;

  v_tab t_tab;

  cursor c_main is
    select a,b from t;

  l_c z.c%type;
  l_d z.d%type;

begin
  open c_main;
  loop
    fetch c_main bulk collect into v_tab;

    exit when c_main%notfound;

    for i in v_tab.first .. v_tab.last loop
      select c,d
        into l_c, l_d
        from z
        where c = v_tab(i).a;
   
    end loop;
  end loop;
  close c_main;
end;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.