oracle insert if not exists,create table if not exists?

John,

It is up to the user to decide, not to you. You have your believe that this is far complicated but it the user wants to enhance his knowledge with learning some PL/SQL code, than let him go ahead.

About "the flawless", well, it can be corrected just creating a procedure PL/SQL and pass as parameter the owner and the table_name as this:

CREATE OR REPLACE PROCEDURE CREATE_TABLE_IF_NOT_EXIST (l_owner IN DBA_TABLES.OWNER%TYPE, l_table_name IN DBA_TABLES.TABLE_NAME%TYPE) IS

l_check  VARCHAR2(10);

BEGIN

SELECT 'VALID' into l_check FROM dba_tables where table_name = l_table_name and owner = l_owner;

DBMS_OUTPUT.PUT_LINE('TABLE '|| l_owner ||'.'|| l_table_name ||' ALREADY EXIST.');

EXCEPTION WHEN NO_DATA_FOUND then

EXECUTE IMMEDIATE 'create table '|| l_owner ||'.'|| l_table_name ||' ( ID VARCHAR2(10))';

DBMS_OUTPUT.PUT_LINE('TABLE '|| l_owner ||'.'|| l_table_name ||' CREATED SUCESSFULLY.');

END;

/

Procedure created.

SYS> EXEC CREATE_TABLE_IF_NOT_EXIST ('SCOTT','NO_TABLE_JOHN');

TABLE SCOTT.NO_TABLE_JOHN CREATED SUCESSFULLY.

PL/SQL procedure successfully completed.

SYS> DROP TABLE SCOTT.NO_TABLE_JOHN;

Table dropped.

SYS> EXEC CREATE_TABLE_IF_NOT_EXIST ('SCOTT','NO_TABLE_JOHN');

TABLE SCOTT.NO_TABLE_JOHN CREATED SUCESSFULLY.

PL/SQL procedure successfully completed.

SYS> EXEC CREATE_TABLE_IF_NOT_EXIST ('SCOTT','NO_TABLE_JOHN');

TABLE SCOTT.NO_TABLE_JOHN ALREADY EXIST.

PL/SQL procedure successfully completed.

SYS> DROP TABLE SCOTT.NO_TABLE_JOHN;

Table dropped.

Also, you are on PL/SQL fórum, so the more correct would be to answer it as a PL/SQL code.

Regards.