get the first value of a type, oracle sql

Question

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.

Not sure what your asking, what are you trying to achieve? maybe add a sample output — Matt, May 19 '15 at 10:58

Dmitry.Samborskyi · Answer 1 · 2015-05-19T11:16:42.583

1

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')));

edited May 19 '15 at 11:16

answered May 19 '15 at 11:09

Dmitry.Samborskyi

485
2
10

first: i had some misstakes in my first post, which are correct now. iam not sure if this is right. i insert in my table some datasets of type "type1" – E. C. May 19 '15 at 11:34
1

you could try. select * from test, table(owntype) where ... and define needed value. – Dmitry.Samborskyi May 19 '15 at 11:47
pls look my new answer – E. C. Jun 01 '15 at 09:50

score 0 · Answer 2 · answered Jun 01 '15 at 09:50

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?

get the first value of a type, oracle sql

2 Answers2