Home » SQL & PL/SQL » SQL & PL/SQL » adding multiple columns to table type (ORACLE, 10G,WINDOWS)
adding multiple columns to table type [message #578588] Fri, 01 March 2013 11:35 Go to next message
shankar680
Messages: 11
Registered: February 2013
Location: HYDERABAD
Junior Member
I have requirement on table type. without using bulk or %rowtype is there any possible to create table type with two or more columns.
I got with single column,but I am unable to create with multiple columns.


DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
T T1;
CNT NUMBER:=0;
BEGIN
FOR I IN 1..100 LOOP
CNT:=CNT+1;
T(CNT):=I;
END LOOP;
FOR J IN T.FIRST..T.LAST LOOP
DBMS_OUTPUT.PUT_LINE(J);
END LOOP;
END;
Re: adding multiple columns to table type [message #578589 is a reply to message #578588] Fri, 01 March 2013 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: adding multiple columns to table type [message #578590 is a reply to message #578588] Fri, 01 March 2013 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
TYPE T1 IS TABLE OF <any type you want> 


Regards
Michel
Re: adding multiple columns to table type [message #578592 is a reply to message #578590] Fri, 01 March 2013 11:54 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Two columns? Such as
SQL> DECLARE
  2      TYPE rt IS RECORD (
  3        col1 NUMBER,
  4        col2 NUMBER);
  5      TYPE t1
  6        IS TABLE OF RT INDEX BY BINARY_INTEGER;
  7      t T1;
  8  BEGIN
  9      FOR i IN 1..5 LOOP
 10          T(i).col1 := i;
 11
 12          T(i).col2 := i + 10;
 13      END LOOP;
 14
 15      FOR j IN t.first..t.last LOOP
 16          dbms_output.Put_line(T(j).col1
 17                               ||' - '
 18                               || T(j).col2);
 19      END LOOP;
 20  END;
 21
 22  /
1 - 11
2 - 12
3 - 13
4 - 14
5 - 15

PL/SQL procedure successfully completed.

SQL>
Re: adding multiple columns to table type [message #578593 is a reply to message #578592] Fri, 01 March 2013 11:57 Go to previous messageGo to next message
shankar680
Messages: 11
Registered: February 2013
Location: HYDERABAD
Junior Member
BUT MY QUERY IS WITHOUT USING TABLE OR BULK,%ROWTYPE.

like this

TYPE T1 IS TABLE OF NUMBER,VARCHAR2,date INDEX BY BINARY_INTEGER;
Re: adding multiple columns to table type [message #578594 is a reply to message #578593] Fri, 01 March 2013 12:06 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Read some documentation, try by yourself, and then you tell us.
Re: adding multiple columns to table type [message #578595 is a reply to message #578593] Fri, 01 March 2013 12:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
Senior Member
You could use SQL object type:

CREATE OR REPLACE
  TYPE obj_type
    AS OBJECT(
              id number,
              val varchar2(10),
              dt date
             )
/

Type created.


Now you can:

SET SERVEROUTPUT ON
DECLARE
    TYPE T1 IS TABLE OF obj_type INDEX BY BINARY_INTEGER;
    T T1;
BEGIN
    FOR I IN 1..3 LOOP
      T(I) := obj_type(I,'val ' || i,sysdate + i);
    END LOOP;
    FOR J IN T.FIRST..T.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('ID = ' || T(J).id || ' val = ' || T(J).val || ' dt = ' || T(J).dt);
    END LOOP;
END;
/
ID = 1 val = val 1 dt = 02-MAR-13
ID = 2 val = val 2 dt = 03-MAR-13
ID = 3 val = val 3 dt = 04-MAR-13

PL/SQL procedure successfully completed.

SQL> 


SY.
Re: adding multiple columns to table type [message #578596 is a reply to message #578593] Fri, 01 March 2013 12:53 Go to previous message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Previous Topic: splitting data and store the data into columns
Next Topic: Alternate for multiple cursors
Goto Forum:
  


Current Time: Fri May 08 22:30:55 CDT 2026