Home » SQL & PL/SQL » SQL & PL/SQL » Records
Records [message #110391] Mon, 07 March 2005 03:50 Go to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
hi,
is it possible to Bulk Collect into Record types without using the Cursor Fetch statement.
For Example,is this possible:
declare
abc emp%rowtype;
begin
select * bulk collect into abc from emp;
end;

If this is possible,then please tell me how to loop over that Record.i am using Oracle 9i.

thanks and regards,
vishal gupta
Re: Records [message #110396 is a reply to message #110391] Mon, 07 March 2005 04:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You do know that Oracle provides end users with manuals? You can look at them by going to http://otn.oracle.com/documentation. The site requires a free subscription.

Anyway, here's how:
SQL> set serverout on
SQL> declare
  2    type emptype is table of emp%rowtype;
  3    emps emptype;
  4  begin
  5    select *
  6      bulk collect into emps
  7      from emp;
  8    for i in emps.first..emps.last
  9    loop
 10      dbms_output.put_line('emps('||i||').ename = '||emps(i).ename);
 11    end loop;
 12  end;
 13  /
emps(1).ename = SMITH
emps(2).ename = ALLEN
emps(3).ename = WARD
emps(4).ename = JONES
emps(5).ename = MARTIN
emps(6).ename = BLAKE
emps(7).ename = CLARK
emps(8).ename = SCOTT
emps(9).ename = KING
emps(10).ename = TURNER
emps(11).ename = ADAMS
emps(12).ename = JAMES
emps(13).ename = FORD
emps(14).ename = MILLER

PL/SQL procedure successfully completed.


Next time, try to look it up first and then ask a question.

MHE

[Updated on: Mon, 07 March 2005 04:54]

Report message to a moderator

Re: Records [message #110401 is a reply to message #110396] Mon, 07 March 2005 05:29 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
hi Maaher,
thanks for your reply.
I do have an access to docs and go through them regularly.
Anyway,my question was whether we can Bulk Collect into an Record Type and Not Nested Table.the example you have given is about Bulk Collecting into an Nested Table but my question was whether it can be done with Record Types.
I know while using Cursor ,Bulk Collect can be used with an Fetch statement to insert into an Record Type but can same thing be done with just an SQL statement as i asked.
Please remember ,i am asking about an Record Type and not about PL/SQL tables(Nested tables and Varrays).

Thanks and regards ,
vishal gupta
Re: Records [message #110417 is a reply to message #110401] Mon, 07 March 2005 06:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have you tried it? The message is clear.
SQL> declare
  2  abc emp%rowtype;
  3  begin
  4  select * bulk collect into abc from emp;
  5  end;
  6  /
select * bulk collect into abc from emp;
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
ORA-06550: line 4, column 32:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
Think about it and you'll realise it makes no sense. Bulk operations are there to fetch multiple records in bulk. Even when you add a restriction to make sure that only 1 row got fetched, it'll fail.

Quote:

If this is possible,then please tell me how to loop over that Record.
How can you loop over a single record? There's nothing to loop. Hence, I assumed you meant a nested table.

So in short: No this cannot be done.

I'm still puzzled, why would you try to fetch ALL emp records in a single record variable?

MHE
Re: Records [message #110604 is a reply to message #110391] Tue, 08 March 2005 22:57 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Which Oracle version are you using? You can do this only in 9.0.2.3 and above. Not possible in 8i.


I suppose you are asking for what is given as below...
================================================
DECLARE
TYPE CustRec IS RECORD
(R_Customer_Account_id Customer.Customer_Account_id%TYPE,
R_Effective_Date Customer.Effective_Date%TYPE,
R_Expired_Date Customer.Expired_Date%TYPE);

TYPE CustRecTab IS TABLE OF CustRec;
Cust_Recs CustRecTab;
v_Array_Size NUMBER := 100;

CURSOR c1 IS
SELECT Customer_Account_Id, Effective_Date, Expired_Date
FROM Customer;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO Cust_Recs LIMIT v_Array_Size;
CLOSE c1;
END;
=================================================

Regards
Himanshu
Re: Records [message #424368 is a reply to message #110604] Thu, 01 October 2009 17:14 Go to previous messageGo to next message
mbalas2
Messages: 2
Registered: October 2009
Location: Chicago
Junior Member
Am getting:

PLS-00386: type mismatch found at 'V_COL_TYPE_VER_COMPARE' between FETCH cursor and INTO variables

function fun(x, y,z) return COL_TYPE_VER_COMPARE is

V_COL_TYPE_VER_COMPARE COL_TYPE_VER_COMPARE;

BEGIN

OPEN CUR_INS_REMOVED;
LOOP
FETCH CUR_INS_REMOVED bulk collect into V_COL_TYPE_VER_COMPARE ;
exit when CUR_INS_REMOVED%NOTFOUND;
END LOOP;
CLOSE CUR_INS_REMOVED;

RETURN V_COL_TYPE_VER_COMPARE;
END;

I verified all the columns in the TYPE and COLLECTION.

Someone pls help to solve this issue.
Re: Records [message #424385 is a reply to message #424368] Thu, 01 October 2009 23:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I verified all the columns in the TYPE and COLLECTION.

We can't, should we trust you?
Post evidences.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Records [message #424478 is a reply to message #424385] Fri, 02 October 2009 10:20 Go to previous messageGo to next message
mbalas2
Messages: 2
Registered: October 2009
Location: Chicago
Junior Member
Yes, i verified it twice. But its wierd that it isnt working.
Made a fix , by using REF cursor, to get rid of the problem of exact type matches. And it worked!!

See the code excerpt:
FUNCTION get_ins_rem(P_ver_sub_id IN NUMBER, P_base_version in NUMBER,  P_prev_version in NUMBER)  
RETURN TYPE_VER_COMPARE_OUT is

  V_TYPE_VER_COMPARE_OUT TYPE_VER_COMPARE_OUT;
  TYPE CUR_INS_REMOVED IS REF CURSOR;
  c1 CUR_INS_REMOVED;  
  v_query VARCHAR2(32767);  
 
  BEGIN
  
   v_query := 'select .... '   ;
    
    OPEN c1 for v_query;   
    LOOP            
         FETCH c1 bulk collect into V_TYPE_VER_COMPARE_OUT ;
         exit when c1%NOTFOUND;
    END LOOP;     
    CLOSE c1;    
    
    RETURN V_TYPE_VER_COMPARE_OUT;
  END;


ModEdit: Added [code][code] tags. Please do this yourself in future posts. Thanks

[Updated on: Fri, 02 October 2009 10:33] by Moderator

Report message to a moderator

Re: Records [message #424479 is a reply to message #424478] Fri, 02 October 2009 10:32 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Made a fix , by using REF cursor, to get rid of the problem of exact type matches. And it worked!!

Nothing strange you change the strongly typed variable to a looy one. No verification is made on what is returned.

Your post is still not formatted. Please read the forum guide and follow it.

For you next question, be sure we do not trust you when you say you verified, we want proof.

Regards
Michel
Previous Topic: regular expression
Next Topic: Unable to retrieve all records
Goto Forum:
  


Current Time: Mon Dec 05 08:46:15 CST 2016

Total time taken to generate the page: 0.10108 seconds