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...?

Well yeah your second select is returning a value and you are not doing anything with it. What would you like to do with the LAST_LOG_ARCHIVE retuned? Just do the same thing that you did with the "select count(*) into" and your code will work, you can then decide what to do with that value. — MarioAna, Aug 10 '15 at 08:14
Coalesce function is returns first not null value so for holding that value in oracle we need a variable like select coalesce(var1,var2) into var3 from table _name. Now var3 contains either var1 or var2. — Tharunkumar Reddy, Aug 10 '15 at 08:15
When table does not exist the pre-compiler sees you are referencing an object in second select that does not exist at compile time (LAST_LOG_ARCHIVE) — Rob van Laarhoven, Aug 10 '15 at 08:24
@Robert: I understand that. Therefore I try to check for the existence of the table and if not create it. — BlueIced, Aug 10 '15 at 08:53
@user2782991 The PL/SQL compiler does not see that you will create the table if it does not exist. At compile time you're referencing a table that does not exist and will always return a compiler error. You will have to convert the select to dynamic sql too if you want to do this. — Rob van Laarhoven, Aug 10 '15 at 09:13

score 0 · Answer 1 · answered Aug 10 '15 at 08:14

your second query needs the 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;

remember to declare the l_someVariable in the DECLARE section

score 0 · Accepted Answer · answered Aug 10 '15 at 08:17

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;

The procedure executes now, but how do you get the results out of that variable? I tried to use dbms_output.put_line(V_LAST_LOG_ARCHIVE). Unfortunately no success... Actually it needs to end up in a .NET application. — BlueIced, Aug 10 '15 at 08:56
What you have right now is just an anonymous block, not a procedure. You want to put in the first line: "Create or replace procedure PROCEDURE_NAME (v_out_LAST_LOG_ARCHIVE as varchar2) AS". Then at the end just add another "end;" statement. Now this will build the procedure which will return you a variable of your choosing when you add the line "return V_LAST_LOG_ARCHIVE". I don't know how PLSQL procedure calls work in .NET though. Also if you want dbms_output.put_line to work you need to "set serveroutput on;" in your terminal or whichever tool you are using. — MarioAna, Aug 10 '15 at 14:10

score 0 · Answer 3 · answered Aug 10 '15 at 08:19

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 INTO clause, as a SELECT statement without INTO clause would fail in PL/SQL. Basically, it makes no sense to select a row and do nothing about it.

If you just want to do the select then do it in pure SQL and not in PL/SQL. If you want to store the respective values from the row returned by the select into local variables, then use INTO clause.

SELECT col1, col2 INTO var1, var2 FROM table

Declare the variables var1 and var2 with respective datatype of the columns in the select list.

Read more about SELECT INTO Statement from documentation.

Also,

TO_TIMESTAMP('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

You could simply use TO_DATE instead of TO_TIMESTAMP. A Date has both date and time elements.

TO_DATE('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

Excute SELECT after CREATE in Oracle PL/SQL

3 Answers3