Re: SQL7-> Oracle Help

Hi James,

    Welcome to the big leagues ;-)

I have inserted my response/comments inline.

James A. Littlefield wrote in message <375670C5.21985016_at_alum.mit.edu>...
>Moving from NT/SQL7.0 to Linux/Oracle I have encountered a few issues.
>Perhaps someone can point an Oracle newbie to right solution(s).
>
>
>1. My database has some MS/SQL7 varbinary columns. This seems to map
>to the raw datatype under Oracle? I would like to apply some
>contraints to ensure that the values are only within a certain range.
>SQL7 lets me define contraints which compare the values of a
>varbinary(1) to constants such as 0x01. I have been able to compare
>for equality under Oracle using RAWTOHEX() to get a string and compare
>the string to a constant '01' but this does not work for ranges. What
>is the best way to do this?

    Sorry, I don't know what a MS/SQL7 varbinary is so I can't comment.

>2. SQL7 has an identity datatype which causes each inserted row to get
>assigned a unique value. The closest thing I have found under Ora is
>to define a sequence and use values from the sequence. Is this the
>correct approach.

    Use the Oracle sequence. Do bear in mind that anytime a rollback occurs there can be a gap in your sequence numbers. In other words they will provide unique identifiers , but don't use them for things like check numbers (your auditors will be very unhappy).

>3. To guard against concurrent read/update errors MS/SQL7 suggests
>using adding a column type of timestamp. The timestamp column gets
>updated automatically whenever anyone does a modification to the
>column. Applications can tell if update collisions have occurred by
>adding a "where timestamp = " clause to the table update statements.
>How does this work under Oracle

    You can do SELECT ... FOR UPDATE. This will lock the record and prevent any other program from either deleting or updating the record until either a commit or a rollback is performed. Note that this will not stop another program from reading the record.

>4. MS/SQL7 has a database option (xact_abort) which controls behavior
>during execution of a batch. If xact_abort is on, the entire batch is
>rolled back if any of its statements fail. If xact_abort is off, only
>the offending statement is rolled back but execution of the rest of the
>batch continues. What option(s) control this behavior under Ora.

    OK, what is an MS/SQL7 batch? If it is equivalent to an Oracle stored procedure or SQL script consisting of more than one SQL statement it is possible to insert explicit commits anyplace you want. The default in Oracle is 1) Commit anytime the session issues a DDL statement. 2) commit anytime the session issues an explicit commit statement. 3) commit when the session terminates in an orderly manner. 4) rollback in the event of an error. 5) rollback if an explicit rollback is issued by the session 6) rollback if the session terminates in a disorderly manner. Your best bet is to build a small test instance and do some experimentation since the scope of a transaction (the commands issued by a session between two commits or between a commit and a rollback) is sometimes obscure and best learned by making stupid mistakes before your developers and users find out you are not omnipotent (I do assume you are going to be the DBA).

regards

Jerry Gitomer

>
>Thanks for any suggestions/pointers.
>
>Jim
>
>
Received on Thu Jun 03 1999 - 15:39:06 CDT