By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,666 Members | 1,924 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,666 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.