How to insert data into TABLE type variable

Question

I'm expecting a lot of downvotes, since I think this is a silly question, but here it goes:

I'm attempting at inserting data into a TABLE TYPE variable. I have a pre-existing TABLE TYPE, defined as:

create or replace type m_code_object
 is object (m_code varchar2(25 char));

create or replace type m_code_tabletype
 is table of m_code_object;

and the table that I want to define based on this and insert data into, is below:

declare
    vtable m_code_tabletype;
begin
    insert into vtable values ('a');
    insert into vtable values ('b');
end;

Now, when running this in SQL Developer I get PL/SQL: ORA-00942: table or view does not exist for both rows.

My understanding was that this is a table variable of type m_code_tabletype and that it's enough to declare it in a block before trying to insert data into it.

Can anyone please explain what I'm doing wrong or what's missing from my understanding?

Thanks

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS00504 — , Aug 25 '15 at 14:21
@a_horse_with_no_name So, this would be considered a collection? (or actually it is?). Can I have this variable sent as a parameter to a stored procedure? — Radu Gheorghiu, Aug 25 '15 at 14:22
@RaduGheorghiu You can also look up Tom Kyte's Q&A here -> https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14334298866128 — abhi, Aug 25 '15 at 15:04

score 14 · Answer 1 · answered Oct 03 '16 at 12:17

This is how you populate a table type of objects :

declare
    vtable m_code_tabletype := m_code_tabletype();
begin
    vtable.extend;
    vtable(vtable.count) := m_code_object('a');
    vtable.extend;
    vtable(vtable.count) := m_code_object('b');
end;

score 1 · Answer 2 · answered Aug 25 '15 at 13:51

1

DECLARE
    c_varray SYS.ODCIVARCHAR2LIST;
BEGIN
    c_varray := SYS.ODCIVARCHAR2LIST();
    c_varray.EXTEND(2);
    c_varray(1) := '1';
    c_varray(2) := '2';
END;

Or

DECLARE
    c_varray SYS.ODCIVARCHAR2LIST;
BEGIN
    SELECT LEVEL BULK COLLECT INTO c_varray FROM DUAL CONNECT BY LEVEL <= 10;
END;

answered Aug 25 '15 at 13:51

Husqvik

5,669
1
19
29

I need this saved into a user defined table type, not a varray. As far as I know the varray is bounded, while a table is an "unbounded array". So, I would have to know the size of the array before inserting. – Radu Gheorghiu Aug 25 '15 at 13:54
It works the same for nested table. Only associative arrays behave differently – Husqvik Aug 25 '15 at 13:56
Though bounded, you can still use the .EXTEND() method to increase the size of the array. – abhi Aug 25 '15 at 14:50

score 0 · Answer 3 · edited May 23 '17 at 11:48

0

There is a detailed explanation on table types here.

Try this:

 declare
    vtable m_code_tabletype;
    vrow m_code_object;     
begin
    vrow := m_code_object('a');
    vtable := m_code_tabletype(vrow);
    abs_test(vtable);
end;

In order to pass a table type to a stored procedure:

Create or replace procedure abs_test(mct IN m_code_tabletype)
AS
BEGIN
    FOR i in 1..mct.count
    LOOP
        dbms_output.put_line('hello' || to_char(mct.count));
    END LOOP;
END;

edited May 23 '17 at 11:48

Community

1
1

answered Aug 25 '15 at 14:18

abhi

3,082
6
47
73

Can I have this table sent to a procedure as parameter? – Radu Gheorghiu Aug 25 '15 at 14:21
My understanding is that yet. I will edit my response. – abhi Aug 25 '15 at 14:26

How to insert data into TABLE type variable

3 Answers3