Home » SQL & PL/SQL » SQL & PL/SQL » Help Needed in Bulk collect (Oracle 10g)
Help Needed in Bulk collect [message #327516] Mon, 16 June 2008 12:59 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Hi all,

DECLARE
TYPE Prod_tab IS TABLE OF STG_SUB_MASTER_MONTH_HISTORY%ROWTYPE ;
Values_tab Prod_tab := Prod_tab ();
CURSOR Cur_sub_rp IS
SELECT A.*
FROM STG_SUB_MASTER_MONTH_HISTORY A, SUB_PHONE_RATEPLAN B
WHERE A.SUB_SSN = B.WS_ESN_UN(+);
BEGIN
OPEN Cur_sub_rp ;
LOOP
FETCH Cur_sub_rp BULK COLLECT INTO Values_tab LIMIT 10000;
EXIT WHEN Cur_sub_rp%NOTFOUND ;
END LOOP ;
CLOSE Cur_sub_rp;
END;

In the above program I want to use A.col1, A.col2, B.col2 instead of * . so is it possible.

Re: Help Needed in Bulk collect [message #327518 is a reply to message #327516] Mon, 16 June 2008 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you just have to declare a record type containing these columns.

Regards
Michel
Re: Help Needed in Bulk collect [message #327522 is a reply to message #327518] Mon, 16 June 2008 13:49 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
DECLARE
TYPE Prod_tab IS RECORD
(Sub_Account STG_SUB_MASTER_MONTH_HISTORY.SUB_ACCOUNT%TYPE,
Rate_Plan SUB_PHONE_RATEPLAN.Rate_Plan%Type,
Sub_Last_Name SUB_PHONE_RATEPLAN.SUB_LAST_NAME%Type,
Sub_SSN STG_SUB_MASTER_MONTH_HISTORY.SUB_SSN%Type);
Values_tab Prod_tab;
CURSOR Cur_sub_rp IS
SELECT A.SUB_ACCOUNT, B.RATE_PLAN,B.SUB_LAST_NAME,A.SUB_SSN
FROM STG_SUB_MASTER_MONTH_HISTORY A, SUB_PHONE_RATEPLAN B
WHERE A.SUB_ACCOUNT = B.SUB_ACCOUNT (+);
BEGIN
OPEN Cur_sub_rp ;
LOOP
FETCH Cur_sub_rp BULK COLLECT INTO Values_tab.Sub_Account,
Values_tab.Rate_plan,
Values_tab.Sub_Last_name,
Values_tab.Sub_Ssn
LIMIT 1000;
EXIT WHEN Cur_sub_rp%NOTFOUND ;
END LOOP ;
CLOSE Cur_sub_rp;
END;



getting PLS 00497. googled it and modified but still getting the same error.
Re: Help Needed in Bulk collect [message #327523 is a reply to message #327522] Mon, 16 June 2008 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try just "BULK COLLECT INTO Values_tab"?
Otherwise (it depends on version), you have to give a table for each variable, that is declared 4 table variables of correct type.

Regards
Michel
Re: Help Needed in Bulk collect [message #327524 is a reply to message #327523] Mon, 16 June 2008 14:06 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
I have declared record type of respective variables.
Now i am using bulk collect as mentioned below.

BULK COLLECT INTO Values_tab.Sub_Account,
Values_tab.Rate_plan,
Values_tab.Sub_Last_name,
Values_tab.Sub_Ssn
Re: Help Needed in Bulk collect [message #327526 is a reply to message #327524] Mon, 16 June 2008 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you can't use a table of record but 4 tables one for each field.

Regards
Michel
Re: Help Needed in Bulk collect [message #327533 is a reply to message #327524] Mon, 16 June 2008 16:02 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am not sure if you are trying to do something like this. But for what it is worth I thought I will post this.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

  1  declare
  2  type record_emp is record
  3  (l_empno emp.empno%type,
  4   l_ename emp.ename%type,
  5   l_job   emp.job%type
  6  );
  7  type table_emp is table of record_emp;
  8  vr_emp table_emp;
  9  cursor c1
 10  is
 11  select empno, ename, job from emp;
 12  begin
 13  open c1;
 14  fetch c1 bulk collect into vr_emp limit 10;
 15  for i in vr_emp.first .. vr_emp.last
 16  loop
 17      dbms_output.put_line('Empno : ' || vr_emp(i).l_empno);
 18      dbms_output.put_line('Ename : ' || vr_emp(i).l_ename);
 19  end loop;
 20  close c1;
 21* end;
SQL> /
Empno : 7839
Ename : KING
Empno : 7698
Ename : BLAKE
Empno : 7782
Ename : CLARK
Empno : 7566
Ename : JONES
Empno : 7788
Ename : SCOTT
Empno : 7902
Ename : FORD
Empno : 7369
Ename : SMITH
Empno : 7499
Ename : ALLEN
Empno : 7521
Ename : WARD
Empno : 7654
Ename : MARTIN

PL/SQL procedure successfully completed.

Regards

Raj

[Edit : ] Added oracle version

[Updated on: Mon, 16 June 2008 16:04]

Report message to a moderator

Previous Topic: Oracle Installation
Next Topic: query to return entries with maximum column value
Goto Forum:
  


Current Time: Sun Dec 04 13:00:50 CST 2016

Total time taken to generate the page: 0.10825 seconds