Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: August 27, 2018 - 7:01 pm UTC

Last updated: November 06, 2019 - 1:18 am UTC

Version: 18c

Viewed 10K+ times! This question is

You Asked

Hi All,

I'm a recent convert from PL/SQL to SQL Developer & Oracle. As such, I used #temptables a ton and was highly discouraged from writing 'super SQL' scripts that tried to do everything in one query.

Now, I'm learning that just the opposite is true in Oracle. I have no problem with that, I'm just confused as to why temptables wouldn't be utilized as they were very beneficial for developing logic in multi-step, complex queries. Jamming everything into a single query seems odd and will make writing the initial scripts somewhat more difficult. I provided a small example below.

select patient, patientAGE, DOB
into #tmp1
from Patients

select patient, diabetesDX, DxDate
into #tmp2
from diagnoses

select patient, A1Clvl, MAX(dateoflab) as DOL
into #tmp3
from labs
where A1C >= 9
GROUP BY patient, A1Clvl

select t1.*, t2.A1Clvl, t2.DOL, t3.A1Clvl, DOL 
from #tmp1 t1
   LEFT JOIN #tmp2 t2 ON t2.patient = t1.patient
   LEFT JOIN #tmp3 t3 ON t3.patient = t3.patient


All input appreciated.

-John

and Connor said...

You have a couple of options here. We have private temporary tables in 18c, which can be used in the same way as #temp tables - you just need to define them as you go. The cost of doing so is basically nil because they live entirely in your session. Very simple demo here



But the other alternative is the WITH clause which lets you keep the flow similar to temporary tables but still have a single query. Your example above would be:

with tmp1 as (
select patient, patientAGE, DOB
from Patients),
tmp2 as (
select patient, diabetesDX, DxDate
from diagnoses),
tmp3 as (
select patient, A1Clvl, MAX(dateoflab) as DOL
from labs
where A1C >= 9
GROUP BY patient, A1Clvl
)
select t1.*, t2.A1Clvl, t2.DOL, t3.A1Clvl, DOL 
from tmp1 t1
   LEFT JOIN tmp2 t2 ON t2.patient = t1.patient
   LEFT JOIN tmp3 t3 ON t3.patient = t3.patient


Rating

  (4 ratings)

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

Comments

Very helpful and clear

John Commissaris, August 28, 2018 - 1:23 pm UTC

Great review from Connor, was exactly what I needed.
Connor McDonald
August 29, 2018 - 6:48 am UTC

glad we could help

How about PL/SQL Scope

Rajeshwaran, Jeyabal, November 05, 2019 - 10:28 am UTC

Is it not possible to have Private temp tables in PL/SQL scope ?

c##rajesh@PDB1> variable x number
c##rajesh@PDB1> begin
  2     execute immediate ' create private temporary table ORA$PTT_t(x number) on commit preserve definition; ';
  3     select count(*) into :x from ORA$PTT_t;
  4  end;
  5  /
        select count(*) into :x from ORA$PTT_t;
                                       *
ERROR at line 3:
ORA-06550: line 3, column 33:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 2:
PL/SQL: SQL Statement ignored


c##rajesh@PDB1> create private temporary table ORA$PTT_t(x number) on commit preserve definition;

Table created.

c##rajesh@PDB1> begin
  2     select count(*) into :x from ORA$PTT_t;
  3  end;
  4  /

PL/SQL procedure successfully completed.

c##rajesh@PDB1> print x

        X
---------
        0

c##rajesh@PDB1>

Connor McDonald
November 06, 2019 - 1:18 am UTC

This is not a private temp table issue, but a compilation issue. To compile PLSQL, the objects need to already exist. If you want to create a PTT dynamically, then you need to query it dynamically as well

SQL> declare
  2    v int;
  3  begin
  4         execute immediate 'create table blah (x int)';
  5         select count(*) into v from blah;
  6  end;
  7  /
       select count(*) into v from blah;
                                   *
ERROR at line 5:
ORA-06550: line 5, column 36:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 8:
PL/SQL: SQL Statement ignored


SQL>
SQL> declare
  2    v int;
  3  begin
  4         execute immediate 'create table blah (x int)';
  5         execute immediate 'select count(*) from blah' into v;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL>


How about PL/SQL Scope

Rajeshwaran Jeyabal, November 06, 2019 - 2:50 am UTC 

  4         execute immediate 'create table blah (x int)';
  5         select count(*) into v from blah;


how it possible for you to create a private temporary table like this?

it should start with "create private temporary table .." like right?

and what was the value for this parameter "private_temp_table_prefix" at your session?

How about PL/SQL Scope

Rajeshwaran Jeyabal, November 06, 2019 - 3:01 am UTC

Sorry, Ignore my above post, got it now. thanks.
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 latest video and Chris's latest video 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.