Home » SQL & PL/SQL » SQL & PL/SQL » Check for existence of table, if not there, create, if there, update
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
|
|
|
|
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
|
|
|
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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 10:14:36 CDT 2024
|