Oracle PL/SQL to Vertica

Conversion Features

Ispirer MnMTK has a built-in capability of migrating Oracle packages, procedures and functions to Java classes with generic rules to create scalar functions for Vertica. The tool will create a few separate files for each PL/SQL element.

From one PL/SQL function or procedure two files are created:

• File with function class that carries out the processing you want your UDF to perform.
• File with factory class that provides metadata about the function class, and creates an instance of it to handle function calls.

Oracle procedure migration

Source Oracle procedure:

 
CREATE OR REPLACE PROCEDURE types_migration
AS 
g NUMBER(10,0);
p NUMBER(4,0);
j NUMBER(11,0);
i NUMBER;
BEGIN
j:=10;
END;
 

Java code with User Defined Scalar Functions (UDSFs):

 
     import java.SQL*;
import java.math.*;
import com.vertica.sdk.*;
public class TypesMigrationFactory extends ScalarFunctionFactory  
     {
  @Override 
   public void getPrototype
     (ServerInterface srvInterface, ColumnTypes argTypes, 
     ColumnTypes returnType)
    {
    returnType.addInt();
    }
  @Override
  public ScalarFunction createScalarFunction
  (ServerInterface srvInterface) { 
      RETURN NEW TypesMigration();
    }
  public class TypesMigration extends ScalarFunction {
    @Override
    public void processBlock
       (ServerInterface srvInterface, 
       BlockReader argReader, BlockWriter resWriter) 
       throws UdfException, DestroyInvocation {
  do {         
    Long g = NULL;
    INTEGER p = NULL;
    BigDecimal j = NULL;
    DOUBLE i = NULL;
    j = NEW BigDecimal(10);
    resWriter.setLong(1); 
    }while(argReader.NEXT()); 
    }
  }
}
 

You should compile the TypesMigrationFactory.java into a class file, so that you could include it in your Java UDF JAR library. If you are using a Vertica node as a development system, you can either copy the TypesMigrationFactory.java file to another location on your host, or compile it in place if you have root privileges.

Why Ispirer MnMTK?

Ispirer MnMTK helps to automate the migration process of Oracle PL\SQL to Vertica database. The tool is constantly evolving and can be easily customized for your migration project. With Ispirer MnMTK you eliminate most of the associated risks and considerably reduce internal efforts. All these benefits are available at very reasonable and competitive costs, which makes Ispirer MnMTK even more attractive instrument for this project type.

For more detailed information about our engagement model for application conversion projects, please, go to Application Migration Engagement Model page.

You are also welcome to reach us via Contacts page.