ORACLE to ALTIBASE Conversion Guide#
Overview#
This document is about how to migrate Oracle Database to Altibase 7.1 or later.
Info
For errors and improvements related to this document, please contact the technical support portal or technical support center.
- Technical support portal: http://support.altibase.com/en
- Technical support center: +82-2-2082-1114
Info
This document is provided for informational purposes only and is subject to change without prior notice. It may contain errors, and Altibase makes no express or implied warranties of any kind, including but not limited to warranties of merchantability or fitness for a particular purpose.
The development, release, and timing of any features or functionality described for Altibase products in this document are at the sole discretion of Altibase.
Altibase may hold patents, trademarks, copyrights, or other intellectual property rights related to the contents of this document.
Migration Process#
Migration from Oracle to Altibase follows the following procedure.


System Analysis#
Describes the preliminary checklist for Oracle versions and environment configurations that can be migrated to Altibase.
Supported Version#
Oracle 12c
Preliminary Checklist#
To migrate from Oracle to Altibase, basic information about Oracle must be collected.
Environment Analysis#
- Oracle version
- OS and hardware specifications (CPU, memory, disk, etc.) of the system where Oracle is installed
- Altibase version
- OS and hardware specifications (CPU, memory, disk, etc.) of the system where Altibase will be installed
- Storage product, specifications (CPU, memory, network), and configuration (RAID1/RAID5, etc.) of the database server
- Network configuration and speed between the database server and storage
Oracle Analysis#
- Number and size of tablespaces
- Number and size of tables
- Number and size of objects
- Physical memory / swap size of the Oracle server
- Proportion of functions/procedures among all queries
Business Analysis#
- Ratio of online tasks to batch tasks
- Query complexity of batch tasks (Join, Group By, Order By, etc.)
- Number of concurrent batch tasks (queries)
SQL Conversion Guide#
Explanation of SQL conversion methods when migrating from Oracle to Altibase.
Data type conversion#
Describes considerations when converting Oracle data types to Altibase.
| Category | Oracle | Altibase | Oracle Max Size | Altibase Max Size | Note |
|---|---|---|---|---|---|
| Character Types | CHAR | CHAR | 2000 bytes | 32000 bytes | |
| VARCHAR2, VARCHAR | VARCHAR2, VARCHAR | 4000 bytes | 32000 bytes | If MAX_STRING_SIZE=EXTENDED in Oracle, 32767 bytes | |
| NCHAR | NCHAR | 1000(UTF16), 666(UTF8) Max 2000 bytes | 16000(UTF16), 10666(UTF8) Max 32000 bytes | ||
| NVARCHAR2 | NVARCHAR | 2000(UTF16), 1333(UTF8) Max 4000 bytes | 16000(UTF16), 10666(UTF8) Max 32000 bytes | If MAX_STRING_SIZE=EXTENDED in Oracle, 16383(UTF16), 10922(UTF8) Max 32767 bytes | |
| Number Types | NUMBER NUMBER(p) NUMERIC(p, s) | NUMBER NUMBER(p) NUMERIC(p, s) | NUMBER(38) is an integer type; it is recommended to convert it to native types such as SMALLINT, INTEGER, BIGINT, etc., considering the maximum size. Using native types can reduce conversion overhead during data processing and improve storage efficiency. | ||
| FLOAT FLOAT(p) | FLOAT FLOAT(p) | ||||
| BINARY_FLOAT | REAL | 4-byte floating point type | |||
| BINARY_DOUBLE | DOUBLE | 8-byte floating point type | |||
| LONG & RAW Types | LONG | CLOB | 2 GB | 4 GB - 1 | Can be replaced with Altibase CLOB |
| LONG RAW | BLOB | 2 GB | 4 GB - 1 | Can be replaced with Altibase BLOB | |
| RAW (size) | BLOB | 2000 bytes | 4 GB - 1 | If MAX_STRING_SIZE=EXTENDED in Oracle, 32767 bytes / Can be replaced with Altibase BLOB | |
| Date Types | DATE | DATE | Altibase DATE: 8 bytes, range Jan 1, year 1 to Dec 31, 9999. Stores year, month, day, hour, minute, second, and microseconds / Oracle DATE: 7 bytes, range Jan 1, 4712 BC to Dec 31, 9999 AD. Stores year, month, day, hour, minute, and second | ||
| INTERVAL YEAR TO MONTH | - | Not supported in Altibase | |||
| INTERVAL DAY TO SECOND | - | Not supported in Altibase | |||
| TIMESTAMP WITH TIME ZONE | - | Not supported in Altibase | |||
| TIMESTAMP WITH LOCAL TIME ZONE | - | Not supported in Altibase | |||
| TIMESTAMP | DATE | Can be replaced with Altibase DATE / Altibase DATE supports second precision up to 6 digits (microseconds) / Oracle TIMESTAMP supports second precision up to 9 digits / * Note: In Altibase, TIMESTAMP values cannot be user-defined. | |||
| Large Object Types | BLOB | BLOB | (4 GB - 1) * (database block size) | 4 GB - 1 | |
| CLOB | CLOB | (4 GB - 1) * (database block size) | 4 GB - 1 | ||
| NCLOB | CLOB | (4 GB - 1) * (database block size) | 4 GB - 1 | Can be replaced with Altibase CLOB | |
| BFILE | BLOB | 4 GB | 4 GB - 1 | Can be replaced with Altibase BLOB | |
| ROWID Types | ROWID | - | Not supported in Altibase | ||
| UROWID | - | Not supported in Altibase | |||
| Any Types | ANYTYPE | - | Not supported in Altibase | ||
| ANYDATA | - | Not supported in Altibase | |||
| ANYDATASET | - | Not supported in Altibase | |||
| XML Types | XMLType | - | Not supported in Altibase | ||
| URI Data Types | - | Not supported in Altibase | |||
| URIFactory Package | - | Not supported in Altibase | |||
| Spatial Types | SDO_GEOMETRY | GEOMETRY | 16 bytes ~ 100 MB, default: 32,000 bytes | Can be replaced with Altibase GEOMETRY | |
| SDO_TOPO_GEOMETRY | - | Not supported in Altibase | |||
| SDO_GEORASTER | - | Not supported in Altibase | |||
| Media Types | ORDAudio | - | Not supported in Altibase | ||
| ORDDicom | - | Not supported in Altibase | |||
| ORDDoc | - | Not supported in Altibase | |||
| ORDImage | - | Not supported in Altibase | |||
| ORDVideo | - | Not supported in Altibase | |||
| still_image_object_types | - | Not supported in Altibase |
Object Conversion#
Describes considerations when converting Oracle objects to Altibase.
Object Comparison#
| Category | Oracle | Altibase |
|---|---|---|
| Schema Object | ANALYTIC VIEW | Not supported |
| ATTRIBUTE DIMENSION | Not supported | |
| CLUSTER | Not supported | |
| CONSTRAINT | Supported | |
| DATABASE LINK | Supported | |
| DATABASE TRIGGER | Supported | |
| DIMENSION | Not supported | |
| EXTERNAL PROCEDURE LIBRARY | Supported (C/C++ only) | |
| HIERARCHY | Not supported | |
| INDEX-ORGANIZED TABLE | Not supported | |
| INDEX | Supports B-TREE, R-TREE, and Function-based indexes (BITMAP, CLUSTER, and Global Partitioned INDEX are not supported) | |
| INDEXTYPE | Not supported | |
| JAVA-related objects | Not supported | |
| JOIN GROUP | Not supported | |
| MATERIALIZED VIEW | Supported | |
| MATERIALIZED VIEW LOG | Not supported | |
| MINING MODEL | Not supported | |
| OBJECT TABLE | Not supported | |
| OBJECT TYPE | Not supported | |
| OBJECT VIEW | Not supported | |
| OPERATOR | Not supported | |
| PACKAGE | Supported | |
| SEQUENCE | Supported | |
| STORED FUNCTION/PROCEDURE | Supported | |
| SYNONYM | Supported | |
| TABLE | Supported | |
| VIEW | Supported | |
| ZONE MAP | Not supported | |
| Nonschema Object | CONTEXT | Not supported |
| DIRECTORY | Supported | |
| EDITION | Not supported | |
| FLASHBACK ARCHIVEF | Not supported | |
| LOCKDOWN PROFILE | Not supported | |
| PROFILE | Not supported | |
| RESTORE POINT | Not supported | |
| ROLE | Supported | |
| ROLLBACK SEGMENT | Not supported | |
| TABLESPACE | MEMORY, DISK, VOLATILE, TEMPORARY, UNDO Supported | |
| TABLESPACE SET | Not supported | |
| UNIFIED AUDIT POLICY | Not supported | |
| USER | Supported |
CREATE TABLESPACE#
Oracle’s Data Tablespace is all Disk Tablespace. Altibase supports both Memory Tablespace and Disk Tablespace, allowing configuration according to business characteristics.
DATA TABLESPACE#
| Oracle | Altibase | Note |
|---|---|---|
| BIGFILE|SMALLFILE | - | Not supported by Altibase, so delete the corresponding option during conversion |
| DATAFILE File Specification | DATAFILE File Specification | |
| MINIMUM EXTENT | - | Not supported by Altibase, so delete the corresponding option during conversion |
| BLOCKSIZE | - | Not supported by Altibase, so delete the corresponding option during conversion |
| LOGGING|NOLOGGING | - | Not supported by Altibase, so delete the corresponding option during conversion |
| FORCE LOGGING | - | Not supported by Altibase, so delete the corresponding option during conversion |
| DEFAULT Storage statement | - | Not supported by Altibase, so delete the corresponding option during conversion |
| ONLINE|OFFLINE | Can be done with the ALTER TABLESPACE statement | For OFFLINE, since it is not supported in the CREATE TABLESPACE statement, delete the option and add the statement: ALTER TABLESPACE tablespace_name OFFLINE; |
| EXTENT MANAGEMENT LOCAL AUTOALLOCATE|UNIFORM SIZE | EXTENTSIZE | Extent management is not supported; only EXTENTSIZE can be set |
| SEGMENT SPACE MANAGEMENT AUTO|MANUAL | SEGMENT MANAGEMENT AUTO | MANUAL |
| FLASHBACK ON|OFF | - | Not supported by Altibase, so delete the corresponding option during conversion |
Example of CREATE DATA TABLESPACE conversion#
| Oracle | Altibase |
|---|---|
CREATE TABLESPACE TESTDATA DATAFILE 'testdata01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT MANUAL FLASHBACK ON; |
CREATE TABLESPACE TESTDATA DATAFILE 'testdata01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED SEGMENT MANAGEMENT MANUAL; |
- The SEGMENT clause is modified to SEGMENT MANAGEMENT AUTO|MANUAL, and other unsupported clauses are removed.
TEMPORARY TABLESPACE#
| Oracle | Altibase | 비고 |
|---|---|---|
| TABLESPACE GROUP | - | Not supported by Altibase, so delete the corresponding option during conversion |
| EXTENT MANAGEMENT LOCAL AUTOALLOCATE|UNIFORM SIZE | EXTENTSIZE | Extent management is not supported; only EXTENTSIZE can be configured |
Example of CREATE TEMPORARY TABLESPACE#
| Oracle | Altibase |
|---|---|
CREATE TEMPORARY TABLESPACE tbs_temp_02 TEMPFILE 'temp02.dbf' SIZE 5M AUTOEXTEND ON TABLESPACE GROUP tbs_grp_01; |
CREATE TEMPORARY TABLESPACE tbs_temp_02 TEMPFILE 'temp02.dbf' SIZE 5M AUTOEXTEND ON; |
- TABLESPACE GROUP clause is not supported and should be removed.
UNDO TABLESPACE#
Altibase automatically manages Undo Tablespaces by the system. Only adding data files and resizing are allowed for Undo Tablespaces.
Example of CREATE UNDO TABLESPACE#
| Oracle | Altibase |
|---|---|
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘undotbs2.dbf' SIZE 500M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED; |
ALTER TABLESPACE SYS_TBS_DISK_UNDO ADD DATAFILE 'undotbs2.dbf' SIZE 500M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED; |
CREATE TABLE#
Altibase does not support Object Tables or XMLType Tables, but it does support Memory Tables. Therefore, based on business characteristics, tables can be created by separating them into Memory Tables and Disk Tables.
When specifying segment-related details during TABLE creation, the order should be: specify TABLESPACE → specify PCTFREE/PCTUSED → specify INITRANS/MAXTRANS → specify Storage clause → specify Logging clause.
COLUMN DEFINITION 절#
| Oracle | Altibase | 비고 |
|---|---|---|
| SORT | - | Not supported by Altibase, so delete the corresponding option during conversion |
| DEFAULT | DEFAULT | |
| ENCRYPT | ENCRYPT | |
| Constraint statement | Constraint statement | Altibase does not support the ENABLE/DISABLE options for constraints or the ON DELETE SET NULL option in the REFERENCES clause (ON DELETE CASCADE is supported), so these should be removed. When specifying PRIMARY KEY or UNIQUE constraints in Altibase, only the Tablespace clause, Parallel clause, Logging clause, and Force clause are allowed in the Using Index clause. In other words, index names and CREATE INDEX clauses are not supported. |
| Ref Constraint syntax | - | Altibase does not support REF columns, so this option should be removed during conversion |
| ORGANIZATION | - | Not supported by Altibase, so delete the corresponding option during conversion |
| CLUSTER | - | Not supported by Altibase, so delete the corresponding option during conversion |
| COMPRESS | NOCOMPRESS | COMPRESS |
| Specifying the Byte keyword for column size | The Byte keyword is not supported when specifying column size. | Oracle allows specifying the Byte keyword for column size, but Altibase does not support it. Example: Oracle: c1 VARCHAR2(10 Byte) → Altibase: c1 VARCHAR2(10) |
- When specifying PRIMARY KEY or UNIQUE constraints using the USING INDEX clause to set index properties, Altibase only allows TABLESPACE, PARALLEL/NOPARALLEL, and LOGGING/NOLOGGING clauses. In other words, storage-related properties cannot be specified.
SEGMENT ATTRIBUTES clause#
| Oracle | Altibase | 비고 |
|---|---|---|
| TABLESPACE | TABLESPACE | |
| PCTFREE | PCTFREE | |
| PCTUSED | PCTUSED | |
| INITRANS | INITRANS | |
| MAXTRANS | MAXTRANS | Oracle default/maximum value: 255, Altibase default/maximum value: 120 When converting to Altibase, omit this parameter or change it to 120. Note that Oracle's MAXTRANS parameter is deprecated. |
| LOGGING|NOLOGGING | LOGGING|NOLOGGING |
STORAGE clause#
| Oracle | Altibase | 비고 |
|---|---|---|
| INITIAL | INITEXTENTS | Change from bytes to number of extents |
| NEXT | NEXTEXTENTS | Change from bytes to number of extents |
| MINEXTENTS | MINEXTENTS | |
| MAXEXTENTS | MAXEXTENTS | |
| PCTINCREASE | - | Not supported by Altibase, so delete the corresponding option during conversion |
| FREELISTS | - | Not supported by Altibase, so delete the corresponding option during conversion |
| FREELIST | - | Not supported by Altibase, so delete the corresponding option during conversion |
| OPTIMAL | - | Not supported by Altibase, so delete the corresponding option during conversion |
| BUFFER POOL | - | Not supported by Altibase, so delete the corresponding option during conversion |
LOB STORAGE clause#
| Oracle | Altibase | 비고 |
|---|---|---|
| TABLESPACE | TABLESPACE | Altibase의 LOB STORAGE절은 TABLESPACE만 지정 가능 |
| STORAGE | - | Not supported by Altibase, so delete the corresponding option during conversion |
| CHUNK | - | Not supported by Altibase, so delete the corresponding option during conversion |
| PCTVERSION | - | Not supported by Altibase, so delete the corresponding option during conversion |
| RETENTION | - | Not supported by Altibase, so delete the corresponding option during conversion |
| FREEPOOLS | - | Not supported by Altibase, so delete the corresponding option during conversion |
| CACHE | - | Not supported by Altibase, so delete the corresponding option during conversion |
| STORAGE IN ROW | - | Not supported by Altibase, so delete the corresponding option during conversion |
| LOGGING|NOLOGGING | LOGGING|NOLOGGING |
TABLE PARTITION clause#
| Oracle | Altibase | Note |
|---|---|---|
| PARTITION BY RANGE | PARTITION BY RANGE | |
| PARTITION BY HASH | PARTITION BY HASH | |
| PARTITION BY LIST | PARTITION BY LIST | |
| Composite partitioning statement | - | Not supported by Altibase, so delete the corresponding option during conversion |
TABLE PROPERTIES clause#
| Oracle | Altibase | Note |
|---|---|---|
| ENABLE|DISABLE ROW MOVEMENT | ENABLE|DISABLE ROW MOVEMENT | Supported only for partitioned TABLEs |
| NOPARALLEL|PARALLEL | NOPARALLEL|PARALLEL | |
| ENABLE|DISABLE VALIDATE|NOVALIDATE | - | Not supported by Altibase, so delete the corresponding option during conversion |
example of CREATE TABLE#
| Oracle | Altibase |
|---|---|
CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 Byte), "JOB" VARCHAR2(9 Byte), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0), CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE, CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ; |
CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0), CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX TABLESPACE "USERS" , CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ) TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 120 STORAGE(INITEXTENTS 1 NEXTEXTENTS 1 MINEXTENTS 1 MAXEXTENTS 2147483645) LOGGING ; |
- "ENAME" VARCHAR2(10): Remove Byte
- USING INDEX TABLESPACE "USERS": When specifying PK, only TABLESPACE, PARALLEL/NOPARALLEL, and LOGGING/NOLOGGING clauses are allowed in the USING INDEX clause, so remove options other than TABLESPACE
- REFERENCES "SCOTT"."DEPT" ("DEPTNO"): For FK, ENABLE option is not supported, so remove it
- PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 120: Since the maximum value for MAXTRANS is 120, change it to 120
- STORAGE(INITEXTENTS 1 NEXTEXTENTS 1 MINEXTENTS 1 MAXEXTENTS 2147483645): Change INITIAL and NEXT to INITEXTENTS and NEXTEXTENTS respectively, and adjust their values to the number of extents
- TABLESPACE "USERS"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 120
STORAGE...
LOGGING: When specifying segment-related options, follow this order: specify TABLESPACE → specify PCTFREE/PCTUSED → specify INITRANS/MAXTRANS → specify Storage clause → specify Logging clause
CREATE INDEX#
The types of indexes supported by Altibase are BTREE, RTREE, and Function-based INDEX. BITMAP, CLUSTER, REVERSE, and Global partitioned INDEX are not supported.
When specifying segment-related details during Altibase INDEX creation, the order should be: specify TABLESPACE → specify PARALLEL/NOPARALLEL → specify LOGGING/NOLOGGING → specify Storage clause.
| Oracle | Altibase | 비고 |
|---|---|---|
| TABLESPACE | TABLESPACE | |
| LOGGING|NOLOGGING | LOGGING|NOLOGGING | |
| NOPARALLEL|PARALLEL | NOPARALLEL|PARALLEL | |
| COMPUTE STATISTICS | - | Not supported by Altibase, so delete the corresponding option during conversion |
| REVERSE | - | Not supported by Altibase, so delete the corresponding option during conversion |
| SORT|NOSORT | - | Not supported by Altibase, so delete the corresponding option during conversion |
| ONLINE | - | Not supported by Altibase, so delete the corresponding option during conversion |
| COMPRESS|NOCOMPRESS | - | Not supported by Altibase, so delete the corresponding option during conversion |
| PCTFREE, PCTUSED | - | Not supported by Altibase, so delete the corresponding option during conversion |
| INITRANS | INITRANS | |
| MAXTRANS | MAXTRANS | Oracle default/maximum value: 255, Altibase default/maximum value: 30 When converting to Altibase, omit this parameter or change it to 30. Note that Oracle's MAXTRANS parameter is deprecated. |
| Storage statement | Same as the TABLE Storage clause |
example of CREATE INDEX#
| Oracle | Altibase |
|---|---|
CREATE INDEX "SCOTT"."EMP_IDX1" ON "SCOTT"."EMP" ("DEPTNO", "SAL") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ; |
CREATE INDEX "SCOTT"."EMP_IDX1" ON "SCOTT"."EMP" ("DEPTNO", "SAL") TABLESPACE "USERS" INITRANS 2 MAXTRANS 30 STORAGE(INITEXTENTS 1 NEXTEXTENTS 1 MINEXTENTS 1 MAXEXTENTS 2147483645) ; |
- INITRANS 2 MAXTRANS 30: Since MAXTRANS supports up to 30, change it to 30
- STORAGE(INITEXTENTS 1 NEXTEXTENTS 1 MINEXTENTS 1 MAXEXTENTS 2147483645): Only INITEXTENTS, NEXTEXTENTS, MINEXTENTS, and MAXEXTENTS can be specified in the STORAGE clause. Change INITIAL and NEXT to INITEXTENTS and NEXTEXTENTS respectively, and adjust their values to the number of extents
- TABLESPACE "USERS"
INITRANS 2 MAXTRANS 30
STORAGE ...
When specifying segment-related details, follow this order: specify TABLESPACE → specify PARALLEL/NOPARALLEL → specify LOGGING/NOLOGGING → specify Storage clause
CREATE VIEW#
Altibase VIEWs are created using the CREATE OR REPLACE VIEW statement, the same syntax as Oracle VIEW creation.
| Oracle | Altibase | 비고 |
|---|---|---|
| WITH READ ONLY | WITH READ ONLY | If this option is not specified, an updatable view is created |
| NO FORCE | NO FORCE | |
| WITH CHECK OPTION | - | Not supported by Altibase, so delete the corresponding option during conversion |
| XMLType view statement | - | Not supported by Altibase, so delete the corresponding option during conversion |
| Object view statement | - | Not supported by Altibase, so delete the corresponding option during conversion |
CREATE TRIGGER#
Altibase triggers are created using the CREATE OR REPLACE TRIGGER statement, the same syntax as Oracle.
When using Altibase replication, data changes reflected by replication do not trigger trigger execution, so this behavior should be considered in the business logic.
| Oracle | Altibase | 비고 |
|---|---|---|
| CREATE OR REPLACE TRIGGER | CREATE OR REPLACE TRIGGER | |
| BEFORE|AFTER|INSTEAD OF | BEFORE|AFTER|INSTEAD OF | |
| DML event statement | DML event statement | DML event statements are the same as in Oracle |
| DDL event statement | - | Altibase does not support DDL triggers |
| WHEN condition | WHEN condition | |
| FOR EACH ROW | FOR EACH ROW | |
| REFERENCING | REFERENCING | In Altibase, OLD and NEW are keywords and cannot be used as aliases |
| Trigger body statement | Trigger body statement |
CREATE SEQUENCE#
Altibase SEQUENCE is created using the CREATE SEQUENCE statement, the same syntax as Oracle SEQUENCE creation.
| Oracle | Altibase | 비고 |
|---|---|---|
| CREATE SEQUENCE | CREATE SEQUENCE | |
| START WITH | START WITH | |
| INCREMENT BY | INCREMENT BY | |
| MAXVALUE|NOMAXVALUE | MAXVALUE|NOMAXVALUE | Oracle’s maxvalue can be specified up to a 28-digit integer. Altibase’s maxvalue can be specified within the range of -9,223,372,036,854,775,805 to 9,223,372,036,854,775,806. |
| MINVALUE|NOMINVALUE | MINVALUE|NOMINVALUE | |
| CYCLE|NOCYCLE | CYCLE|NOCYCLE | |
| CACHE|NOCACHE | CACHE|NOCACHE | |
| ORDER|NOORDER | Not supported by Altibase, so delete the corresponding option during conversion | |
| KEEP|NOKEEP | Not supported by Altibase, so delete the corresponding option during conversion | |
| SESSION|GLOBAL | Not supported by Altibase, so delete the corresponding option during conversion |
CREATE SYNONYM#
Altibase's CREATE SYNONYM statement is the same as Oracle's, except that the EDITIONABLE and NONEDITIONABLE options are not supported.
ALTER TABLE#
Altibase allows adding only one constraint at a time.
In Oracle, multiple constraints can be added using the statement: ALTER TABLE ADD (CONSTRAINT constraint_name constraint_type, …);
But in Altibase, this must be split into separate statements for each constraint: ALTER TABLE ADD CONSTRAINT constraint_name constraint_type;
In Oracle, PRIMARY KEY and UNIQUE constraints can be specified after pre-creating the index. However, in Altibase, the index is created internally at the time of constraint definition, so only one of PK constraint, UNIQUE constraint, or INDEX creation is allowed on the same column. This means you cannot define a PK constraint on a column that already has an index.
SQL Conversion#
Numeric Functions#
| Oracle | Altibase | Note |
|---|---|---|
| ABS(n) | ABS(n) | Returns the absolute value of n |
| ACOS(n) | ACOS(n) | Returns the arc cosine of n |
| ASIN(n) | ASIN(n) | Returns the arc sine of n |
| ATAN(n) | ATAN(n) | Returns the arc tangent of n |
| ATAN2(n1, n2) | ATAN2(n1, n2) | Returns the arc tangent of n1/n2 in radians |
| BITAND(expr1, expr2) | BITAND(expr1, expr2) | Performs bitwise AND between expr1 and expr2 and returns an integer |
| CEIL(n) | CEIL(n) | Returns the smallest integer greater than or equal to n |
| COS(n) | COS(n) | Returns the cosine of n |
| COSH(n) | COSH(n) | Returns the hyperbolic cosine of n |
| EXP(n) | EXP(n) | Returns e raised to the power of n |
| FLOOR(n) | FLOOR(n) | Returns the largest integer less than or equal to n |
| LN(n) | LN(n) | Returns the natural logarithm of n |
| LOG(n2, n1) | LOG(n2, n1) | Returns the logarithm of n1 with base n2 |
| MOD(n2, n1) | MOD(n2, n1) | Returns the remainder of n2 divided by n1 |
| NANVL(n2, n1) | Not Supported | Returns n1 if n2 is NaN, otherwise returns n2 |
| POWER(n2, n1) | POWER(n2, n1) | Returns n2 raised to the power of n1 |
| REMAINDER(n2, n1) | MOD(n2, n1) | Returns the remainder of n2 divided by n1 |
| ROUND(number) | ROUND(number) | Rounds number to the nearest integer |
| SIGN(n) | SIGN(n) | Returns the sign of n |
| SIN(n) | SIN(n) | Returns the sine of n |
| SINH(n) | SINH(n) | Returns the hyperbolic sine of n |
| SQRT(n) | SQRT(n) | Returns the square root of n |
| TAN(n) | TAN(n) | Returns the tangent of n |
| TANH(n) | TANH(n) | Returns the hyperbolic tangent of n |
| TRUNC(number) | TRUNC(number) | Truncates number to the specified decimal places |
| WIDTH_BUCKET | Not Supported | Creates histograms with equal width |
Character Functions Returning Character Values#
| Oracle | Altibase | Note |
|---|---|---|
| CHR | CHR | Returns the character corresponding to the input ASCII code value |
| CONCAT | CONCAT | Concatenates char1 and char2 and returns the result |
| INITCAP | INITCAP | Converts the first letter of each word in the input string to uppercase and returns it |
| LOWER | LOWER | Converts the input string to lowercase and returns it |
| LPAD | LPAD | Inserts expr2 repeatedly on the left side of expr1 until the total length is n and returns it |
| LTRIM | LTRIM | Removes characters matching expr2 from the left side of expr1 and returns it |
| NCHR | NCHR | Returns the character corresponding to the value n in the national character set |
| NLS_INITCAP | Not Supported | Converts the first letter of each word in the input string to uppercase and returns it |
| NLS_LOWER | Not Supported | Converts all characters to lowercase and returns it |
| NLS_UPPER | Not Supported | Converts the input string to uppercase and returns it |
| NLSSORT | Not Supported | Sorts the input string and returns it |
| REGEXP_REPLACE | REGEXP_REPLACE | Replaces parts matching the specified regular expression with another string and returns it |
| REGEXP_SUBSTR | REGEXP_SUBSTR | Returns the substring matching the specified regular expression |
| REPLACE | REPLACE2 | Replaces all occurrences of the second string with the third string in the first string and returns the result |
| RPAD | RPAD | Inserts expr2 repeatedly on the right side of expr1 until the total length is n and returns it |
| RTRIM | RTRIM | Removes characters matching expr2 from the right side of expr1 and returns it |
| SOUNDEX | Not Supported | Returns a string representing the phonetic expression of the character |
| SUBSTR | SUBSTR | Returns the substring from expr starting at position start for length characters |
| TRANSLATE | TRANSLATE | Replaces each character in from_string with the corresponding character in to_string and returns it |
| TRANSLATE ... USING | Not Supported | Converts characters between database character set and national character set and returns it |
| TRIM | TRIM | Removes all characters found in expr2 from the beginning and end of expr1 and returns it |
| UPPER | UPPER | Converts all characters to uppercase and returns it |
Character Functions Returning Number Values#
| Oracle | Altibase | Note |
|---|---|---|
| ASCII | ASCII | Returns the decimal value corresponding to the ASCII code of the first character in the given char |
| INSTR | INSTR | Returns the position (number) where the specified character first appears in the string |
| LENGTH | CHAR_LENGTH CHARACTER_LENGTH LENGTH |
Returns the length of the argument char |
| REGEXP_COUNT | REGEXP_COUNT | Returns the number of occurrences of pattern_expr in the string |
| REGEXP_INSTR | REGEXP_INSTR | Returns the position of the first occurrence that matches the specified pattern (regular expression) |
Character Set Functions#
| Oracle | Altibase | Note |
|---|---|---|
| NLS_CHARSET_DECL_LEN NLS_CHARSET_ID NLS_CHARSET_NAME |
Not supported | Returns the database character set ID and name |
Collation Functions#
| Oracle | Altibase | Note |
|---|---|---|
| COLLATION NLS_COLLATION_ID NLS_COLLATION_NAME |
Not supported | Returns information about data collation settings |
Datetime Functions#
| Oracle | Altibase | Note |
|---|---|---|
| +, - operations | +, -, DATEADD | Returns the result of increasing or decreasing the specified date_field_name part of a date by a given number |
| ADD_MONTHS | ADD_MONTHS | Returns the result of adding a specific number of months (integer) to a date |
| CURRENT_DATE | CURRENT_DATE | Returns the current session's date as a DATE type |
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | Returns the current session’s date and time (alias of CURRENT_DATE) |
| DBTIMEZONE | DB_TIMEZONE | Returns the database time zone value |
| EXTRACT(datetime) | EXTRACT(datetime) DATEPART | Extracts and returns the value of the specified date/time field from a datetime or interval expression |
| FROM_TZ | Not Supported | Converts a timestamp and time zone to a TIMESTAMP WITH TIME ZONE value |
| LAST_DAY | LAST_DAY | Returns the last day of the month in which the given date falls |
| LOCALTIMESTAMP | Not Supported | Returns the current date and time in the session time zone as a TIMESTAMP value |
| MONTHS_BETWEEN | MONTHS_BETWEEN | Returns the number of months between date1 and date2 |
| NEW_TIME | Not Supported | Converts a date and time from timezone1 to timezone2 and returns the result |
| NEXT_DAY | NEXT_DAY | Returns the date of the next occurrence of a specified weekday after the given date |
| NUMTODSINTERVAL | NUMTODSINTERVAL TO_INTERVAL | Converts n to an INTERVAL DAY TO SECOND value |
| NUMTOYMINTERVAL | TO_INTERVAL | Converts n to an INTERVAL YEAR TO MONTH value |
| ORA_DST_AFFECTED | Not Supported | |
| ORA_DST_CONVERT | Not Supported | |
| ORA_DST_ERROR | Not Supported | |
| ROUND(date) | ROUND(date) | Returns the date rounded to the specified unit |
| SESSIONTIMEZONE | SESSION_TIMEZONE | Returns the time zone of the current session |
| SYS_EXTRACT_UTC | Not Supported | Returns the UTC (Coordinated Universal Time) |
| SYSDATE | SYSDATE | Returns the current date and time of the operating system hosting the database |
| SYSTIMESTAMP | SYSTIMESTAMP | Returns the system date as a TIMESTAMP WITH TIME ZONE value |
| TO_CHAR(datetime) | TO_CHAR(datetime) | Converts to a VARCHAR value using the specified format |
| TO_DSINTERVAL | Not Supported | Converts the argument to an INTERVAL DAY TO SECOND value |
| TO_TIMESTAMP | TO_DATE | Converts a char value to a TIMESTAMP value |
| TO_TIMESTAMP_TZ | Not Supported | Converts a char value to a TIMESTAMP WITH TIME ZONE value |
| TO_YMINTERVAL | Not Supported | Converts a string to an INTERVAL YEAR TO MONTH value |
| TRUNC(date) | TRUNC(date) | Truncates the date to the specified unit |
| TZ_OFFSET | Not Supported | Returns the time zone offset corresponding to the input based on the date the statement is executed |
General Comparison Functions#
| Oracle | Altibase | Note |
|---|---|---|
| GREATEST | GREATEST | Returns the greatest value among one or more arguments |
| LEAST | LEAST | Returns the least value among one or more arguments |
Conversion Functions#
| Oracle | Altibase | Note |
|---|---|---|
| ASCIISTR | ASCIISTR | Returns the ASCII string representation of the input string |
| BIN_TO_NUM | BIN_TO_NUM | Converts a bit (binary) vector to its equivalent numeric (decimal) value |
| CAST | CAST | Converts one data type to another |
| CHARTOROWID | Not Supported | Converts a character value to a ROWID |
| COMPOSE | Not Supported | Returns a normalized Unicode string |
| CONVERT | CONVERT | Converts a string to the specified character set |
| DECOMPOSE | Not Supported | Returns a decomposed Unicode string using the same character set as input |
| HEXTORAW | Not Supported | Converts a hexadecimal string to a RAW data type |
| NUMTODSINTERVAL | NUMTODSINTERVAL TO_INTERVAL | Converts n to an INTERVAL DAY TO SECOND data type |
| NUMTOYMINTERVAL | TO_INTERVAL | Converts n to an INTERVAL YEAR TO MONTH data type |
| RAWTOHEX | Not Supported | Converts RAW to a hexadecimal string |
| RAWTONHEX | Not Supported | Converts RAW to a hexadecimal string in NVARCHAR2 format |
| RAWIDTOCHAR | Not Supported | Converts a RAWID to VARCHAR2 |
| ROWIDTONCHAR | Not Supported | Converts a RAWID to NVARCHAR2 |
| SCN_TO_TIMESTAMP | Not Supported | Converts a system change number (SCN) to a TIMESTAMP |
| TIMESTAMP_TO_SCN | Not Supported | Returns the approximate SCN associated with the input TIMESTAMP |
| TO_BINARY_DOUBLE | Not Supported | Converts input to a double-precision floating-point number |
| TO_BINARY_FLOAT | Not Supported | Converts input to a single-precision floating-point number |
| TO_BLOB(bfile) | Not Supported | Converts a BFILE to a BLOB |
| TO_BLOB(raw) | Not Supported | Converts a RAW value to a BLOB |
| TO_CHAR(bfile|blob) | Not Supported | Converts BFILE or BLOB to the database character set |
| TO_CHAR(character) | Not Supported | Converts NCHAR, NVARCHAR2, CLOB, or NCLOB to the database character set |
| TO_CHAR(datetime) | TO_CHAR(datetime) | Converts datetime or interval to a VARCHAR in the specified format |
| TO_CHAR(number) | TO_CHAR(number) | Converts a number to a VARCHAR |
| TO_CLOB(bfile|blob) | Not Supported | Converts BFILE or BLOB to a CLOB using the database character set |
| TO_CLOB(character) | Not Supported | Converts NCLOB or other strings to a CLOB |
| TO_DATE | TO_DATE | Converts a character string to a DATE data type |
| TO_DSINTERVAL | Not Supported | Converts input to INTERVAL DAY TO SECOND |
| TO_LOB | Not Supported | Converts LONG or LONG RAW to LOB |
| TO_MULTI_BYTE | Not Supported | Converts all single-byte characters in the input to multibyte form |
| TO_NCHAR(character) | TO_NCHAR(character) | Converts string, CLOB, or NCLOB to the national character set |
| TO_NCHAR(datetime) | TO_NCHAR(datetime) | Converts datetime or interval to national character set |
| TO_NCHAR(number) | TO_NCHAR(number) | Converts number to national character set |
| TO_NCLOB | Not Supported | Converts a CLOB to an NCLOB |
| TO_NUMBER | TO_NUMBER | Converts an expression to a NUMBER |
| TO_SINGLE_BYTE | Not Supported | Converts all multibyte characters in the input to single-byte form |
| TO_TIMESTAMP | TO_DATE | Converts a character string to TIMESTAMP |
| TO_TIMESTAMP_TZ | Not Supported | Converts a character string to TIMESTAMP WITH TIME ZONE |
| TO_YMINTERVAL | Not Supported | Converts a string to an INTERVAL YEAR TO MONTH |
| TREAT | Not Supported | Used for object-oriented type conversion from a supertype to a subtype |
| UNISTR | UNISTR | Represents or converts a Unicode string |
| VALIDATE_CONVERSION | Not Supported | Checks whether an expression can be converted to a specified data type |
Large Object Functions#
| Oracle | Altibase | Note |
|---|---|---|
| BFILENAME | Not Supported | Returns a BFILE locator associated with a physical LOB binary file in the server file system |
| EMPTY_BLOB / EMPTY_CLOB | EMPTY_BLOB / EMPTY_CLOB | Function to initialize LOB variables |
Collection Functions#
| Oracle | Altibase | Note |
|---|---|---|
| CARDINALITY COLLECT POWERMULTISET POWERMULTISET_BY_CARDINALITY SET |
Not Supported | Function performed on nested tables and varrays |
Hierarchical Functions#
| Oracle | Altibase | Note |
|---|---|---|
| SYS_CONNECT_BY_PATH | SYS_CONNECT_BY_PATH | Function that returns the path of column values from the root node to the current node |
Data Mining Functions#
| Oracle | Altibase | Note |
|---|---|---|
| CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY CLUSTER_SET FEATURE_COMPARE FEATURE_DETAILS FEATURE_ID FEATURE_SET FEATURE_VALUE ORA_DM_PARTITION_NAME PREDICTION PREDICTION_BOUNDS PREDICTION_COST PREDICTION_DETAILS PREDICTION_PROBABILITY PREDICTION_SET |
Not Supported | Functions related to data mining |
XML Functions#
| Oracle | Altibase | Note |
|---|---|---|
| DEPTH EXISTSNODE EXTRACT (XML) EXTRACTVALUE PATH SYS_DBURIGEN SYS_XMLAGG SYS_XMLGEN XMLAGG XMLCAST XMLCDATA XMLCOLATTVAL XMLCOMMENT XMLCONCAT XMLDIFF XMLELEMENT XMLEXISTS XMLFOREST XMLISVALID XMLPARSE XMLPATCH XMLPI XMLQUERY XMLROOT XMLSEQUENCE XMLSERIALIZE XMLTABLE XMLTRANSFORM |
Not Supported | Functions related to XML |
JSON Functions#
| Oracle | Altibase | Note |
|---|---|---|
| JSON_QUERY JSON_TABLE JSON_VALUE JSON_ARRAY JSON_ARRAYAGG JSON_OBJECT JSON_OBJECTAGG JSON_DATAGUIDE |
Not Supported | Functions related to JSON |
Encoding and Decoding Functions#
| Oracle | Altibase | Note |
|---|---|---|
| DECODE | DECODE | Function used to implement IF logic within SQL or PL/SQL |
| DUMP | DUMP | Returns information such as position and length of the specified data in a given format |
| ORA_HASH | Not Supported | Function that calculates a hash value for the given expression |
| STANDARD_HASH | Not Supported | Calculates a standard hash value |
| VSIZE | OCTET_LENGTH | Returns the length of the input string in bytes |
NULL-Related Functions#
| Oracle | Altibase | Note |
|---|---|---|
| COALESCE | COALESCE | Returns the first non-NULL expression from the list of arguments, checked in order |
| LNNVL | LNNVL | Returns TRUE if the condition is FALSE or NULL; returns FALSE if the condition is TRUE |
| NANVL | Not Supported | Detects only NaN (Not a Number) floating-point values and replaces them with another value |
| NULLIF | NULLIF | Returns NULL if expr1 equals expr2; otherwise returns expr1 |
| NVL | NVL | Replaces NULL values in query results |
| NVL2 | NVL2 | Returns expr2 if expr1 is not NULL; otherwise returns expr3 |
Environment and Identifier Functions#
| Oracle | Altibase | Note |
|---|---|---|
| CON_DBID_TO_ID CON_GUID_TO_ID CON_NAME_TO_ID CON_UID_TO_ID ORA_INVOKING_USER ORA_INVOKING_USERID |
Not Supported | |
| SYS_CONTEXT | SYS_CONTEXT | Returns the value of a parameter related to the current session's environment using a namespace |
| SYS_GUID | SYS_GUID_STR | Generates and returns a globally unique 16-byte identifier (RAW value) |
| SYS_TYPEID | Not Supported | Returns the type ID of the identifier |
| UID | USER_ID | Returns a unique integer that identifies the session user |
| USER | USER_NAME | Returns the name of the session user |
| USERENV | SESSION_ID | USERENV returns information about the session; SESSION_ID returns the user's session ID |
Aggregate/Analytic Functions#
| Oracle | Altibase | Note |
|---|---|---|
| AVG | AVG | Returns the average of non-NULL values in the specified column that meet the condition |
| CORR | CORR | Returns the correlation coefficient for pairs of numeric values |
| COUNT | COUNT | Returns the number of rows returned by the query |
| COVAR_POP | COVAR_POP | Returns the population covariance of a set of number pairs |
| COVAR_SAMP | COVAR_SAMP | Returns the sample covariance of a set of number pairs |
| CUME_DIST | CUME_DIST | Calculates the cumulative distribution of a value within a group |
| DENSE_RANK | DENSE_RANK | Assigns ranks to rows in order by specified columns or expressions; unlike RANK, the next rank increases by 1 regardless of ties |
| FIRST | FIRST | Used with aggregate functions (MIN, MAX, etc.) to get the first row’s value based on ordering |
| LAST | LAST | Used with aggregate functions (MIN, MAX, etc.) to get the last row’s value based on ordering |
| LISTAGG | LISTAGG | Aggregate function that concatenates string values from multiple rows into a single string |
| MAX | MAX | Returns the maximum value among the arguments |
| MIN | MIN | Returns the minimum value among the arguments |
| PERCENT_RANK | PERCENT_RANK | Returns the relative rank percentage of a value within a group |
| PERCENTILE_CONT | PERCENTILE_CONT | Inverse distribution function assuming a continuous distribution model |
| PERCENTILE_DISC | PERCENTILE_DISC | Inverse distribution function assuming a discrete distribution model |
| RANK | RANK | Calculates the rank of a value within a group |
| REGR_ (Linear Regression) Functions | Not Supported | Aggregate functions for linear regression |
| STDDEV | STDDEV | Returns the sample standard deviation of a numeric expression |
| STDDEV_POP | STDDEV_POP | Calculates the population standard deviation, the square root of population variance |
| STDDEV_SAMP | STDDEV_SAMP | Calculates the cumulative sample standard deviation, the square root of sample variance |
| SUM | SUM | Returns the sum of values in an expression |
| VAR_POP | VAR_POP | Returns the population variance of a set of numbers after excluding NULL values |
| VAR_SAMP | VAR_SAMP | Returns the sample variance of a set of numbers after excluding NULL values |
| VARIANCE | VARIANCE | Returns the variance of an expression |
Aggregate Functions#
| Oracle | Altibase | Note |
|---|---|---|
| APPROX_COUNT_DISTINCT APPROX_COUNT_DISTINCT_AGG APPROX_COUNT_DISTINCT_DETAIL APPROX_MEDIAN APPROX_PERCENTILE APPROX_PERCENTILE_AGG APPROX_PERCENTILE_DETAIL |
Not Supported | |
| CORR_* (CORR_S, CORR_K) | Not Supported | Calculates Pearson’s correlation coefficient |
| GROUP_ID | Not Supported | Distinguishes duplicate groups from specified GROUP BY results |
| GROUPING | GROUPING | Function to distinguish aggregation levels when using ROLLUP, CUBE, GROUPING SETS |
| GROUPING_ID | GROUPING_ID | Returns a number corresponding to the GROUPING bit vector related to the row |
| MEDIAN | MEDIAN | Aggregate/analytic function to find the median value |
| STATS_BINOMIAL_TEST | Not Supported | Exact probability test used for binary variables with only two valid exclusive values |
| STATS_CROSSTAB | Not Supported | Method for analyzing two nominal variables |
| STATS_F_TEST | Not Supported | Tests whether two variances have a significant difference |
| STATS_KS_TEST | Not Supported | Kolmogorov-Smirnov function to test whether two samples come from the same population or distribution |
| STATS_MODE | Not Supported | Returns the value with the highest frequency |
| STATS_MW_TEST | Not Supported | Mann-Whitney test comparing two independent samples |
| STATS_ONE_WAY_ANOVA | STATS_ONE_WAY_ANOVA | Tests for statistically significant differences among means (groups or variables) by comparing two variance estimates |
| STATS_T_TEST_* | Not Supported | t-Test to measure the significance of difference between means |
| STATS_WSR_TEST | Not Supported | Performs Wilcoxon Signed-Rank test for paired samples, testing if differences are significantly different from zero |
| SYS_OP_ZONE_ID | Not Supported | Takes rowid as an argument and returns the zone ID |
| TO_APPROX_COUNT_DISTINCT | Not Supported | |
| TO_APPROX_PERCENTILE | Not Supported |
Analytic Functions#
| Oracle | Altibase | Note |
|---|---|---|
| CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY CLUSTER_SET |
Not Supported | |
| FEATURE_DETAILS FEATURE_ID FEATURE_SET FEATURE_VALUE |
Not Supported | |
| FIRST_VALUE * | FIRST_VALUE | Returns the first value in an ordered set |
| LAG | LAG | Refers to the previous value relative to the current row |
| LAST_VALUE * | LAST_VALUE | Returns the last value in an ordered window |
| LEAD | LEAD | Refers to the subsequent value relative to the current row |
| NTH_VALUE * | Returns the value at the specified offset row | |
| NTILE | NTILE | Divides the output into the user-specified number of groups |
| PREDICTION PREDICTION_COST PREDICTION_DETAILS PREDICTION_PROBABILITY PREDICTION_SET |
Not Supported | |
| RATIO_TO_REPORT | RATIO_TO_REPORT | Calculates the ratio of a value to the sum of the set of values |
| ROW_NUMBER | ROW_NUMBER | Assigns a rank to ordered results within each partition |
Object Reference Functions#
| Oracle | Altibase | Note |
|---|---|---|
| DEREF MAKE_REF REF REFTOHEX VALUE |
Not Supported | Object Reference Functions |
Model Functions#
| Oracle | Altibase | Note |
|---|---|---|
| CV ITERATION_NUMBER PRESENTNNV PRESENTV PREVIOUS |
Not Supported | Only usable in the Model_clause of a Select statement |
OLAP Functions#
| Oracle | Altibase | Note |
|---|---|---|
| CUBE_TABLE | Not Supported | Convert 3D data into 2D data |
Data Cartridge Functions#
| Oracle | Altibase | Note |
|---|---|---|
| DATAOBJ_TO_MAT_PARTITION DATAOBJ_TO_PARTITION |
Not Supported | Useful for data cartridge development |
Pseudocolumns#
| Oracle | Altibase | Note |
|---|---|---|
| ROWID | Not Supported | |
| ROWNUM | ROWNUM | Order value of selected ROW, not supported in DML |
Expressions#
| Oracle | Altibase | Note |
|---|---|---|
| CASE | CASE, CASE2 | If expr1 is true, returns ret_expr1 |
Stored Procedure or Function Conversion#
In Altibase AUTOCOMMIT mode#
Procedures are processed as a single transaction. Therefore, COMMIT/ROLLBACK statements inside the procedure are ignored, and an automatic COMMIT occurs after the procedure execution.
In contrast, Oracle executes COMMIT/ROLLBACK inside procedures/functions even in AUTOCOMMIT mode, which may lead to different results from Altibase.
In NON-AUTOCOMMIT mode, procedure execution results are the same between Altibase and Oracle.
| Oracle | Altibase |
|---|---|
CREATE OR REPLACE PROCEDURE t1_test( in_t IN INTEGER, in_v IN VARCHAR) IS BEGIN INSERT INTO t1 VALUES(in_t, in_v); ROLLBACK; END; / EXEC t1_test(4, '000004'); SQL> SELECT COUNT(*) FROM t1; COUNT(*) ----------------- 0 |
CREATE OR REPLACE PROCEDURE t1_test( in_t IN INTEGER, in_v IN VARCHAR(20)) IS BEGIN INSERT INTO t1 VALUES(in_t, in_v); ROLLBACK; END; / EXEC t1_test(4, '000004'); iSQL> SELECT COUNT(*) FROM t1; COUNT ----------- 1 |
PARAMETER TYPES and RETURN TYPES of PROCEDURE/FUNCTION#
Oracle’s PARAMETER TYPE and RETURN TYPE do not allow size specification and automatically permit the maximum size.
However, even for the same type, the maximum size in SQL and PL/SQL can differ, as detailed in the table below.
In contrast, Altibase’s PARAMETER TYPE and RETURN TYPE allow size specification.
If size is not specified, the maximum size of PARAMETER TYPE and RETURN TYPE is determined according to the following property settings:
- PSM_PARAM_AND_RETURN_WITHOUT_PRECISION_ENABLE = 0
The size of CHAR, NCHAR, NVARCHAR, VARCHAR is 1. - PSM_PARAM_AND_RETURN_WITHOUT_PRECISION_ENABLE = 1 (default)
The size of CHAR, NCHAR, NVARCHAR, VARCHAR is determined by the property settings in the table below.
Maximum size of data types in PROCEDURE/FUNCTION#
| Data Type | Maximum Size in Oracle SQL | Maximum Size in Oracle PL/SQL | Maximum Size in Altibase SQL | Maximum Size in Altibase PL/SQL | Maximum Size in Altibase PL/SQL When Size Not Specified |
|---|---|---|---|---|---|
| Maximum Size in Oracle SQL | 2000 bytes | 32767 bytes | 32000 bytes | 65534 bytes | PSM_CHAR_DEFAULT_PRECISION (default: 32767) |
| Maximum Size in Oracle PL/SQL | 1000(UTF16), 666(UTF8) 최대 2000 bytes |
16383(UTF16), 10922(UTF8) Max 32767 bytes |
16000(UTF16), 10666(UTF8) Max 32000 bytes |
32766(UTF16), 21843(UTF8) Max 65534 bytes |
PSM_NCHAR_UTF16_DEFAULT_PRECISION (default: 16383) PSM_NCHAR_UTF8_DEFAULT_PRECISION (default: 10921) |
| Maximum Size in Altibase SQL | 2000 bytes | 32767 bytes | |||
| Maximum Size in Altibase PL/SQL | 4000 bytes | 32767 bytes | 32000 bytes | 65534 bytes | PSM_VARCHAR_DEFAULT_PRECISION (default: 32767) |
| Maximum Size in Altibase PL/SQL When Size Not Specified | 2000(UTF16), 1333(UTF8) 최대 4000 bytes |
16383(UTF16), 10922(UTF8) Max 32767 bytes |
16000(UTF16), 10666(UTF8) Max 32000 bytes |
32766(UTF16), 21843(UTF8) Max 65534 bytes |
PSM_NVARCHAR_UTF16_DEFAULT_PRECISION (default: 16383) PSM_NVARCHAR_UTF8_DEFAULT_PRECISION (default: 10921) |
| LONG | 2 GB - 1 | 32760 bytes | |||
| LONG RAW | 2 GB | 32760 bytes | |||
| BLOB | (4 GB - 1) * (database block size) | 128 TB | 4 GB - 1 | 100 MB | LOB_OBJECT_BUFFER_SIZE (default: 32000) |
| CLOB | (4 GB - 1) * (database block size) | 128 TB | 4 GB - 1 | 100 MB | LOB_OBJECT_BUFFER_SIZE (default: 32000) |
| NCLOB | (4 GB - 1) * (database block size) | 128 TB |
The maximum size of Oracle's Extended Data Types (MAX_STRING_SIZE = EXTENDED) can be found in this document under 'SQL Conversion Guide -> Data Type Conversion.
Stored procedures and functions provided for input/output and file control#
Procedures and functions related to input/output and file control are automatically created under the SYSTEM_ user and defined as public synonyms, so users can call them using only the procedure/function names.
| Category | Oracle | Altibase | Note |
|---|---|---|---|
| Standard Output | DBMS_OUTPUT.DISABLE | Not Supported | |
| DBMS_OUTPUT.ENABLE | Not Supported | ||
| DBMS_OUTPUT.GET_LINE | Not Supported | ||
| DBMS_OUTPUT.GET_LINES | Not Supported | ||
| DBMS_OUTPUT.NEW_LINE | DBMS_OUTPUT.NEW_LINE | The DBMS_OUTPUT package in Altibase can be created and used by running the scripts located in $ALTIBASE_HOME/packages | |
| DBMS_OUTPUT.PUT | DBMS_OUTPUT.PUT | Can be replaced with PRINT in Altibase | |
| DBMS_OUTPUT.PUT_LINE | DBMS_OUTPUT.PUT_LINE | Can be replaced with PRINTLN in Altibase | |
| File handling | UTL_FILE.FCLOSE | FCLOSE | |
| UTL_FILE.FCLOSE_ALL | FCLOSE_ALL | ||
| UTL_FILE.FCOPY | FCOPY | ||
| UTL_FILE.FFLUSH | FFLUSH | ||
| UTL_FILE.FGETATTR | Not Supported | ||
| UTL_FILE.FGETPOS | Not Supported | ||
| UTL_FILE.FOPEN | FOPEN | ||
| UTL_FILE.FOPEN_NCHAR | Not Supported | ||
| UTL_FILE.FREMOVE | FREMOVE | ||
| UTL_FILE.FRENAME | FRENAME | ||
| UTL_FILE.FSEEK | Not Supported | ||
| UTL_FILE.GET_LINE | GET_LINE | ||
| UTL_FILE.GET_LINE_NCHAR | Not Supported | ||
| UTL_FILE.GET_RAW | Not Supported | ||
| UTL_FILE.IS_OPEN | IS_OPEN | ||
| UTL_FILE.NEW_LINE | NEW_LINE | ||
| UTL_FILE.PUT | PUT | ||
| UTL_FILE.PUT_LINE | PUT_LINE | ||
| UTL_FILE.PUT_LINE_NCHAR | Not Supported | ||
| UTL_FILE.PUT_NCHAR | Not Supported | ||
| UTL_FILE.PUTF | Not Supported | ||
| UTL_FILE.PUTF_NCHAR | Not Supported | ||
| UTL_FILE.PUT_RAW | Not Supported |
DBMS_RANDOM conversion#
Altibase supports the RANDOM function in addition to the DBMS_RANDOM package.
| Oracle | Altibase | Note |
|---|---|---|
| DBMS_RANDOM.NORMAL | Not supported | Returns a random number from a normal distribution |
| DBMS_RANDOM.SEED | DBMS_RANDOM.SEED | Resets the seed; can be replaced with Altibase’s RANDOM(seed) function |
| DBMS_RANDOM.STRING | DBMS_RANDOM.STRING | Returns a random string |
| DBMS_RANDOM.VALUE | DBMS_RANDOM.VALUE | Returns a random number; can be replaced with Altibase’s RANDOM(0) function |
| DBMS_RANDOM.INITIALIZE | DBMS_RANDOM.INITIALIZE | Procedure to initialize the package; deprecated in Oracle |
| DBMS_RANDOM.RANDOM | DBMS_RANDOM.RANDOM | Generates a random number; deprecated in Oracle |
| DBMS_RANDOM.TERMINATE | Not supported | Procedure to terminate the package; deprecated in Oracle |
WHERE CURRENT OF clause#
The WHERE CURRENT OF clause using Oracle’s CURSOR needs to be converted as follows in Altibase.
| Oracle | Altibase |
|---|---|
CREATE OR REPLACE PROCEDURE proc1 IS CURSOR emp_list IS SELECT empno FROM employee WHERE empno = 1 FOR UPDATE; BEGIN FOR emplst IN emp_list LOOP UPDATE employee SET empjob = 'SALESMAN' WHERE CURRENT OF emp_list; END LOOP; END; / |
CREATE OR REPLACE PROCEDURE proc1 AS BEGIN DECLARE CURSOR cur1 IS SELECT empno FROM employee WHERE empno = 1; v_empjob VARCHAR(10); v_empno INTEGER; BEGIN OPEN cur1; LOOP FETCH cur1 INTO v_empno; EXIT WHEN cur1%NOTFOUND; UPDATE employee SET empjob = 'SALESMAN' WHERE empno = v_empno; //emp_no가 PK이어야 한다. END LOOP; CLOSE cur1; END; END; / |
EXCEPTION#
Oracle and Altibase have predefined exceptions in the system for those that occur in stored PROCEDUREs/FUNCTIONs.
| Oracle | Altibase | |||
|---|---|---|---|---|
| SQLERRM | SQLCODE | SQLERRM | SQLCODE(integer) | SQLCODE(hexadecimal) |
| ACCESS_INTO_NULL | -6530 | Not Supported | ||
| CASE_NOT_FOUND | -6592 | Not Supported | ||
| COLLECTION_IS_NULL | -6531 | Not Supported | ||
| CURSOR_ALREADY_OPEN | -6511 | CURSOR_ALREADY_OPEN | 201062 | 31166 |
| DUP_VAL_ON_INDEX | -1 | DUP_VAL_ON_INDEX | 201063 | 31167 |
| INVALID_CURSOR | -1001 | INVALID_CURSOR | 201064 | 31168 |
| INVALID_NUMBER | -1722 | INVALID_NUMBER | 201065 | 31169 |
| LOGIN_DENIED | -1017 | Not Supported | ||
| NO_DATA_FOUND | +100 | NO_DATA_FOUND | 201066 | 3116A |
| NO_DATA_NEEDED | -6548 | Not Supported | ||
| NOT_LOGGED_ON | -1012 | Not Supported | ||
| PROGRAM_ERROR | -6501 | PROGRAM_ERROR | 201067 | 3116B |
| ROWTYPE_MISMATCH | -6504 | Not Supported | ||
| SELF_IS_NULL | -30625 | Not Supported | ||
| STROAGE_ERROR | -6500 | STORAGE_ERROR | 201068 | 3116C |
| SUBSCRIPT_BEYOND_COUNT | -6533 | Not Supported | ||
| SUBSCRIPT_OUTSIDE_LIMIT | -6532 | Not Supported | ||
| SYS_INVALID_ROWID | -1410 | Not Supported | ||
| TIMEOUT_ON_RESOURCE | -51 | TIMEOUT_ON_RESOURCE | 201069 | 3116D |
| TOO_MANY_ROWS | -1422 | TOO_MANY_ROWS | 201070 | 3116E |
| VALUE_ERROR | -6502 | VALUE_ERROR | 201071 | 3116F |
| ZERO_DIVIDE | -1476 | ZERO_DIVIDE | 201072 | 31170 |
Database Migration Procedure#
To migrate a database from Oracle to Altibase, you can use the MigrationCenter tool, which automatically performs object conversion and data migration.
Before starting the migration, users and tablespaces need to be created in the source database environment to support mapping.
Features dependent on Oracle, non-standard functions, or those not supported by Altibase must be converted manually.

