Skip to Main Content
  • Questions

  • SQL Server migration: temporary tables

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, naveen.

Asked: August 17, 2017 - 9:29 am UTC

Last updated: August 17, 2017 - 2:19 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

within a stored procedure, I need a way to store intermediate results from a query. The idea is that I select some data, store them (in a variable), and later in my SP/CB I use them in other queries.
In MS SQL a temporary table would do just fine, but in Oracle it is not an option since in Oracle temporary table are created at schema-level.
then what should i use to achieve this type of situation in oracle?

and Chris said...

Why are Oracle temporary tables not an option? Will the columns you're selecting change at runtime?

If no, make your temporary table to match the query output and select into that. I'm not overly familiar with temp tables in MSSQL, but it seems to me this will do the trick.

Alternatively, you could load the results into an array for later processing using bulk collect. Just declare an array with a type matching the shape of the query. For example:

declare
  cursor cur is 
    select rownum x from dual
    connect by level <= 5;
  
  type cur_tab is table of cur%rowtype index by binary_integer;
  rws  cur_tab;
begin
  open cur;
  fetch cur bulk collect into rws;
  
  for i in 1 .. rws.count loop
    dbms_output.put_line(rws(i).x);
  end loop;
  close cur;
end;
/

1
2
3
4
5

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what kind of temp table in SQL Server

Jim Berg, August 17, 2017 - 1:45 pm UTC

I develop in both SQL Server and Oracle by necessity. I suspect that the poster is using multiple databases in SQL Server and is creating global temporary tables to make the data available outside the session that creates the temporary table. I would suggest that he create permanent tables to hold this data and truncate the tables at the start of the process. Depending on how many rows he is writing, one or more indexes may be beneficial.
And suggesting a cursor to a SQL Server developer needs a long explanation on the difference between locking in SQL Server and Oracle.
The actual question is really too vague to answer in any other way than it depends on what you are trying to do.
Chris Saxon
August 17, 2017 - 2:19 pm UTC

The actual question is really too vague to answer in any other way than it depends on what you are trying to do.

Well said.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.