Create table and insert data in script

Question

What I'm trying to do is create table if not exists and then insert data in it. The script works fine when I only create the table, but not when I add an insert in the script (Error : table or view does not exists for the insert statement). I'm new in Oracle, what am I doing wrong ?

declare 
v_table_exists number;
       
begin
  
  select count(*) 
    into v_table_exists
    from user_tables 
   where table_name = upper('my_table');

  if v_table_exists = 1 
  then
    execute immediate 'drop table my_table';
  end if;
   
  execute immediate 'create table my_table
                                  (my_ID int
                                   my_column int
                                  )';                                      
    
  insert into my_table
        (my_ID,
         my_column 
        )
  values (1,2);
  
  commit;
end;

Answer 1

The first error you will run into is that "my_table" doesn't exist. That is because when parsing the PL/SQL block, your INSERT statement is referencing a table that doesn't exist yet, so you will want to put that in an EXECUTE IMMEDIATE statement.

You will also need to put a comma between your column definitions of your table.

See functional code below

DECLARE
    v_table_exists   NUMBER;
BEGIN
    SELECT COUNT (*)
      INTO v_table_exists
      FROM user_tables
     WHERE table_name = UPPER ('my_table');

    IF v_table_exists = 1
    THEN
        EXECUTE IMMEDIATE 'drop table my_table';
    END IF;

    EXECUTE IMMEDIATE 'create table my_table (my_ID int, my_column int)';

    EXECUTE IMMEDIATE 'INSERT INTO my_table (my_ID, my_column) VALUES (1, 2)';

    COMMIT;
END;
/