Hosting Provided By

Re: [GENERAL] postgresql varchar[] data type equivalent in Oracle

From: Albe Laurenz <all(at)adv.magwien.gv.at>
Date: Tue Jun 26 2007 - 05:08:36 EDT

Murali Doss wrote:
>
> I like to know about postgresql varchar[ ] data type
> equivalent in Oracle.

The best I can think of is a VARRAY, though you cannot access it by index in SQL (you need a stored procedure or client API for that). Quite clumsy.

Example:

SQL> CREATE TYPE VARCHAR_A AS VARRAY(100) OF VARCHAR2(10); 2 /

Type created.

SQL> CREATE TABLE N(ID NUMBER(10,0) PRIMARY KEY, A VARCHAR_A); Table created.

SQL> INSERT INTO N VALUES (1, VARCHAR_A('one', 'two', 'three'));

1 row created.

SQL> SELECT A FROM N WHERE ID=1; A

VARCHAR_A('one', 'two', 'three')

SQL> SELECT X.* FROM N, TABLE(N.A) X WHERE ID=1; COLUMN_VALUE

one
two
three

Yours,
Laurenz Albe

---------------------------(end of broadcast)---------------------------

TIP 6: explain analyze is your friend Received on Tue Jun 26 05:10:15 2007

This archive was generated by hypermail 2.1.8 : Tue Jun 26 2007 - 05:20:01 EDT