1

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

  • Please edit your question instead of adding replies as answers. See the help center and take the tour for more information about how this site works, thank you. – Paul White Dec 24 '15 at 0:57
4

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.

0

Before execution the WHOLE PL/SQL block is parsed - all objects used in an PL/SQL block must exist BEFORE the PL/SQL block is executed...

(source: https://stackoverflow.com/questions/7858902/unable-to-create-table-using-pl-sql)

You can't create a table and then use it within the same PL/SQL block. You have to create it first, then use it later. I can't think of any good reason why you'd do what you're trying to do.

If your data will be created and consumed within the same session, just create a global temporary table, and use that instead of the overhead of creating and dropping a table every time this runs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.