Creating a table in Oracle DB if it does not exist, using SQL

[google_user]

Hi,

I need to create a table in Oracle DB using SQL before which I need to
check if it exists or not, where the dbuser will have CONNECT and
RESOURCE privileges granted.

All this needs to be in a .sql file so that it can be included in the
release.

I tried writing a Stored Procedure using DBMS_SQL.parse etc., but
looks like, the above stated privileges are not enough.
Is there a way that serves my purpose without anymore privileges given
?

If someone can take time to write a running sample and send me, I
would be Grateful.

Thanks,
Chaitanya.

[rob]

Re: Creating a table in Oracle DB if it does not exist, using SQL

If someone can take time to write a running sample and send me, I

would be Grateful.
>
Thanks,
Chaitanya.

Something like this?
=== create_test123. sql ===
DECLARE
c_table_name varchar2(50) := upper('test123' );
cursor c1 is
select table_name
from user_tables
where table_name = c_table_name;
BEGIN
open c1;
fetch c1 into v_table_name;
if c1%NOTFOUND
then
execute immediate 'create table test123 (n number
,b varchar2(20)) ' ;
end if;
close c1;
END;
/

=== end create_test123. sql ===

[Wario]

Re: Creating a table in Oracle DB if it does not exist, using SQL

1. Create a file with your table definition. Your could call it
anything you want with a SQL extention. EXAMPLE mynewtable.sql:

create table &1..new_tabl e (
col1 char(1),
col2 char(1),
col3 char(1),
col4 char(1)
);

2. Logon to database with DBA rights and call mynewtable.sql with
user name as parameter.

sqlplus dba/password

SQL @mynewtable scott

3. Repeat #2 for every user. This will create the table for each
user. There is no need to check if the table already exists since
trying to create the table will error out and not harm the existing
table.