CODEWITHSUNDEEP
oracle-databasetypes
user2999593
user2999593

Reputation:

Oracle: Insert into a table type

CREATE OR REPLACE TYPE tabela is TABLE OF number;

I created this table and now I want to insert values so that I can return it in a function. How do I insert into this kind of table?

Upvotes: 1

Views: 4059

Answers (1)

MT0
MT0

Reputation: 168681

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE OR REPLACE TYPE tabela is TABLE OF number;

CREATE TABLE test ( val ) AS
          SELECT 1 FROM DUAL
UNION ALL SELECT 2 FROM DUAL
UNION ALL SELECT 4 FROM DUAL
UNION ALL SELECT 6 FROM DUAL;

Query 1:

-- In SQL
SELECT tabela( 1, 2, 4, 6 ) FROM DUAL

Results:

| TABELA(1,2,4,6) |
|-----------------|
|         1,2,4,6 |

Query 2:

-- In SQL, getting value from a table
SELECT CAST( COLLECT( val ) AS tabela ) AS vals FROM test

Results:

|    VALS |
|---------|
| 1,2,4,6 |

Query 3:

-- In PL/SQL
DECLARE
  t tabela := tabela();
BEGIN
  t.EXTEND(4);
  t(1) := 1;
  t(2) := 2;
  t(3) := 4;
  t(4) := 6;
  -- Do stuff with t.
END;

Query 4:

-- In PL/SQL, getting values from a table:
DECLARE
  t tabela;
BEGIN
  SELECT val
  BULK COLLECT INTO t
  FROM test;
  -- Do stuff with t.
END;

Upvotes: 2

Related Questions