Database Forum / Oracle / Oracle Server / January 2006
whenever sqlerror is an unknown statement?
|
|
Thread rating:  |
jjsavage@gmail.com - 20 Jan 2006 16:15 GMT Hi all, I downloaded Oracle XE (the free trial version), and I'm trying it out for my boss. I've got a script that contains the statement 'whenvever sqlerror continue;' and the Run Script page says "You have requested to run a script containing statement(s) SQL Workshop will ignore. Please confirm your request." It says that "whenever sqlerror continue;" is an unknown statement. Is this an XE problem, or what?
Thanks, John Savage
Sybrand Bakker - 20 Jan 2006 18:31 GMT >Hi all, > I downloaded Oracle XE (the free trial version), and I'm trying it [quoted text clipped - 6 lines] >Thanks, > John Savage whenever sqlerror is a sql*plus statement, it is not a sql statement. You need to find out whether SQL Workshop is meant to be a sqlplus replacement. If it is not you need to find out how error trapping has been catered for in SQL Workshop
-- Sybrand Bakker, Senior Oracle DBA
jjsavage@gmail.com - 20 Jan 2006 19:28 GMT My overall goal is to create a table iff it doesn't exist. After more poking around the docs, I've got this:
begin select * from all_tables where table_name = 'foo'; exception when no_data_found then begin create table foo(bar char(5)); end; end;
And SQL Workshop says: m ORA-06550: line 6, column 4: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe
Bah! Why can't I have a create, but I can have a select or update or anything else?
- John
Sybrand Bakker - 20 Jan 2006 20:06 GMT >My overall goal is to create a table iff it doesn't exist. After more >poking around the docs, I've got this: [quoted text clipped - 21 lines] > > - John create is DDL. DDL is evidently not a normal operation in SQL, ie you don't create tables on the fly. So you need to submit them using EXECUTE IMMEDIATE. All of which is liberally documented in the PL/SQL reference manual on http://tahiti.oracle.com It is also wise to look up error messages on http://tahiti.oracle.com prior to asking questions here. So far all your questions demonstrate you are just hacking away, come from a SQL server background, and think Oracle is SQL server sold by a different vendor. You are doomed to fail when you stick to that assumption. BTW: In Oracle one doesn't check the dictionary for existence or non-existence of tables. One traps exceptions.
-- Sybrand Bakker, Senior Oracle DBA
jjsavage@gmail.com - 23 Jan 2006 15:32 GMT > All of which is liberally documented in the PL/SQL reference manual on > http://tahiti.oracle.com Ah ha! Thanks very much. :)
> So far all your questions demonstrate you are just hacking away, come from a SQL > server background, and think Oracle is SQL server sold by a different vendor. Just out of curiosity, do you know how arrogant and rude that sounds?
Thanks again, John
jjsavage@gmail.com - 23 Jan 2006 17:57 GMT >BTW: In Oracle one doesn't check the dictionary for existence or >non-existence of tables. One traps exceptions. Which exception do I trap? I've found several lists of exceptions thrown, but none of them are the right one.
- John
Mark C. Stock - 24 Jan 2006 01:33 GMT > >BTW: In Oracle one doesn't check the dictionary for existence or >>non-existence of tables. One traps exceptions. [quoted text clipped - 3 lines] > > - John SQL> select * 2 from thistableisnothere; from thistableisnothere * ERROR at line 2: ORA-00942: table or view does not exist
use WHEN OTHERS and check SQLCODE for -942
(and a picky point: exceptions are raised, not thrown)
++ mcs
jjsavage@gmail.com - 25 Jan 2006 18:14 GMT Thanks! But it doesn't work:
declare x varchar(20); begin select id into x from pass where id = '1'; exception when others then if sqlcode = -942 then execute immediate 'create table pass(a char(10));'; end if; end;
Gives ORA-06550: line 4, column 23: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 4, column 1: PL/SQL: SQL Statement ignored. So it's not catching any 'others', and it's not creating the table (I know because the same thing happens if I run it again). What's wrong?
Thanks again, John
Mark C. Stock - 25 Jan 2006 18:19 GMT > Thanks! But it doesn't work: > [quoted text clipped - 16 lines] > Thanks again, > John in a pl/sql block you can only trap this if you are using dynamic sql
you are getting a compile time error
you can check ORA-00942 in pre-compiler environments
++ mcs
DA Morgan - 25 Jan 2006 18:28 GMT > Thanks! But it doesn't work: > [quoted text clipped - 16 lines] > Thanks again, > John You are misunderstanding what you see.
1. Believe the ORA-06550. The privilege must be granted explicitly. 2. Creating a table in any PL/SQL is deplorable. In an exception handler about as bad as bad can be. 3. VARCHAR is NOT an Oracle data type for declaring PL/SQL variables. Oracle is NOT SQL Server. 4. Is ID a string or a number? 5. Try recoding like this:
DECLARE x VARCHAR2(5); sc NUMBER; se VARCHAR2(200); BEGIN SELECT id INTO x FROM pass WHERE id = '1'; EXCEPTION WHEN OTHERS THEN sc := sqlcode; se := sqlerrm; dbms_output.put_line(TO_CHAR(sc) || ' ' || se); END; /
 Signature Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)
