What is EXECUTE IMMEDIATE for?

Question

I am a SQL Server user, and I have a small project to work with Oracle, so I'm trying to understand some of the features of Oracle, and I believe that I need help to better understand the following situation:

I want to check if a temporary table exists before creating it, so I had this code here:

DECLARE
  table_count INTEGER;
  var_sql VARCHAR2(1000) := 'create GLOBAL TEMPORARY table TEST (
            hello varchar(1000) NOT NULL)';
BEGIN
  SELECT COUNT(*) INTO table_count FROM all_tables WHERE table_name = 'TEST';

  IF table_count = 0 THEN
    EXECUTE IMMEDIATE var_sql;
  END IF;
END;

It works fine, so after I executed it once, I added an else statement to my IF:

ELSE
  insert into test (hello) values ('hi');

I executed it again, and a row was added to the test table.

Ok, my code was ready and working, so I reset the temporary table and tried to run the whole statement again, but when I do this, I get the following error:

ORA-06550: line 11, column 19:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 11, column 7:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Then I replaced this expression with another, and now it works again:

ELSE
  EXECUTE IMMEDIATE 'insert into test (hello) values (''hi'')';

, , EXECUTE IMMEDIATE, SELECT , BEGIN - , , , EXECUTE IMMEDIATE ?

Answer 1

PL/SQL , . ( , ).

if/else . , , , , .

, ; , , 1, , . , ORA-00942 , .

, - , , , . - , , , , .

" " . , , / . ( , / , , , GTT ).

Oracle . - , , , . , PL/SQL.

GTT PL/SQL- . - SQL Server, PL/SQL.

Answer 2

PL/SQL: ORA-00942:

, .. SQL GTT.

:

SQL:

SQL> DECLARE
  2  v number;
  3  BEGIN
  4  select empno into v from a;
  5  end;
  6  /
select empno into v from a;
                         *
ERROR at line 4:
ORA-06550: line 4, column 26:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored

SQL:

SQL> DECLARE
  2  v number;
  3  BEGIN
  4  execute immediate 'select empno from a' into v;
  5  end;
  6  /
DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 4

1- PL/SQL- , PL/SQL: ORA-00942: table or view does not exist. PL/SQL PL/SQL.

Bottomline,

, , .

, , INSERT EXECUTE IMMEDIATE. , , , .

, , GTT , . , .

Answer 3

litle, . exp [ , GTT , goood.

--- Firstly by dropping the table i.e NO TABLE EXISTS in the DB in AVROY 

SET serveroutput ON;
DECLARE
  table_count INTEGER;
  var_sql     VARCHAR2(1000) := 'create GLOBAL TEMPORARY table TEST (            
hello varchar(1000) NOT NULL)';
BEGIN

EXECUTE IMMEDIATE 'DROP TABLE AVROY.TEST'; --Added the line just to drop the table as per your comments

  SELECT COUNT(*)
  INTO table_count
  FROM all_tables
  WHERE table_name = 'TEST'
  AND OWNER        = 'AVROY';
  IF table_count   = 0 THEN
    EXECUTE IMMEDIATE var_sql;
    dbms_output.put_line('table created');
  ELSE
    INSERT INTO AVROY.test
      (hello
      ) VALUES
      ('hi'
      );
  END IF;
END;

--------------------OUTPUT-----------------------------------------------

anonymous block completed
table created

SELECT COUNT(*)
--  INTO table_count
  FROM all_tables
  WHERE table_name = 'TEST'
  AND OWNER        = 'AVROY';

COUNT(*)
------
1
--------

-- Second option is without DROPPING TABLE


SET serveroutput ON;
DECLARE
  table_count INTEGER;
  var_sql     VARCHAR2(1000) := 'create GLOBAL TEMPORARY table TEST (            
hello varchar(1000) NOT NULL)';
BEGIN

--EXECUTE IMMEDIATE 'DROP TABLE AVROY.TEST';

  SELECT COUNT(*)
  INTO table_count
  FROM all_tables
  WHERE table_name = 'TEST'
  AND OWNER        = 'AVROY';
  IF table_count   = 0 THEN
    EXECUTE IMMEDIATE var_sql;
    dbms_output.put_line('table created');
  ELSE
    INSERT INTO AVROY.test
      (hello
      ) VALUES
      ('hi'
      );
      dbms_output.put_line(SQL%ROWCOUNT||' Rows inserted into the table');
  END IF;
END;

-------------------------------OUTPUT-------------------------------------

anonymous block completed
1 Rows inserted into the table


---------------------------------------------------------------------------