HPL/SQL supports many procedural dialects that allows you to reuse your existing code without changes. But not all statements are executed by HPL/SQL, some of them must be executed by the database.

Consider the following example:

IF code = 'A' THEN
  CREATE TABLE dept 
  (
    deptno NUMBER(2,0),
    dname  NUMBER(14),
    loc    VARCHAR2(13),
    CONSTRAINT pk_dept PRIMARY KEY (deptno)
  );
END IF;

In this example, IF statement is executed by PL/HQL itself. It checks the value of code variable and if it is equal to 'A', HPL/SQL executes the CREATE TABLE statement.

HPL/SQL cannot execute CREATE TABLE, this statement must be sent to the database to create a physical persistent table accessible to other sessions.

But you can see that the syntax of CREATE TABLE does not conform to Hive, it uses NUMBER and VARCHAR2 data types as well as a constraint.

Here is where On-the-fly SQL conversion comes into play. Before sending the CREATE TABLE statement to Hive, HPL/SQL converts it to

CREATE TABLE dept 
(
  deptno DECIMAL(2,0),
  dname  DECIMAL(14),
  loc    STRING
);

On-the-fly conversion is enabled by default and set using hplsql.conn.convert.default option.

Conversions for data types in CREATE TABLE statements:

For more details, see Data Type Conversion.

Conversions for language elements and operators in SQL DDL and DML statements:

Conversion of built-in SQL functions in executable SQL statements:

Conversions of SQL SELECT statements:

For more information, see SELECT Statement.

Conversion of SQL statements: