How to do the following task in Oracle

oracle

I wish to declare a variable, and create a temp table, and insert some data into the table, and show the data. BTW, I don't what this done in a new stored procedure:

DECLARE 
   I NUMBER := 1;  

begin
  CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);
  WHILE I <= 10 LOOP
    INSERT INTO temp Values (I);
    I := I + 1;
  END LOOP;
end;
SELECT * FROM temp;
/

But it has a compiling error:

ORA-06550: line 5, column 7: PLS-00103: Encountered the symbol
"CREATE" when expecting one of the following: ( begin case declare
exit for goto if loop mod null pragma raise return select update while
with << continue close current delete fetch lock insert open
rollback savepoint set sql execute commit forall merge pipe purge

Thank you Marco. I change it to the following but still not working:

DECLARE 
   I NUMBER := 1;       
   global temporary table temp(CID NUMBER) on commit preserve rows;
begin
  WHILE I <= 10 LOOP
    INSERT INTO temp Values (I);
    I := I + 1;
  END LOOP;
  SELECT * FROM temp;
end;
/

ORA-06550: line 3, column 25: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; not null range default character

The following code does not work either. It looks I cannot place declare and create in the same code:

CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);
DECLARE 
   I NUMBER := 1;        
begin
  WHILE I <= 10 LOOP
    INSERT INTO temp Values (I);
    I := I + 1;
  END LOOP;
  SELECT * FROM temp;
end;
/

The error message: ORA-00911: invalid character

Best Answer

You should probably consider creating the Global Temp table once ... outside of this entire process ... creating it "on the fly" like this doesn't really make any sense.

Note also that the data is by default transaction based ... so those records you're inserting will tend to "dissappear" quite quickly (ie once you hit a commit).

  SQL> CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);

  Table created.

  SQL> insert into temp ( select level from dual connect by level <= 10);

  10 rows created.

  SQL> Select * from temp;

         CID
  ----------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

  10 rows selected.

  SQL> commit;

  Commit complete.

  SQL> Select * from temp;

  no rows selected

  SQL>

You don't need the PL/SQL block for this, and in fact creates more overhead than it's worth.

Related Question