Solved

FETCH BULK COLLECT from cursor to table of records

Posted on 2007-04-11
11
19,686 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now