1

I am struggling with migrating the temp tables (SQL server) to oracle. Mostly, oracle don't consider to use temporary table inside the store procedure but in sql server, they are using temp tables for small fetching record and also manipulate same. How to overcome this issue. I am also searching some online articles about migrating temp table to oracle but they are not clearly explained for my expectations. i got information like using inline view, WITH clause, ref cursor instead of temp table. I am totally confused. Please suggest me, in which case may use Inline view, WITH clause, ref cursor. This may be helpful for improve my knowledge and also doing job well.

As always thank you for your valuable time in helping out the newbies. Thanks Alsatham hussain

Hussain
  • 23
  • 3
  • Also look at collections [[1](https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#LNPLS00501), [2](http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42plsql-1653077.html)] if you are using the same data within multiple queries in the same procedure. – MT0 Mar 17 '17 at 11:38
  • 1
    Oracle does have temp tables. Check this [similar question](http://stackoverflow.com/questions/2671518/how-to-create-a-temporary-table-in-oracle). – Panagiotis Kanavos Mar 17 '17 at 11:41
  • In most cases you don't actually need temp tables in Oracle. It usually more efficient to just do it in a single statement (In Oracle readers don't block writers and writers don't block readers so it's usually no necessary to copy data around to "manipulate" it). But as you have not shown _any_ code that you are having problem with, this is impossible to answer. –  Mar 17 '17 at 12:04

1 Answers1

0

Like many questions, the answer is "it depends". A few things

  1. Oracle's "temp" table is called a GLOBAL TEMPORARY TABLE (GTT). Unlike most other vendor's TEMP tables, their definition is global. Scripts or programs in SQL Server (and others), will create a temp table and that temp table will disappear at the end of a session. This means that the script or program can be rerun or run concurrently by more than one user. However, this will not work with a GTT, since the GTT will remain in existence at the end of the session, so the next run that attempts to create the GTT will fail because it already exists. So one approach is to pre-create the GTT, just like the rest of the application tables, and then change the program to INSERT into the gtt, rather than creating it.

  2. As others have said, using a CTE Common Table Expression) could potentially work, buy it depends on the motivation for using the TEMP table in the first place. One of the advantages of the temp table is it provides a "checkpoint" in a series of steps, and allows for stats to be gathered on intermediate temporary data sets; it what is a complex set of processing. The CTE does not provided that benefit.

  3. Others "intermediate" objects such as collections could also be used, but they have to be "managed" and do not really provide any of the advantages of being able to collect stats on them.

So as I said at the beginning, you choice of solution will depend somewhat on the motivation for the original temp table in the first place.

BobC
  • 4,208
  • 1
  • 12
  • 15