CODEWITHSUNDEEP
sqloracle-databaseselecttypesvarray
E. C.
E. C.

Reputation: 31

get the first value of a type, oracle sql

I have following:

CREATE OR REPLACE TYPE mem_type2 IS VARRAY(2) of VARCHAR2(10);
CREATE TABLE test(owntype type1);
INSERT INTO test VALUES (mem_type2('0','A'));  
INSERT INTO test VALUES (mem_type2('1','B'));

so my table look like:

.MEM_TYPE2('Beryl','1')
.MEM_TYPE2('Fred','2')
.MEM_TYPE2('a','3')
.MEM_TYPE2('b','4')
.MEM_TYPE2('c','5')
.MEM_TYPE2('d','6')
.MEM_TYPE2('e','7')
.MEM_TYPE2('f','8')
.MEM_TYPE2('g','9')
.MEM_TYPE2('h','10')
.MEM_TYPE2('i','11')

I want some-think like this: (to get the first value(second...)

select test.owntype(1) // second value

but thats ofcourse wrong, how it is right? i cant find it. i search only sql, if possible, anyone a idea?

The final target is following: I have to Table like this on above. And i want update the first by the second. Id is the primary key.

Upvotes: 2

Views: 274

Answers (2)

E. C.
E. C.

Reputation: 31

ok i find a solution for the way i want origin: https://community.oracle.com/thread/360862

But i found out, that this way is not the best. So i start to do it like Dmitry Samborskyi

But i have a Problem: If i do following i get a answer which isnt what i want.

CREATE OR REPLACE TYPE mem_type AS OBJECT (idmt number, v1 varchar2(20),v2 varchar2(15));
select eric.mem_type(1,'a','b') from dual;

Result:

ERIC.MEM_TYPE(

ofcourse i want 

ERIC.MEM_TYPE('1','a','b')

Where is my mistake?

Upvotes: 0

Dmitry.Samborskyi
Dmitry.Samborskyi

Reputation: 485

If I understood you correctly you need type

CREATE OR REPLACE TYPE type1 IS OBJECT (id varchar2(2), val varchar2(10));
CREATE OR REPLACE TYPE type2 IS table of type1;

and then you can put data in such way

CREATE TABLE test(owntype type2);
INSERT INTO club VALUES (type2 (type1 ('0','A')));  
INSERT INTO club VALUES (type2 (type1 ('1','B')));

Upvotes: 1

Related Questions