OraFAQ Forum: SQL & PL/SQL » Check for existence of table, if not there, create, if there, update

Home » SQL & PL/SQL » SQL & PL/SQL » Check for existence of table, if not there, create, if there, update

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Check for existence of table, if not there, create, if there, update [message #492]

Fri, 15 February 2002 09:08

Mike
Messages: 417
Registered: September 1998

Senior Member

Greetings, I am trying to write a SQL statement for Oracle that will first check for the existence of a table in the database, and if it finds it, a row will be updated. If it doesn't find it, it will create the table and insert the first row. Everything I've tried doesn't work, I can find nothing in the documentation and I've searched this message board, all to no avail. I know it has to be possible. This will be going into a distributed utility application with many users connecting to the database. Any help would be GREATLY appreciated. Thanks in advance!

Mike

Report message to a moderator

Re: Check for existence of table, if not there, create, if there, update [message #493 is a reply to message #492]

Fri, 15 February 2002 10:47

Suresh Vemulapalli
Messages: 624
Registered: August 2000

Senior Member

declare
n number;
table_not_exist exception;
pragma exception_init(table_not_exist,-00942);
begin
execute immediate 'insert into emp1 values(123,''suresh'')';
commit;
exception
when table_not_exist then
execute immediate 'create table emp1(empno number,ename varchar2(20))';
execute immediate 'insert into emp1 values(123,''suresh'')';
commit;
end;

Report message to a moderator

Re: Check for existence of table, if not there, create, if there, update [message #497 is a reply to message #492]

Fri, 15 February 2002 13:22

Mike
Messages: 417
Registered: September 1998

Senior Member

Suresh, thanks so much for the help. It works great when I try it in Oracle ODBC32 Test. I tweaked it a little to fit my particlar needs:

declare
n number;
table_not_exist exception;
pragma exception_init(table_not_exist,-00942);
begin
execute immediate 'update currobj set curr_obj = curr_obj + 1';
commit;
exception
when table_not_exist then
execute immediate 'create table currobj(curr_obj number,lic_obj char(10))';
execute immediate 'insert into currobj values(1,''1234567890'')';
commit;
end;

However, when I put it in my VB project I get an error:

PLS-00103: Encountered the symbol "update currobj set curr_obj = curr_obj + 1" when expecting one of the following: := . ( @ % ; not null range default character
ORA-06550: line 1. column 360:

This is the syntax in VB:

SQLstr = "declare n number; table_not_exist exception; " + _
"pragma exception_init(table_not_exist,-00942); begin " + _
"Execute immediate 'update currobj set curr_obj = curr_obj + 1'; " + _
"commit; exception when table_not_exist then " + _
"Execute immediate 'create table currobj(curr_obj number,lic_obj char(10))'; " + _
"Execute immediate 'insert into currobj values(1,''1234567890'')'; " + _
"commit; end; select curr_obj from currobj;"

I don't know if you're familiar with VB, but if not the + _ are just line continuation symbols.

Again, any help is greatly appreciated.

Thanks in advance,

Mike

Report message to a moderator

Re: Suresh, never mind, I got it to work! [message #498 is a reply to message #492]

Fri, 15 February 2002 13:41

Mike
Messages: 417
Registered: September 1998

Senior Member

Suresh, it turns out all I needed to do was put a space at the end of the first two lines before the close quote.

Thanks again for your help, I've been trying to figure this out on my own for two days.

Mike

Report message to a moderator

Previous Topic:	number of records in each table
Next Topic:	A question about escape sequence...Urgent!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 16:34:36 CDT 2024