470,855 Members | 1,193 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,855 developers. It's quick & easy.

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

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.
Jul 19 '05 #1
2 25673
rob
> 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 ===
Jul 19 '05 #2
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_table (
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.
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by KGP | last post: by
1 post views Thread by Ankit | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.