?
Solved

FETCH BULK COLLECT from cursor to table of records

Posted on 2007-04-11
11
Medium Priority
?
19,736 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

770 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