CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqlsyntax-error
sjc725
sjc725

Reputation: 511

Insert values into global temp table using PL/SQL

I'm new to PL/SQL and I'd like to insert values into a global temp table using the following code:

CREATE GLOBAL TEMPORARY TABLE test_variable
(
only_datex TIMESTAMP(6) NULL,
only_datey TIMESTAMP(6) NULL
)
ON COMMIT PRESERVE ROWS;
DECLARE
    x TIMESTAMP(6) := CURRENT_DATE;
    y TIMESTAMP(6) := CURRENT_DATE - 1;
BEGIN 
     INSERT INTO test_variable VALUES(x,y);
END

SELECT * FROM test_variable;

After trying to select, I'm getting this error: enter image description here

Upvotes: 1

Views: 1923

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

you need a slash / just after END;

DECLARE
    x TIMESTAMP(6) := CURRENT_DATE;
    y TIMESTAMP(6) := CURRENT_DATE - 1;
BEGIN 
     INSERT INTO test_variable VALUES(x,y);
END;/

SELECT * FROM test_variable;

Upvotes: 3

Mureinik
Mureinik

Reputation: 311863

You need to terminate the pl/sql block - you're missing the semicolon (;) that goes with the end and a slash (/) to terminate the block:

CREATE GLOBAL TEMPORARY TABLE test_variable
(
only_datex TIMESTAMP(6) NULL,
only_datey TIMESTAMP(6) NULL
)
ON COMMIT PRESERVE ROWS;

DECLARE
    x TIMESTAMP(6) := CURRENT_DATE;
    y TIMESTAMP(6) := CURRENT_DATE - 1;
BEGIN 
     INSERT INTO test_variable VALUES(x,y);
END; -- Here
/ 
--^ And here

SELECT * FROM test_variable;

Upvotes: 3

Related Questions