CODEWITHSUNDEEP
oracle-databasesqlplusoracle12c
Gryu
Gryu

Reputation: 2189

Can I declare local temporary table in Oracle 12c

I'm trying to declare local temporary table using the example from Oracle / PLSQL: LOCAL TEMPORARY TABLES. But when I try to insert it into sqlplus' CLI and hit Enter, it does not execute anything and I don't know what to do next to complete the command except pressing Ctrl+C interrupting command inputing:

SQL> DECLARE LOCAL TEMPORARY TABLE suppliers_temp
( supplier_id number(10) NOT NULL,
  supplier_name varchar2(50) NOT NULL,
  contact_name varchar2(50)
);  2    3    4    5
  6
  7  ;
  8  ^C

To execute this query I've logged in as SYSTEM user.

Why this example does not work for me?

Upvotes: 1

Views: 8441

Answers (1)

Boneist
Boneist

Reputation: 23578

local temporary tables aren't a thing in the Oracle RDBMS. Instead, you can have a Global Temporary Table (GTT) (which creates a permanent table, but the data is held at session level) or, introduced in 18c, you can have a Private Temporary Table (PTT) (the table definition and data are held at session level).

Both are similar to the standard create table statement, so to create a GTT that drops the rows when you commit, you would do something like:

create global temporary table table_name (col1 number, col2 varchar2(20))
   on commit delete rows;

Upvotes: 5

Related Questions