Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Arrays Nested tables
PL/SQL Arrays Nested tables [message #3419] Wed, 25 September 2002 20:03 Go to next message
Prasad
Messages: 104
Registered: October 2000
Senior Member
Hi !

I am a newbie @ PL/SQL, I come from perl/java background. Could someone help me understand how do I implement arrays in pl/sql code ?

If I will be using >8.0 What do I use to manipulate data as an array and process this array? (varray/nested table? )

Is temp table is same as nested table?

Please let me know a code example for this so as I can get better picture of how I manage temp dataset as we do in Perl/Java as Array/Vectors/Hashes...

Thanks
Re: PL/SQL Arrays Nested tables [message #3425 is a reply to message #3419] Wed, 25 September 2002 22:18 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3170352229012

A temp table is quite different - it is a physical object, not a memory structure like PL/SQL collections.
Re: PL/SQL Arrays Nested tables [message #3427 is a reply to message #3425] Wed, 25 September 2002 23:06 Go to previous messageGo to next message
Prasad
Messages: 104
Registered: October 2000
Senior Member
Thanks very much, it certainly helped a LOT.

Now I am lil confused about .extend. Let's say in my PL/SQL code I need to push certain values in an array/nested table and once this loop is over I want to fetch and process the values I stored into this array/nested table. With the current syntax how can I open up a n.table say

mytable is TABLE of varchar2(50) ...this is another catch what if the values I wanna push are long type? it raises error.. But bsides the point...
now I create

testtable mytable;

And in the LOOP i wanna assign testtable(1):='Test1'; and so on for multiple elements How do I do that?

Let me give a simple example in perl which may helo u undertsand what I am asking for,

@test;
in the program logic I can dynamically assign
$test[[1]]='Test1';
and go on adding elements to this array on the fly.

How do I do it here?

Thanks
Re: PL/SQL Arrays Nested tables [message #3439 is a reply to message #3425] Thu, 26 September 2002 14:39 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Here are examples for ibt (index-by tables, or pl/sql tables) and varray. Hope they help.

sql>declare
  2    type tibt is table of varchar2(10) index by binary_integer;
  3    type tva  is table of varchar2(10);
  4    v_ibt  tibt;
  5    v_va   tva := tva();
  6  begin
  7    -- index-by table
  8    for i in 1..3 loop
  9      v_ibt(i) := 'Test ' || i;
 10    end loop;
 11    for i in 1..v_ibt.count loop
 12      dbms_output.put_line( v_ibt(i) );
 13    end loop;
 14  
 15    -- varray
 16    for i in 1..3 loop
 17      v_va.extend;
 18      v_va(i) := 'Test ' || i;
 19    end loop;
 20    for i in 1..v_va.count loop
 21      dbms_output.put_line( v_va(i) );
 22    end loop;
 23  end;
 24  /

-- this is the index-by table output
Test 1
Test 2
Test 3

-- this is the varray output
Test 1
Test 2
Test 3
Previous Topic: Multiple Rows
Next Topic: stumped Select Rpad
Goto Forum:
  


Current Time: Sun Apr 28 14:02:34 CDT 2024