How to store the result of select statement into the temporary table in Oracle?

Question

We can write select column1,column2 into #temp from tableName in SQL Server. But I am unable to write the same query in an Oracle database.

I want to store the result of select/insert/delete/update or any result set into a local temporary table in oracle database. How I can do this?

I am executing below query in my Oracle sql developer tool:

select * into #temp 
from bmi;

but I am getting the error as follow please help to find this error.

when I execute the same query in Microsoft SQL Server it get executed & #temp table get created which is not present in the database but it can hold the data for that particular session. so i want same scenario in ORACLE database.

ORA-00911: invalid character 00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. *Action: Error at Line: 1 Column: 15

Solution 1

I want to store the result of select/insert/delete/update or any result set into a local temporary table in oracle database,How I can Do This?

You can't. Oracle doesn't have local temporary tables, it doesn't work like that. But it doesn't need to. Oracle has a very different internal model from SQL Server which means a lot of SQL Server practices are unnecessary in Oracle. (To be fair SQL Server has neat things which Oracle doesn't, like ANSI 92 Joins for DML.)

The key insight is: you don't want to store the result of select/insert/delete/update or any result set into a local temporary table. That is something you had to do in T-SQL to achieve the end goal of implementing some business logic. But what you actually wanted to do in SQL Server and what you want to do in Oracle is write some code which delivers value to your organisation.

So, with that mindset in place, what do you need to do?

If you want to loop round a result set then perhaps a Cursor Loop is what you're looking for?

for rec in ( select * from some_table
             where the_date = date '2018-02-01' )
loop
    ...

If you want to work on some data prior to inserting it into a data then perhaps you should use a PL/SQL collection:

type l_recs is table of some_table%rowtype;

But maybe you just need to understand Oracle's Transaction Management model. A lot of things are possible in pure SQL without any need for procedural framework.

Solution 2

Create temporary table :

create global temporary table 
results_temp (column1, column2)
on commit preserve rows;

and then insert to it from your table:

insert into results_temp (column1, column2 )
SELECT column1,column2 
FROM source_table

Comments

[venkatesh]

#temp is only for sql server to store temp info for oracle ther is a global temp table for current session it is presistant definition but not data , the data in global temp table will not be cleared or roll back

[venkatesh]

In Oracle a global temporary table is a permanent table. The only thing that is temporary in them is the data. Only the session that inserts data into them can see it and the data is automatically removed when the session ends.

[a_horse_with_no_name]

You typically don't need temporary tables in Oracle the way you need them in SQL Server. Just use a derived table or sub-select.

[William Robertson]

PL/SQL's select into is for populating scalar variables, e.g. select max(salary) into l_salary from employees would populate scalar variable l_salary with the value of the highest salary. As the error message says, variable names can't start with #.

[sandesh jogi]

Hi user7294900,

[sandesh jogi]

I don't want to create the table I just want to store the result in temp table for further use in the same session. thanks......

[user7294900]

so use the second SQL, a simple insert into using select

[sandesh jogi]

Hi, Thanks for your answer but when I execute your query the table is get created but I don't want to create the the table i just want the temporary table holding the result of query & i want to use that temporary table in fruther use in the same session. I am using ORACLE database. without creating the table I want to store the result in temporary table.

[sandesh jogi]

can you please see my question that i was edited eariler, so that i will get answer from you.

[sandesh jogi]

can you please see my question that i was edited eariler, so that i will get answer from you. thanks.....

[venkatesh]

A GLOBAL TEMPORARY table has a persistent definition but data is not persistent and the global temporary table generates no redo or rollback information. For example if you are processing a large number of rows, the results of which are not needed when the current session has ended, you should create the table as a temporary table instead: create global temporary table results_temp (...) on commit preserve rows;

[user7294900]

don't use #, just write the name of the temp table (you must create it if it's not exists) as select * into temp from bmi

[a_horse_with_no_name]

@sandeshjogi: the only way to get a temporary table is to create one

[GGO]

Please explain what you did. Avoid only code answer

[Carlo Sirna]

Oracle 18, which has been released very recently DOES support what it calls "PRIVATE TEMPORARY TABLE" which are exactly tables that do exist only for the duration of a transaction (or of a session, optionally). the new syntax is described here: docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/‌​…

[APC]

@carlosirna - very interesting, thanks for the tip. In my experience very few organisations run on the latest release of Oracle (or any enterprise platform). In fact many places are still running on the unsupported and deprecated 11g, or older.

[William Robertson]

I came across that too in the New Features guide. But unless I am missing something it still requires dynamic SQL, so all you gain from it is the ability to create a temp table with a fixed name and not conflict with other sessions. You could do the same thing in 11g using a generated table name like 'MY_GTT#'||sys_context('userenv','sessionid').