Oracle PL/SQL Creating tables in cursor?

Question

I'm relatively new to PL/SQL, so please bear with me. I'm creating two tables that will be used in the FOR loop cursor. My intention was to create these two tables at the start (before the cursor loop), truncate them inside the cursor loop whenever I need it cleared and insert new values, then drop them at after the cursor loop completes. (After looking a bit on SO, I think this can be done with REF CURSOR, but I'm not very clear on how to use that so I decided to go ahead with the first approach.)

I tried creating the tables in the DECLARE section, then in the BEGIN section before the loop starts, but it raises the error 'ORA-065500 and PLS-00103 Encountered the symbol CREATE when expecting begin function pragma ...' Note that I am dropping the tables after the LOOP ends but before the END section of the cursor.

Should I create the tables outside the cursor , before the DECLARE section, and drop them after the cursor END section? I thought it should be possible to create permanent oracle tables inside cursors?

Update: Posting code here-

1)

DECLARE
  CREATE TABLE T1
    (
      col1 VARCHAR2(128),
      col2  VARCHAR2(128),
      col3 NUMBER(3) NOT NULL,
      col3 FLOAT(100)
    );

  CREATE TABLE T2 AS
  SELECT * FROM other_table WHERE 1 = 0;

CURSOR CUR IS ...
BEGIN

       FOR rec IN CUR
       LOOP
       --Do stuff here
         END LOOP;

Drop table T1;
Drop table T2;
END;
/

2)

DECLARE

CURSOR CUR IS ...
BEGIN

  CREATE TABLE T1
    (
      col1 VARCHAR2(128),
      col2  VARCHAR2(128),
      col3 NUMBER(3) NOT NULL,
      col3 FLOAT(100)
    );

  CREATE TABLE T2 AS
  SELECT * FROM other_table WHERE 1 = 0;

  FOR rec IN CUR
  LOOP
  --Do stuff here
  END LOOP;

  Drop table T1;
  Drop table T2;
END;
/

(1) and (2) both don't work.

Update- Do we need EXECUTE IMMEDIATE for this? How do I know when I need EXECUTE IMMEDIATE? Do we also need EXECUTE IMMEDIATE for truncating tables in cursors?

what are you trying to solve that you need these intermediate tables? T2 for example could be a view — mucio
I join T2 with other_table later to delete common rows from other_table, so I think it cannot be a view? Can you confirm this? — user2441441

Marcin Wroblewski Marcin Wroblewski · Accepted Answer · 2015-02-26T17:48:39

Usually you don't perform DDLs (create, alter, drop) inside your PL/SQL procedure. If you need a table to store some temporary data, you can create temporary tables for this purpose. In your case I would first create the tables

CREATE GLOBAL TEMPORARY TABLE T1
  (
    col1 VARCHAR2(128),
    col2  VARCHAR2(128),
    col3 NUMBER(3) NOT NULL,
    col3 FLOAT(100)
  );

CREATE GLOBAL TEMPORARY TABLE T2 AS
SELECT * FROM other_table WHERE 1 = 0;

And then the procedure would look like this

DECLARE
CURSOR CUR IS ...
BEGIN
       FOR rec IN CUR
       LOOP
       --Do stuff here
         END LOOP;

DELETE FROM T1;
DELETE FROM T2;
END;
/

Of course the tables would not be dropped after that, but I suppose you want to use your PL/SQL procedure regularly, not only once, right?

If you still want to peform DDL in your procedure, then you must use dynamic sql (execute immediate). However you have to be aware that DDL operations perform implicit commits, so your procedure would not be a single atomic transaction.

Oracle PL/SQL Creating tables in cursor?

1 Answers