Excute SELECT after CREATE in Oracle PL / SQL
I am new to Oracle PL / SQL although I have a lot of experience with SQL. I am currently trying to convert multiple T-SQL statements to PL / SQL. I am trying to execute the following code, but I am getting some errors.
If the table does not already exist, then the error is: The table or view does not exist. But when I run the query without the second select statement, it creates the table. Now that the table exists I tried to execute it again and now I get the following error:
An INTO clause is expected in the SELECT statement.
The code I'm using:
DECLARE
cnt NUMBER;
stmt VARCHAR2(1000) := 'CREATE TABLE LAST_LOG_ARCHIVE (LAST_LOG_ARCHIVE TIMESTAMP NULL)';
BEGIN
SELECT COUNT(*) INTO cnt FROM all_tables WHERE table_name = 'LAST_LOG_ARCHIVE';
IF (cnt = 0) THEN
EXECUTE IMMEDIATE stmt;
END IF;
SELECT COALESCE((
SELECT LAST_LOG_ARCHIVE FROM LAST_LOG_ARCHIVE WHERE ROWNUM = 1
), TO_TIMESTAMP('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AS LAST_LOG_ARCHIVE FROM dual;
END;
How can I solve this problem ??
source to share
Edit:
DECLARE
cnt NUMBER;
stmt VARCHAR2(1000) := 'CREATE TABLE LAST_LOG_ARCHIVE (LAST_LOG_ARCHIVE TIMESTAMP NULL)';
BEGIN
SELECT COUNT(*) INTO cnt FROM all_tables WHERE table_name = 'LAST_LOG_ARCHIVE';
IF (cnt = 0) THEN
EXECUTE IMMEDIATE stmt;
END IF;
SELECT COALESCE((
SELECT LAST_LOG_ARCHIVE FROM LAST_LOG_ARCHIVE WHERE ROWNUM = 1
), TO_TIMESTAMP('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AS LAST_LOG_ARCHIVE FROM dual;
END;
To:
DECLARE
V_LAST_LOG_ARCHIVE varchar2(100);
cnt NUMBER;
stmt VARCHAR2(1000) := 'CREATE TABLE LAST_LOG_ARCHIVE (LAST_LOG_ARCHIVE TIMESTAMP NULL)';
BEGIN
SELECT COUNT(*) INTO cnt FROM all_tables WHERE table_name = 'LAST_LOG_ARCHIVE';
IF (cnt = 0) THEN
EXECUTE IMMEDIATE stmt;
END IF;
SELECT COALESCE((
SELECT LAST_LOG_ARCHIVE
FROM LAST_LOG_ARCHIVE
WHERE ROWNUM = 1), TO_TIMESTAMP('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
INTO V_LAST_LOG_ARCHIVE
FROM dual;
END;
source to share
your second request requires an INTO statement
SELECT COALESCE((
SELECT LAST_LOG_ARCHIVE FROM LAST_LOG_ARCHIVE WHERE ROWNUM = 1
), TO_TIMESTAMP('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AS LAST_LOG_ARCHIVE
INTO l_someVariable
FROM dual;
don't forget to declare l_someVariable
in the sectionDECLARE
source to share
SELECT COALESCE ((SELECT LAST_LOG_ARCHIVE FROM LAST_LOG_ARCHIVE WHERE ROWNUM = 1), TO_TIMESTAMP ('2015-01-01 00:00:00', 'YYYY-MM-DD HH24: MI: SS')) AS LAST_LOG_ARCHIVE FROM dual;
PL / SQL is different from SQL . You must use an INTO clause , as a SELECT statement without an INTO clause will not work in PL / SQL. Basically, there is no point in selecting a row and doing nothing.
If you just want to make a choice, do it in pure SQL, not PL / SQL. If you want to store the corresponding values from the string returned by the selection into local variables, use the INTO clause .
SELECT col1, col2 INTO var1, var2 FROM table
Declare variables var1
and var2
with the appropriate column data type in the select list.
More on SELECT INTO Statement from the documentation.
Besides,
TO_TIMESTAMP ('2015-01-01 00:00:00', 'YYYY-MM-DD HH24: MI: SS'))
You can just use TO_DATE instead of TO_TIMESTAMP . Date has both date and time elements.
TO_DATE('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
source to share