 |
|
|
| When you need to convert an Oracle schema to say, a Postgres schema, restrictions follow suit. What holds good in Oracle may not necessarily hold good in PostgreSQL. Using the sed script you can overcome this problem. If you run the sed script on Linux with the specified command, you can transform the Oracle schema file into one that is acceptable by postgreSql. Here, we showcase conversion of data from Oracle to Postgresql and Oracle to mySql. You may readily download the scripts and run them on linux with the respective command. | ||
| I. | Oracle to Postgresql | |
| Connection String (JDBC) for PostgreSQL | ||
|
# JDBC connect string: database.db.jdbcUrl = jdbc:postgresql://192.168.11.5432/postgres JDBC properties (passed directly to the JDBC driver): database.db.jdbcProperties.server = 192.168.11.3 database.db.jdbcProperties.user = postgres database.db.jdbcProperties.server = Nisiki |
||
| Datatype Changes or Differences from Oracle | ||
 |
long raw in oracle is bytea in postgresql | |
|
Blob in Oracle is replaced by bytea. | |
|
Clob in Oracle is replaced by bytea in postgresql | |
|
varchar2 in oracle is replaced by varchar | |
|
number in Oracle is replaced by decimal | |
|
date in Oracle is datetime in Postgresql | |
| After creation of database you need to grant permission to the user for accessing the database, otherwise it will throw an error for access rights. | ||
|
Command |
||
| Run it with the command: | ||
| sed -f oracleToPgSql.sed < oracleSchema.sql > psqlSchema.sql | ||
| II. | Oracle to mySql | |
| Connection String (JDBC) for PostgreSQL | ||
|
# JDBC connect string: database.db.jdbcUrl=jdbc:mysql://192.168.11.183/etailer?user=Zerocode&password=Zerocode # Class name of the JDBC driver: database.db.jdbcDriverClass = org.gjt.mm.mysql.Driver # JDBC properties (passed directly to the JDBC driver): database.db.jdbcProperties.server = Nisiki database.db.jdbcProperties.user = Zerocode database.db.jdbcProperties.password = Zerocode |
||
| Datatype Changes or Differences from Oracle | ||
|
varchar2 in oracle is replaced by varchar for dynamic character datatype. But there's a restriction - it supports only 255 characters like in db2 6.5 version. For more than 255 characters you can use text datatype. | |
|
Instead of long raw in Oracle, mySql uses Blob that supports binary data. | |
|
Use regular English words, that are self-descriptive and without abbreviations while naming tables, columns and other entities. | |
|
Table Names are case sensitive in mySql. It supports lowercase, even if you writ it out in uppercase. | |
|
After creation of database you need to grant permission to the user for accessing the database, otherwise it will throw an error for access rights. | |
|
|
Number in Oracle is replaced by numeric in mySql | |
| Command | ||
| Run it with the command | ||
| sed -f oracleTomySql.sed < oracleSchema.sql > mySqlSchema.sql | ||
|
||