Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / Oracle / Oracle Server / January 2006

Tip: Looking for answers? Try searching our database.

whenever sqlerror is an unknown statement?

Thread view: 
Enable EMail Alerts  Start New Thread
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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2010 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.