CODEWITHSUNDEEP
oracle-databaseoracle11g
Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

How to insert data into TABLE type variable

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

Upvotes: 12

Views: 38018

Answers (3)

cyn
cyn

Reputation: 858

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;

Upvotes: 15

abhi
abhi

Reputation: 3136

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;

Upvotes: 0

Husqvik
Husqvik

Reputation: 5809

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;

Upvotes: 1

Related Questions