PL/SQL: Creation and usage of a temporary table in script

Question

On an Oracle 19c DB I need to sample on customers whilst keeping a uniorm distribution to model the moving of customers. There are (on purpose) multiple rows with the same customers but changeing adderssses to model moving. Thus, for the sampling i need to group first.

Now what I got is that for the SAMPLE clause the source has to be materialized. So in the PL/SQL script I generate a temporary table that i want to use afterwards with SAMPLE. But even a simple SELECT INTO afterwards doens't work.

set SERVEROUT on;
DECLARE
    v_cust_name VARCHAR2(100);
    cmd_creation VARCHAR2(500):='CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_cust AS(
        SELECT cust_id, MIN(name) as name
        FROM customers
        GROUP BY cust_id)';
BEGIN
    
    EXECUTE IMMEDIATE cmd_creation;
    dbms_output.put_line('temp created');
    
    SELECT name 
    INTO v_cust_name
    FROM (SELECT * 
        FROM ORA$PTT_temp_cust SAMPLE(5))
    WHERE ROWNUM =1;
        
    EXECUTE IMMEDIATE 'DROP TABLE ORA$PTT_temp_cust';
    dbms_output.put_line('temp dropped');
END;

What I get is this

ORA-06550: line 15, column 18:
PL/SQL: ORA-00942: table or view does not exist

The table gets created. That far I got when I only executed the String for the creation and nothing else. Then I can access the table in a desired script from a different point. Does this have to do with the compiling? Is there some different way to solve this?

In addition, there is no reason to create a "temporary" table on the fly. Don't treat oracle like it is mssql. Look into global temporary table — EdStevens
– EdStevens, Commented Dec 7, 2021 at 14:44
The OP is using PRIVATE TEMPORARY table @EdStevens that was introduced in Oracle to satisfy the habit of the people used to do this in other databases;) — Marmite Bomber
– Marmite Bomber, Commented Dec 7, 2021 at 16:21
@MarmiteBomber - true, but that doesn't make it the best approach. If the OP would create a GTT (one time) then his problems with the PT table would simply cease to exist, and he wouldn't have to resort to evil dynamic SQL and all of the other attendant problems. — EdStevens
– EdStevens, Commented Dec 7, 2021 at 16:45
@EdStevens gonna try a go with GTT then, thanks for the hint! — fero
– fero, Commented Dec 14, 2021 at 11:19

Marmite Bomber · Accepted Answer · 2021-12-07 10:43:48Z

Your PL/SQL Block simple does not compile as the table you query does not exists at the time of compilation.

You must perform event the query with execute immediate

Simplified Example

DECLARE
    v_cust_name VARCHAR2(100);
    cmd_creation VARCHAR2(500):='CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_cust  AS select * from dual';

BEGIN
    
    EXECUTE IMMEDIATE cmd_creation;
    dbms_output.put_line('temp created');
    
    EXECUTE IMMEDIATE 'SELECT DUMMY  FROM ORA$PTT_temp_cust' INTO v_cust_name;
    dbms_output.put_line('name' || v_cust_name);
        
    EXECUTE IMMEDIATE 'DROP TABLE ORA$PTT_temp_cust';
    dbms_output.put_line('temp dropped');
END;
/

An other caveat the can lead to ORA-00942: table or view does not existin your setup is that you performs commit in your script.

The default definition of the ON COMMIT clause is DROP DEFINITION so you must use

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_cust 
ON COMMIT PRESERVE DEFINITION 
...

Littlefoot · Accepted Answer · 2021-12-07 10:24:46Z

Dynamic SQL is evil. The fact that you created the table using dynamic SQL (your 1st execute immediate) doesn't mean that Oracle "predicts" you'll actually succeed with that and "presume" that statements that follow are correct. Nope - that table doesn't exist yet, so everything has to be moved to dynamic SQL.

Something like this (disregard changes in table and column names I used and global vs. private temporary table; this is 11gXE):

SQL> DECLARE
  2     v_cust_name   VARCHAR2 (100);
  3     cmd_creation  VARCHAR2 (500)
  4                      := 'CREATE global TEMPORARY TABLE PTT_temp_cust AS
  5          SELECT empno, MIN(ename) as name
  6          FROM emp
  7          GROUP BY empno';
  8  BEGIN
  9     EXECUTE IMMEDIATE cmd_creation;
 10
 11     EXECUTE IMMEDIATE '
 12      SELECT max(name)
 13      FROM (SELECT *
 14          FROM PTT_temp_cust SAMPLE(5))
 15      WHERE ROWNUM = 1'
 16        INTO v_cust_name;
 17
 18     EXECUTE IMMEDIATE 'DROP TABLE PTT_temp_cust';
 19
 20     DBMS_OUTPUT.put_line ('Result = ' || v_cust_name);
 21  END;
 22  /
Result =

PL/SQL procedure successfully completed.

SQL>

I got no result, though - but you should (at least, I hope so).

Collectives™ on Stack Overflow

PL/SQL: Creation and usage of a temporary table in script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related