jjsavage@gmail.com - 25 Jan 2006 19:21 GMT 1. Ah - I forget I'm running the script from a Delphi program that logs into the database with different priveleges than the SQL Workshop that comes with Oracle. 2. This is bad news, since I really need to create a table dynamically somehow, but only if it doesn't already exist. How is this done in Oracle? 3. I'm porting our application from SQL Server to Oracle, that's why there's all the Server-isms. If I had my choice, I'd be doing this in MySQL: CREATE TABLE IF NOT EXISTS pass(...) Now THAT'S what I call a good language design. But there's more to a database than a good SQL implementation. 4. It's a number; that might be part of the problem too. 5. That shows the error, which is better than crashing, but doesn't create the table.
Thanks, John
Frank van Bortel - 25 Jan 2006 20:14 GMT > 2. This is bad news, since I really need to create a table dynamically > somehow, but only if it doesn't already exist. How is this done in > Oracle? Seldom true.
You're just porting SS2K code 1:1 to Oracle and being lazy. <ashamed>Been there, done that.</ashamed>
Oracle is far more sophisticated, and has seldom need for dBaseIV like constructs; you should rewrite that code. Been there, done that, too.
 Signature Regards, Frank van Bortel
Top-posting is one way to shut me up...
Sybrand Bakker - 27 Jan 2006 17:20 GMT >3. I'm porting our application from SQL Server to Oracle, that's why >there's all the Server-isms. If I had my choice, I'd be doing this in >MySQL: >CREATE TABLE IF NOT EXISTS pass(...) >Now THAT'S what I call a good language design. Do you really think you are in the correct profession? Or are you just one of the million hackers ruining the trade?
-- Sybrand Bakker, Senior Oracle DBA
jjsavage@gmail.com - 27 Jan 2006 19:28 GMT >Do you really think you are in the correct profession? I don't quite follow... I enjoy programming, but I don't like fighting against the languange to get it to do what I want. When the language designer added built-in functionality for something I want to do, this make me happy. 6 words, to me, is far better than 16 lines. Do you disagree?
>Or are you just one of the million hackers ruining the trade? By trade, do you mean database programming or programming in general? I confess I don't like databases; the only comp sci class I ever got a C in was my database class. But, until I graduate, I'm stuck with whatever jobs I can get, and this is what I got. When I have those 2 magic letters after my name (BS), then I'll have many more options, and I'll move into something that really appeals to me.
- John
Andy Hassall - 25 Jan 2006 21:14 GMT >2. Creating a table in any PL/SQL is deplorable. ... if it's application code modifying its own schema. The exception to this rule is if it's actually the installer for the application, that's setting up or upgrading the schema for a new version of a packaged application, where constructs like this have their uses.
(But it sounds from a later post by the OP that it _is_ application code, so yes it's bad)
 Signature Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
jjsavage@gmail.com - 27 Jan 2006 17:01 GMT We occasionally send our clients updates to our program, which sometimes use new tables that our clients don't have. So every time the application starts, it checks to see if any new tables need to be created. If so, it creates them. If not, it goes on its merry way. Is there a better approach?
Thanks, John
Sybrand Bakker - 27 Jan 2006 17:20 GMT >We occasionally send our clients updates to our program, which >sometimes use new tables that our clients don't have. So every time [quoted text clipped - 4 lines] >Thanks, > John Ever heard of upgrade procedures?
-- Sybrand Bakker, Senior Oracle DBA
jjsavage@gmail.com - 27 Jan 2006 19:13 GMT Upgrade procedures? That sounds promising indeed. Where can I find out about them?
Thanks, John
Jim Smith - 28 Jan 2006 10:20 GMT >Upgrade procedures? That sounds promising indeed. Where can I find >out about them? Upgrade procedures are procedures that you write to do an upgrade. My preference would also be to upgrade the database before running the upgraded software but if your customers have no technical staff then embedding the upgrade in the application is probably acceptable.
Ignoring all the shouting in here about whether it is good practice to create tables on the fly (it isn't), it is necessary in some rare circumstances like yours.
The best way for individual tables is probably
declare tname varchar2(30);
begin select table_name into tname from user_tables where table_name = 'NEWTABLE'; exception when no_data_found then execute immediate 'create...'; end;
However, this approach has a number of problems - it assumes the application is connected to the database as the schema owner - it is impractical if there are a lot of new tables/objects - it is impractical for altered tables (ie new of changed columns) - if it fails for any reason it leaves the application in limbo and possible broken.
A better approach is to maintain a db version table and use it to drive an upgrade procedure.
declare required_version constant number(10,0):=987; actual_version number(10); begin select db_version into actual_version from version_info; exception when others .... <snip> end; if actual_version < require_version then do_upgrade;
end if;
procedure do_upgrade is -- very crudely begin begin execute immediate 'create...'; execute immediate 'drop...'; execute immediate 'alter..'; update version_info set db_version=3; exception when others -- ; end; end;
Note:
The do_upgrade needs to be run as the schema owner. Again, if this is run as part of the application, failure could leave the databases in a broken state.
 Signature Jim Smith I'm afraid you've mistaken me for someone who gives a damn.
Mark C. Stock - 28 Jan 2006 11:11 GMT >>Upgrade procedures? That sounds promising indeed. Where can I find >>out about them? [quoted text clipped - 3 lines] > upgraded software but if your customers have no technical staff then > embedding the upgrade in the application is probably acceptable. ...
along with jim's suggestion of a version table, it would probably be good to have your application merely check if the database structures are correct and report an error, then isolate the upgrade procedures to a separate utility that is only run be privileged users, rather than upgrade things on the fly. this imposes some discipline on version management.
++ mcs
jjsavage@gmail.com - 31 Jan 2006 15:34 GMT After all that, we decided it would be too much work to port to Oracle, so the whole project has been scrapped. Oh well. Thanks, everyone, for your help! :)
- John
Andy Hassall - 20 Jan 2006 20:24 GMT >My overall goal is to create a table iff it doesn't exist. After more >poking around the docs, I've got this: > >begin > select * from all_tables where table_name = 'foo'; You need an INTO clause, even if you don't actually care about the result.
> exception > when no_data_found then > begin > create table foo(bar char(5)); You can't write DDL directly in PL/SQL, you have to wrap it in EXECUTE IMMEDIATE.
> end; >end; > >Bah! Why can't I have a create, but I can have a select or update or >anything else? http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CA CDDACH
 Signature Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
|
|
|