Excute SELECT after CREATE in Oracle PL/SQL

Question

I am new to Oracle PL/SQL, although I have a lot of experience with SQL. At the moment I am trying to convert a couple of T-SQL statements to PL/SQL. I am trying to execute the following code, but I get some errors.

If the table does not exist yet, then the error is: 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 once more and now I am getting the following error:

An INTO clause is expected in this SELECT statement.

The code I am 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...?

Answer 1

Change:

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;