Declare a Table Variable in PLSQL Oracle 19c

Question

How do I declare a variable in PL/SQL that is a table? I don't want a new table in the database. I want a variable that is declared at the beginning of my session and is automatically destroyed at the end.

I have created the following custom types obj_gifts & tbl_gifts

Create Or Replace Type obj_gifts as object (gifts_key number, gift_name vachar(100));

Create Or Replace Type tbl_gifts Is Table Of obj_gifts

---

I want something akin to the following

Declare gifts tbl_gifts

Insert Into gifts Values (1,'toy')

Select * from gifts

---

What I DO NOT WANT is to create a new table named gifts just to run my query.

Answer 1

You don't need to create a type.

Just use the DUAL table if you want a query to output those values:

SELECT 1 AS gifts_key, 'toy' AS gift_name FROM DUAL;

If you did want to create a type then:

SELECT *
FROM   TABLE(tbl_gifts(obj_gifts(1, 'toy')));

And, if you want to use PL/SQL then:

DECLARE
  gifts tbl_gifts;
BEGIN
  gifts := tbl_gifts();
  gifts.EXTEND;
  gifts(gifts.COUNT) := obj_gifts(1, 'toy');

  FOR i IN 1 .. gifts.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(gifts(i).gifts_key || ', ' || gifts(i).gift_name);
  END LOOP;
END;
/

---

I want a variable that is declared at the beginning of my session and is automatically destroyed at the end.

That sounds like you may want a table; a private temporary table.

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_GIFTS
(
  gifts_key number,
  gift_name varchar2(100)
)
  ON COMMIT PRESERVE DEFINITION;

Then you can insert into the table, select from it and it will be destroyed at the end of the session.

fiddle