Insert values into global temp table using PL/SQL

1
votes

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

2
sqldatabaseoracleplsqlsyntax-error

2 Answers

3
votes

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;
3
votes

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;