Re: Access to Oracle upgrade....

On Fri, 29 Sep 2006 19:55:09 +0000, Dave wrote:

> Hi All,
>
> I'm looking at upgrading the Access database I use at work to Oracle,
> now I can import all the tables and data okay but what I am having
> trouble with are the constraints.
>
> In my main Access table I have validation rule for 2 fields that are
> basically
>
> 1)>=459000 And <=501000
> 2)>=202000 And <=244000
>
> How would I do this in Oracle? I tried just cutting and pasting the code
> into Oracle's Constraint Expression field but I get the error ORA-00936:
> missing expression
>
> Any ideas?

Yup.

First - the database does not have a 'Contraint Expression field'. That must be something in whatever tool you are using.

Second - this is a Check Constraint. Under Oracle's definitions, however, a Check constraint does not link back to a specific column. You need to specify the column name in the condition. See the link beow for examples.

>
> and any recommendations for a book that will bridge the gap between
> Access and Oracle (or a beginners book if that gap is too big!)
>

There are many books out there, but IMO none are at the level you want. The fall-back is to use Oracle reference material, which is complete but awkward to read. The access is via http://docs.oracle.com or http://tahiti.oracle.com and then drill down based on your version.

The SQL Reference manual is filled with examples. Dry reading, but worth it. The specific question is answered (with the 10gR2 version of doc) at http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#i1002719

Since you are migrating form a non-Oracle system, I recommend the following books for you:

1. OReilly (http://oracle.oreilly.com) has - Oracle Essentials ... excellent overview on how it works and helps get over the terminology hurdle; - Mastering SQL ... understand what Oracle's SQL can do and how to do it (a must for those coming from Access and SQL Server);
2. APress (http://www.apress.com) has excellent 'how it works and how to make it perform' books by Tom Kyte. Others there are also worth buying, but are for more advanced people.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.