Create a temp table in PL/SQL

Question

I'm working with an Oracle 10g database, and I want to extract a group of records from one table, and then use that for pulling records out of a bunch of related tables.

If this were T-SQL, I'd do it something like this:

CREATE TABLE #PatientIDs (
  pId int
)

INSERT INTO #PatientIDs
  select distinct pId from appointments

SELECT * from Person WHERE Person.pId IN (select pId from #PatientIDs)

SELECT * from Allergies WHERE Allergies.pId IN (select pId from #PatientIDs)

DROP TABLE #PatientIDs

However, all the helpful pages I look at make this look like a lot more work than it could possibly be, so I think I must be missing something obvious.

(BTW, instead of running this as one script, I'll probably open a session in Oracle SQL Developer, create the temp table, and then run each query off it, exporting them to CSV as I go along. Will that work?)

Answer 1

Oracle has temporary tables, but they require explicit creation:

create global temporary table...

The data in a temporary table is private for the session that created it and can be session-specific or transaction-specific. If data is not to be deleted until the session ends, you need to use ON COMMIT PRESERVE ROWS at the end of the create statement. There's also no rollback or commit support for them...

I see no need for temp tables in the example you gave - it risks that updates made to the APPOINTMENTS table since the temp table was populating won't be reflected. Use IN/EXISTS/JOIN:

SELECT p.* 
  FROM PERSON p
 WHERE EXISTS (SELECT NULL
                 FROM APPOINTMENTS a
                WHERE a.personid = a.id)

SELECT p.* 
  FROM PERSON p
 WHERE p.personid IN (SELECT a.id
                        FROM APPOINTMENTS a)

SELECT DISTINCT p.* 
  FROM PERSON p
  JOIN APPOINTMENTS a ON a.id = p.personid

JOINing risks duplicates if there are more than one APPOINTMENT records associated to a single PERSON record, which is why I added the DISTINCT.