Solved

FETCH BULK COLLECT from cursor to table of records

Posted on 2007-04-11
11
19,712 Views
Last Modified: 2012-06-27
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;
0
Comment
Question by:DalTXColtsFan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 18891237
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
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 18891343
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
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 18891368
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:MohanKNair
ID: 18892078
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
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 18892373
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
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 18892382
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
 
LVL 16

Expert Comment

by:MohanKNair
ID: 18892692
Change the following line

>>  type t_perfdata_tab is table of c_perfdata;

 type t_perfdata_tab is table of c_perfdata%ROWTYPE;
0
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 18892747
----------
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
 
LVL 16

Accepted Solution

by:
MohanKNair earned 250 total points
ID: 18894287
I think this is implementation restriction. Instead of creating a table of RECORD or ROWTYPE, create several tables of <COLUMN_NAME>%TYPE.
0
 
LVL 2

Author Comment

by:DalTXColtsFan
ID: 18934219
I've had a couple other people tell me that you have to create several tables of <column_name>%type as well.
0
 

Expert Comment

by:Rust
ID: 26354992
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question