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;
CodePudding user response:
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;
/