Data Migration and Application Conversion Procedure#

Data Integrity Verification#
| APPLICATION 검증 |
Deriving Verification Plan - Derive the verification plan through prior consultation with the client Example: Online screen capture or report comparison, batch report comparison, etc. |
| DATA검증 |
Migration Center Report - Compare the total number of table records between the source database and Altibase using the migration result report Deriving Verification Plan - Derive the verification plan through prior consultation with the client Examples: Verify total record counts, Compare sum of data in specific columns, Compare results of specific query executions |
Contingency Plan for Database Migration Failures#

Database Migration Tool#
When migrating from Oracle to Altibase, the automatic migration tool MigrationCenter is used.
1) Advantages of MigrationCenter#
- Easy to use with a GUI-based interface
- Supports CLI mode for easy execution of migration tasks and high performance
- Migrates schema and data together in a single operation
- Performs migration through mapping between different data types
- Supports user-defined data types for flexible data migration
- Provides step-by-step summary reports of migration results
2) How to Use#
(1) Add Database Connection#
Enter the information for the Source/Target Database
- Connection Name: Can be set arbitrarily
- IP: Database Server IP
- Port: Database Server Port
- User: Connection user account
- Password: Connection user password
- SID: Service name
- IP Version: IPv4/IPv6

Since JDBC drivers are used to connect to the databases, it is important to prepare the appropriate JDBC drivers for both the source and target databases.
For user convenience, MigrationCenter provides several JDBC drivers suitable for supported databases.
(2) Create Project#
Create a project for data migration.
- Project Name: Can be set arbitrarily
- Project Path: Automatically generated
- Source Database: Oracle
- Destination Database: Altibase

(3) Connect#
Connect to both the source and target databases.

(4) Build Project#
- Build User: Collects information on all migratable objects owned by the user connected to the source database.
- Build Table: Manually constructs a list of tables to migrate from the tables owned by the user connected to the source database. Collects object information for the selected tables and their dependent constraints and indexes.
Choose the method (Fast/Slow) to retrieve the record count of tables from the source database and proceed.

(5) Reconcile Project#
This is the step where the migration execution method is defined, and it is the most important phase in the migration process.

- Data Type Mapping: Mapping data types between Oracle and Altibase
- PSM Data Type Mapping: Mapping data types between Oracle PL/SQL and Altibase PSM
- Tablespace to Tablespace Mapping: Mapping tablespaces between Oracle and Altibase
- Table to Tablespace Mapping: Specifying the tablespace where Altibase tables will be stored
- Select Editing: Adding or modifying data retrieval conditions for Oracle data
- DDL Editing: Editing the final DDL for Altibase

(6) Run Project#
Perform the data migration.

(7) PL/SQL Converter Tool#
Use MigrationCenter to modify the syntax related to data types in Oracle PL/SQL files saved as files.
Since business logic is not automatically converted, developers need to manually convert it.