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:

Oracle.PNG

HANA:

HANA.PNG

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.

To report this post you need to login first.

20 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann

    I think the idea to compare the two platforms under the perspective of a migration is pretty good and some of the points are surely a good hint for developers.

    However, there are some points that are not as good in this blog post and maybe you correct them:

    • Formatting: please, there are heading, bullet list, numbered list, etc. styles available that allow you to structure the text. Please make use of them! It doesn’t only look much better but also helps the reader to navigate the text.
    • Your comment no. 3 “Rounding issues” is absolutely wrong. If SAP HANA would simply cut off the values, it would produce wrong results.
      The reason why you don’t see the decimal digits in your result set is that by default SAP HANA studio has the option “Format Values” ticked ON for result sets.
      This setting then makes sure that the values in the result set display are rendered according to the regional settings of the JAVA runtime your SAP HANA studio is running in. Typically that would be the regional setting of your OS-session.
      You can disable the formatting to see the “raw” values in the SAP HANA preferences.
      Remember: whenever you actually see a result set of any database (same with Oracle) you see a rendered version of it.
    • ROWID is a big NO NO for developers in Oracle.
      If you use it – that’s a bug (unless you’re Tom Kyte or Jonathan Lewis, that is 🙂 )
      The statement you made about the “$rowid$” column (“it should not be used”) is equally true for Oracle’s ROWID column.
    • The statement about nesting of sub-queries is to general to even have a validity. Not sure if you ever heard about query transformation, but both Oracle and SAP HANA use that a lot and not all sub-queries are handled the same way.
      Statements like this definitively require a lot more factual backing!

    I guess, what I’m saying is: keep going with the blog post writing and up your game.

    – Lars

    (0) 
    1. Stefan Koehler

      Hey Lars,

      >> ROWID is a big NO NO for developers in Oracle. If you use it – that’s a bug (unless you’re Tom Kyte or Jonathan Lewis)


      In general i would agree with you on this, but there are application cases that benefit from using ROWID (and it is legal to do it so). Good examples are applications that are used to manually maintain data – SQL*Developer is such a tool which uses ROWID for updating some data set for example. However every mask based single data set update application may benefit and use the ROWID approach for maintaining data.

      Not the common use case for the “big data hyper super OLAP fast” HANA appliance, but pretty common in OLTP 😛

      Regards

      Stefan

      (0) 
      1. Lars Breddemann

        hmm… I cannot agree to this, really.

        SQL*Developer, TOAD and the like do use ROWID to access rows without having to know the table key. But these are rather special developer tools and they usually don’t do the OLTP processing.

        Also, the ROWID actually can change for a given record – you won’t build your application logic on that.

        Even good old Tom makes it very clear (Ask Tom "Is it safe to use ROWID to locate a row?") :

        It is perfectly safe to use the rowid in ALL CASES however, assuming you combine it with the primary key as well

        In other words: locating the record with rowid – fine. Identifying it by rowid – not such a good idea.

        – Lars

        (0) 
        1. Stefan Koehler

          Hi Lars,

          great, i need to convince you – Let me try 😉

          >> SQL*Developer, TOAD and the like do use ROWID to access rows without having to know the table key. But these are rather special developer tools and they usually don’t do the OLTP processing.


          I guess you misunderstood my point. The usual (OLTP) single data set mask processing is the following:

          1. User selects data in application mask at first (e.g. based on some primary / table key or whatever)
          2. User locks data (in edit) mode and modifies specific data set
          3. User updates data set (and commits)


          In step 1 (or step 2 depend on your application) you usually select the ROWID as well and then use it to proceed with step 3 in the most efficient way later on. That’s also the way how SQL*Developer (Single Record View) does it, but the dialog box can be from any other application as well.

          In consequence the SQL (execution) flow could be something like this:

          1. SELECT ROWID,”EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,”COMM”,”DEPTNO” FROM “SCOTT”.”EMP” WHERE “EMPNO” = 7788 FOR UPDATE
          2. UPDATE “SCOTT”.”EMP” SET COMM=:sqldevvalue WHERE ROWID = :sqldevgridrowid

          Totally legal to do so and in the most efficient way as the PK (or any other WHERE clause) is not used / accessed twice 🙂

          Regards

          Stefan

          P.S.: This “logic” is also described by Christian Antognini in his book “Troubleshooting Oracle Performance, 2nd Edition” on page 482.

          (0) 
          1. Lars Breddemann

            Alright Stefan, it’s not always a bug to do that.

            Just most of the times 😉 .

            Your workflow is very specifically designed to make the use of ROWID safe – that’s not to be assumed in general and honestly the vast majority of occasions I have seen ROWID in productive code it was a bug (e.g. trying to find the “newest” record and the like).

            Anyhow, good to see you’re still out and about and holding up the flag for Oracle know-how here on SCN!

            (0) 
    2. Prachi Tiwari Post author

      Hi Lars,

      Thanks for your suggestion. I will surely work on it.

      I might be wrong, but I tried to turn “Format Values” option off and then created a table and inserted rows in it –

      CREATE TABLE INVENTORY (NAME NVARCHAR(50), QTY DECIMAL(15,3), UNIT NVARCHAR(5));

        

      INSERT INTO INVENTORY values (‘RICE’, 891.0008, ‘KG’);

      INSERT INTO INVENTORY values (‘WHEAT’, 713.009, ‘KG’);

      When I see the result of it, HANA gives me the following result –

      Inv_HANA.PNG

      While Oracle gives me this –

      Inv_Oracle.PNG

      Please let me know if I am wrong somewhere.

      Regards,
      Prachi

      (0) 
      1. Lars Breddemann

        Hi Prachi,

        you’re right and I’ve got to apologize. 😯

        Indeed this was not a display error, but (like other DBMS as well) SAP HANA doesn’t perform automatic rounding when doing a type conversion.

        Compared with Oracle the implicit conversion performs a TRUNC() while Oracle does a ROUND() .

        It’s still possible, of course, to manually ensure that the values are treated the same way:

        by using the ROUND() function during the insert.

        BTW: the SQL standard left the decision whether to round or to truncate during implicit conversions explicitly to the DBMS platform. Which means both ways of doing it are actually correct. 

        Cheers, Lars

        (0) 
  2. Ashok Bhakare

    Thanks Prachi for putting this in one place. I am searching for Decode function in HANA and i got it 🙂  it helped me to run Hana procedure.

    (0) 
  3. Stephen Du

    we are doing sql syntax translators from oracle plsql to hana sqlscript, other translation type can also be supported, if anyone have this needs, please consult me or my team, hana e2e.

    (0) 
    1. Sudarshan Survepalli

      Hi Stephen,

      Please can you clarify, is SAP building a tool that does auto-translation of ORACLE PLSQL to HANA SQLScript?

      Which version is this going to be available from? Will there be any license charges involved?

      Thanks

      Sudarshan

      (0) 
      1. Stephen Du

        Hello Sudarshan,

        Yes, there is some plan to do this, but not officially a project yet. This will be based on newest version of Oracle and HANA, charge issue is not in consideration yet.

        thanks,

        Stephen Du

        (0) 
        1. Sudarshan Survepalli

          OK Thanks Stephen. This will be extremely useful for a lot of customers. Am directly involved in a few such projects where we start with a legacy Oracle Script and then re-build it for HANA! Wish the tool is available soon. Look forward to hearing more.  Regards, Sudarshan

          (0) 

Leave a Reply