Home » SQL & PL/SQL » SQL & PL/SQL » initialize a nested table (Oracle11gR2, windows 7)
initialize a nested table [message #648311] Sat, 20 February 2016 03:30 Go to next message
live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India
Member
SQL> desc t1;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
C1 NUMBER Y
C2 VARCHAR2(90) Y

----------------

declare
-- Local variables here
type t1_typ is table of t1%rowtype;
tab1 t1_typ:=t1_typ();

begin
-- Test statements here
tab1.extend(1);

tab1(1):=t1_typ(1,'asdf');

end;


it is giving error
Ora -06550 - line 10,col 12
PLS 00306 wrong number or type of arguments in call to 'T1_TYP'


How can it be initialized ?


Re: initialize a nested table [message #648314 is a reply to message #648311] Sat, 20 February 2016 05:16 Go to previous messageGo to next message
sss111ind
Messages: 636
Registered: April 2012
Location: India
Senior Member


DECLARE 
    -- Local variables here 
    TYPE t1_typ 
      IS TABLE OF t1%ROWTYPE; 
    tab1      T1_TYP := T1_typ(); 
    l_counter NUMBER := 1; 
    CURSOR c1 IS 
      SELECT * 
      FROM   t1; 
BEGIN 
    -- Test statements here 
    tab1.Extend(1); 

	--initialing single 
    --tab1(1).C1:=10; 
    --tab1(1).C2:='ACCOUNT'; 
 
	
	--fetching all the data 
    OPEN c1; 

    LOOP 
        FETCH c1 bulk collect INTO tab1; 

        exit WHEN tab1.count = 0; 

        FOR i IN tab1.first .. tab1.last LOOP 
            dbms_output.Put_line(Tab1(i).C1 
                                 ||Tab1(i).c2); 
        END LOOP; 
    END LOOP; 

    CLOSE c1; 
END; 
Re: initialize a nested table [message #648315 is a reply to message #648314] Sat, 20 February 2016 05:33 Go to previous messageGo to next message
live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India
Member
thanks
Re: initialize a nested table [message #648354 is a reply to message #648314] Mon, 22 February 2016 00:57 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
You don't need to INITIALIZE and EXTEND a collection if you use BULK COLLECT.
And you shouldn't FETCH in the LOOP execpt you use the LIMIT clause.

[Updated on: Mon, 22 February 2016 00:59]

Report message to a moderator

Previous Topic: data view
Next Topic: How to use LAG function in oracle pl sql until non zero value is reached?
Goto Forum:
  


Current Time: Sun Jun 02 20:10:08 CDT 2024