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;

The table has to exist before the block can be compiled. If the table's existence is optional before the block is compiled, then the insert also has to be dynamic. This is one of the primary reasons why commercial apps have install scripts that are separate from the application code. — Jeff Holt
– Jeff Holt, Commented Apr 8, 2022 at 14:15

EJ Egyed · Accepted Answer · 2022-04-08 14:37:37Z

2

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;
/

answered Apr 8, 2022 at 14:37

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create table and insert data in script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related