MIGRATING FROM ORACLE TO SAP HANA

This blog is related to few of my experiences while working on a Project(Migrating from Oracle PL/SQL to HANA SQL Scripts). Some of the basic differences that I found between Oracle PL/SQL and HANA SQL Script are as follows:

1. In HANA nvl() function does not exist. We have IFNULL() function to check if an attribute value is null or not.

For example:

In Oracle: select employee_name from employees where nvl(salary, 0) > 20000;

While in HANA:  select employee_name from  employees where ifnull(salary, 0) > 20000;

The above statements check if the salary is NULL or not, if the salary is null, it sets the salary to 0.

2. Equivalent function for DECODE (In Oracle) is MAP in HANA. It works exactly the way DECODE works.

In Oracle : SELECT DECODE (GENDER, ‘F’, ‘FEMALE’, ‘MALE’) “GENDER”, EMPLOYEE_NAME FROM EMPLOYEES;

In HANA : SELECT MAP (GENDER, ‘F’, ‘FEMALE’, ‘MALE’) “GENDER”, EMPLOYEE_NAME FROM EMPLOYEES;

The above statements return employee name and gender (FEMALE or MALE, based on Values F or M stored in column GENDER).

3. Rounding Issues: In HANA, If a column’s datatype is decimal and its precision exceeds the allowed limit, then HANA doesn’t round it based on the next digit unlike Oracle.

For example:

TABLE NAME: PURCHASE_ORDERS

COLUMNS: PURCHASEORDERID VARCHAR(20),

          ITEM VARCHAR(30),

          LOCATION VARCHAR(30),

          QUANTITY DECIMAL(15, 3)

Suppose I want to insert the following row into PURCHASE_ORDERS table:

INSERT INTO PURCHASE_ORDERS (PURCHASEORDERID, ITEM, LOCATION, QUANTITY) VALUES (‘234212’, ‘Q-123-VE21’, ‘IND’, ‘200.0009’);

And when I check the Quantity of this record after inserting the record in HANA and Oracle, I get the following result:

Oracle:

HANA:

Oracle rounded the Quantity based on the next few digits(rounded 200.0009 to 200.001 as the precision limit is 3), while HANA did not round the Quantity based on next few digits.

4. In Oracle a variable/column having data type as DATE can store timestamp also along with the date, but in HANA the DATE data type can have only date. If you want to have timestamp along with date in HANA, you can use Data type TIMESTAMP.

5. The function CONCAT behaves differently in Oracle and HANA if one or more strings that is to be concatenated is NULL.

     For example:

     In Oracle: select concat(‘a’, null) from dual;

                         The output will be ‘a’.

     In HANA: select concat(‘a’, null) from dummy;

                         The output will be null.

6. Materialized view – Oracle uses materialized view to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment. While in HANA, we don’t have materialized view as materialized views are expensive and are not required in HANA.

7. Regular Expression Functions: Regular expression functions in Oracle like REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_COUNT etc. are not yet implemented in HANA. You can write SQL script procedure/function to implement the same functionality.

8. Rowid Psuedocolumn: The psuedocolun ROWID returns the address of the row in Oracle.

For example:

SELECT ROWID, EMPLOYEE_NAME FROM EMPLOYEES WHERE EMP_ID = 123;

This statement returns the row address in Oracle.

However, HANA doesn’t offer ROWID functionality. Altough there is “$rowid$” in HANA which can provide similar functionality. But it is purely internal and non-documented hence its use should be avoided.

9. Whether it is Oracle or HANA, nesting of select queries should be avoided as it consumes a lot of memory and the performance degrades. Instead, break the query into smaller chunks, put them in variable and use those variables.