Re: table type

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Thu, 10 Jun 2004 13:23:27 +0100
Message-ID: <40C852BF.8020205@orindasoft.com>

Christian "Raistlin" Gulich wrote:
> Hallo,
> how can I create a table type from an existing table? It must have the same
> attribute names because I want to use it as return type for a PL/SQL
> procedure, which does something on the original table.
> I tried something like this, but it didn't work.
>
> CREATE TYPE TESTER.Videos_TableType AS
> TABLE OF TESTER.Videos%ROWTYPE
>
> Do you have any ideas?
>

There is no easy and foolproof way to do this. You could do something like this:

SQL> desc bookings

  Name                                      Null?    Type
  ----------------------------------------- -------- 
----------------------------
  CUSTOMER_NAME                             NOT NULL VARCHAR2(12)
  AIRLINE_NAME                              NOT NULL VARCHAR2(50)
  FLIGHT_NUMBER                             NOT NULL NUMBER
  DEPARTURE_TIME                            NOT NULL DATE
  SEAT                                      NOT NULL VARCHAR2(3)

select
decode(a.column_id,1,' ',',')||a.column_name||' '||a.data_type

||decode(a.data_type,'VARCHAR2', '('||a.data_length||')'
                     ,'VARCHAR', '('||a.data_length||')'
                     ,'CHAR', '('||a.data_length||')'
                     ,'')

from user_tab_columns a
where table_name = 'BOOKINGS'
/

DECODE(A.COLUMN_ID,1,'',',')||A.COLUMN_NAME||''||A.DATA_TYPE||DECODE(A.DATA_TYPE

---

  CUSTOMER_NAME VARCHAR2(12)

,AIRLINE_NAME VARCHAR2(50)
,FLIGHT_NUMBER NUMBER
,DEPARTURE_TIME DATE
,SEAT VARCHAR2(3)

This will give you the fields 'inside' your table. You can then add text   on either side to turn it into a record:

CREATE TYPE BOOKINGS_TYPE AS OBJECT
(CUSTOMER_NAME VARCHAR2(12)

,AIRLINE_NAME VARCHAR2(50)
,FLIGHT_NUMBER NUMBER
,DEPARTURE_TIME DATE
,SEAT VARCHAR2(3));

CREATE TYPE BOOKINGS_ARRAY AS TABLE OF BOOKINGS_TYPE; David Rolfe
Orinda Software
Dublin, Ireland

---

Orinda Software make OrindaBuild, which writes JDBC calls for you. Received on Thu Jun 10 2004 - 07:23:27 CDT