CODEWITHSUNDEEP
databasepostgresqloracle-databaseasp.net-web-apitypes
user14343802
user14343802

Reputation:

convert PostgreSQL column type `text[]` to Oracle

I have a table in PostgreSQL

CREATE TABLE DEPARTMENT(
    name            text,
    floor           text[]
);

sample data :

name = 'CSE', floor = ['1-A', 1-B', '2-A'];
name = 'EEE', floor = ['2-B', '3-A'];

I want to convert the table in Oracle, I need help for the second column. What is Oracle equivalent of TEXT[] ?

I have some requirements:

  1. (If Oracle has any) I want to make my array type string[] (I mean TEXT[]) . I don't want to have any scope of pushing INT into the array.
  2. (If possible) please try not to use raw(200) or byte(200), since I want my type strictly string.
  3. Suggest me also If I can make a array of VARCHAR2(200)

[ Note:

I used .NET Core for API before, So,

string name;
string[] floor;

was fine for PostgreSQL, I don't want to change my API code, I just want a string[] equivalent type for Oracle (or int[] based on when I need to use what) ]

Upvotes: 0

Views: 1739

Answers (2)

Justin Cave
Justin Cave

Reputation: 231781

You can declare a table in Oracle with a nested table as a column

create type string_tbl is table of varchar2(200);
/

create table department (
  name varchar2(10),
  floor string_tbl
)
nested table floor store as floor_tbl;
/

insert into department( name, floor )
  values( 'CSE', string_tbl( '1-A', '1-B', '2-A' ) );

It rarely makes sense to define a data model this way, however. It almost always makes more sense to have a separate floor table and a department_floor mapping table to map departments to floors (I assume that a single floor can have multiple departments). Particularly if you are trying to support multiple databases with the same code base. Supporting different dialects of SQL is hard enough without introducing things like nested tables which tend to have very different semantics in different databases.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143003

According to PostgresSQL to Oracle migration tools and services, text is a

Variable-length character data, ⇐ 1G

and is to be "migrated" to CLOB datatype in Oracle.

Though, values you presented are way smaller so I guess that VARCHAR2 might do, e.g. varchar2(100).

Upvotes: 0

Related Questions