This discussion is archived
2 Replies Latest reply: Aug 2, 2013 6:23 AM by Dermot ONeill RSS

Sybase and Oracle Reserved Words

user814560 Newbie
Currently Being Moderated

Hi all,

 

we are planning on migrating a Sybase ASE database to Oracle 11g by using SQL Developer. Although for IBM DB2 and MS Access,the documentation clearly states SQL Developer's native behavior when encountering Oracle reserved words in the source db, this is not clearly stated for Sybase.

 

IBM DB2

Reserved words differ between Oracle and IBM DB2. Some Oracle reserved words are

valid object or column names in IBM DB2. Use of reserved words as schema object

names makes it impossible to use the same names across databases. SQL Developer

appends an underscore and a numeric digit (_n) to the end of the name of an IBM DB2

object that is an Oracle reserved word.

 

MS ACCESS

The following table lists, in tabular format, words that are reserved in Oracle. SQL

Developer appends an underscore to any object names that conflict with these

reserved words.

 

Can anyone share their experience in migrating from Sybase to Oracle with SQL Developer and let us know what the default behavior is? (underscore as a suffix, underscore as a prefix, _n, etc)

 

Thanks

Tony

  • 1. Re: Sybase and Oracle Reserved Words
    rp0428 Guru
    Currently Being Moderated

    Can anyone share their experience in migrating from Sybase to Oracle with SQL Developer and let us know what the default behavior is? (underscore as a suffix, underscore as a prefix, _n, etc)

    The easiest way to get your answer is to create some sample objects and do a test migration.

     

    This is from the latest doc that I could find. It has similar language to what you quoted but does not say what behavior is used.

     

    Oracle® SQL Developer Supplementary Information for Microsoft SQL Server and Sybase Adaptive Server Migrations
    Release 3.0

    http://docs.oracle.com/cd/E35137_01/doc.32/e18462/ss_oracle_compared.htm#sthref17

    2.1.2 Schema Object Names

    Reserved words differ between Oracle and Microsoft SQL Server or Sybase Adaptive Server. Many Oracle reserved words are valid object or column names in Microsoft SQL Server or Sybase Adaptive Server. For example, DATE is a reserved word in Oracle, but it is not a reserved word in Microsoft SQL Server or Sybase Adaptive Server. Therefore, no column is allowed to have the name DATE in Oracle, but a column can be named DATE in Microsoft SQL Server or Sybase Adaptive Server. Use of reserved words as schema object names makes it impossible to use the same names across databases.

    You should choose a schema object name that is unique by case and by at least one other characteristic, and ensure that the object name is not a reserved word from either database

    .

  • 2. Re: Sybase and Oracle Reserved Words
    Dermot ONeill Journeyer
    Currently Being Moderated

    Hi Tony,

     

    The same naming convention is applied to all the databases migrated to Oracle using SQL Developer.

    If its Keyword in Oracle,  then an underscore is appended.

    Oracle has certain rules on what characters are allowed in an object name and what characters can be used at the start of an object name. These are handled as well.

    If its greater than 30 characters in length , then it is truncated.

    Sometimes after a identifier name goes through this process to make it valid in Oracle, you end up with two or more objects with the same name in the same name space. Like two tables with the same name in the same schema.

    SQL Developer recognizes these as well and give a unique name to each object in the name space by truncating a little more to append _n.

     

    If you capture and convert your Sybase database using SQL Developer, there is a report detailing the name changes of each object.

     

    Regards,

    Dermot.

    SQL Developer Team.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points