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

2
votes

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

3
sqloracleplsqltemp-tables
#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. – venkatesh
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. – a_horse_with_no_name
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 #. – William Robertson

3 Answers

4
votes

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.

0
votes

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
0
votes
create global temporary table temp_table_name
on commit preserve rows as select column1,column2,columnN from your_table;