Migrate legacy applications from Oracle Pro*C to ECPG

Install the required PostgreSQL packages by using the following commands.

yum update -y
yum install -y yum-utils
rpm -ivh https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql

Install the postgresql12-devel package, which contains header files and libraries, by using the following commands. Install the package in both the development and the runtime environments to avoid errors in the runtime environment.

dnf -y install postgresql12-devel
yum install ncompress zip ghostscript jq unzip wget git -y

For the development environment only, also run the following commands.

yum install zlib-devel make -y
ln -s /usr/pgsql-12/bin/ecpg /usr/bin/

Set the environment path for PostgreSQL client libraries.

export PATH=$PATH:/usr/pgsql-12/bin

If required, install pgLoader as a replacement for SQL*Loader in Oracle.

wget -O /etc/yum.repos.d/pgloader-ccl.repo https://dl.packager.io/srv/opf/pgloader-ccl/master/installer/el/7.repo
yum install pgloader-ccl -y
ln -s /opt/pgloader-ccl/bin/pgloader /usr/bin/

If you’re calling any Java applications from Pro*C modules, install Java.

yum install java -y

Install ant to compile the Java code.

yum install ant -y

Install the AWS CLI to run commands to interact with AWS services such as AWS Secrets Manager and Amazon Simple Storage Service (Amazon S3) from your applications.

cd /tmp/
curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
./aws/install -i /usr/local/aws-cli -b /usr/local/bin --update

Identify the applications that you want to convert from Pro*C to ECPG.

Remove the include headers that are not required in PostgreSQL, such as oci.h, oratypes, and sqlda.

Add EXEC SQL statements for all variable declarations that are used as host variables.

Remove the EXEC SQL VAR declarations such as the following from your application.

EXEC SQL VAR query IS STRING(2048);

The ROWNUM function isn’t available in PostgreSQL. Replace this with the ROW_NUMBER window function in SQL queries.

Pro*C code:

SELECT SUBSTR(RTRIM(FILE_NAME,'.txt'),12) INTO :gcpclFileseq  
FROM   (SELECT FILE_NAME 
FROM  DEMO_FILES_TABLE 
WHERE FILE_NAME    LIKE '%POC%' 
ORDER BY FILE_NAME DESC) FL2 
WHERE ROWNUM <=1 ORDER BY ROWNUM;

ECPG code:

SELECT SUBSTR(RTRIM(FILE_NAME,'.txt'),12) INTO :gcpclFileseq  
FROM   (SELECT FILE_NAME , ROW_NUMBER() OVER (ORDER BY FILE_NAME DESC) AS ROWNUM
FROM  demo_schema.DEMO_FILES_TABLE 
WHERE FILE_NAME    LIKE '%POC%'
ORDER BY FILE_NAME DESC) FL2 
WHERE ROWNUM <=1 ORDER BY ROWNUM; 

In PostgreSQL, function parameters can’t be used as host variables. Overwrite them by using an alias variable.

Pro*C code:

int processData(int referenceId){
  EXEC SQL char col_val[100];
  EXEC SQL select column_name INTO :col_val from table_name where col=:referenceId;
}

ECPG code:

int processData(int referenceIdParam){
  EXEC SQL int referenceId = referenceIdParam;
  EXEC SQL char col_val[100];
  EXEC SQL select column_name INTO :col_val from table_name where col=:referenceId;
}

Define struct types in EXEC SQL BEGIN and END blocks with typedef if the struct type variables are used as host variables. If the struct types are defined in header (.h) files, include the files with EXEC SQL include statements.

Pro*C code:

Header file (demo.h)

struct s_partition_ranges
{
 char   sc_table_group[31];
 char   sc_table_name[31];
 char   sc_range_value[10];
}; 
struct s_partition_ranges_ind
{
  short    ss_table_group;
  short    ss_table_name;
  short    ss_range_value;
}; 

ECPG code:

Header file (demo.h)

EXEC SQL BEGIN DECLARE SECTION;
typedef struct 
{
  char   sc_table_group[31];
  char   sc_table_name[31];
  char   sc_range_value[10];
} s_partition_ranges; 
typedef struct 
{
  short    ss_table_group;
  short    ss_table_name;
  short    ss_range_value;
} s_partition_ranges_ind; 
EXEC SQL END DECLARE SECTION;

Pro*C file (demo.pc)

#include "demo.h"
struct s_partition_ranges gc_partition_data[MAX_PART_TABLE] ;
struct s_partition_ranges_ind gc_partition_data_ind[MAX_PART_TABLE] ;

ECPG file (demo.pc)

exec sql include "demo.h"
EXEC SQL BEGIN DECLARE SECTION;
s_partition_ranges gc_partition_data[MAX_PART_TABLE] ;
s_partition_ranges_ind gc_partition_data_ind[MAX_PART_TABLE] ;
EXEC SQL END DECLARE SECTION;

To fetch multiple rows from cursors by using array variables, change the code to use FETCH FORWARD.

Pro*C code:

EXEC SQL char  aPoeFiles[MAX_FILES][FILENAME_LENGTH];
EXEC SQL FETCH filename_cursor into :aPoeFiles;

ECPG code:

EXEC SQL char  aPoeFiles[MAX_FILES][FILENAME_LENGTH];
EXEC SQL int fetchSize = MAX_FILES;
EXEC SQL FETCH FORWARD :fetchSize filename_cursor into :aPoeFiles;

Oracle package functions that don’t have return values should be called with an indicator variable. If your application includes multiple functions that have the same name or if the unknown type functions generate runtime errors, typecast the values to the data types.

Pro*C code:

void ProcessData (char *data , int id)
{        
        EXEC SQL EXECUTE
               BEGIN
                  pkg_demo.process_data (:data, :id);                                                                                    
               END;
       END-EXEC;
}

ECPG code:

void ProcessData (char *dataParam, int idParam )
{
        EXEC SQL char *data = dataParam;
        EXEC SQL int id = idParam;
        EXEC SQL short rowInd;
        EXEC SQL short rowInd = 0;
        EXEC SQL SELECT pkg_demo.process_data (
                       inp_data => :data::text,
                       inp_id => :id
               ) INTO :rowInd;
}

Rewrite the SQL_CURSOR variable and its implementation.

Pro*C code:

/* SQL Cursor */
SQL_CURSOR      demo_cursor;
EXEC SQL ALLOCATE :demo_cursor;
EXEC SQL EXECUTE
  BEGIN
      pkg_demo.get_cursor(     
        demo_cur=>:demo_cursor
      );
  END;
END-EXEC;

ECPG code:

EXEC SQL DECLARE demo_cursor CURSOR FOR SELECT
         * from
    pkg_demo.open_filename_rc(
            demo_cur=>refcursor
          ) ;
EXEC SQL char open_filename_rcInd[100]; 
# As the below function returns cursor_name as 
# return we need to use char[] type as indicator. 
EXEC SQL SELECT pkg_demo.get_cursor (
        demo_cur=>'demo_cursor'
    ) INTO :open_filename_rcInd;

To run the ECPG program in debug mode, add the following command inside the main function block.

ECPGdebug(1, stderr); 

If you have an embedded SQL C source file named prog1.pgc, you can create an executable program by using the following sequence of commands.

ecpg prog1.pgc
cc -I/usr/local/pgsql/include -c prog1.c
cc -o prog1 prog1.o -L/usr/local/pgsql/lib -lecpg

Create a make file to compile the ECPG program, as shown in the following sample file.

CFLAGS ::= $(CFLAGS) -I/usr/pgsql-12/include -g -Wall
LDFLAGS ::= $(LDFLAGS) -L/usr/pgsql-12/lib -Wl,-rpath,/usr/pgsql-12/lib
LDLIBS ::= $(LDLIBS) -lecpg
PROGRAMS = test 
.PHONY: all clean
%.c: %.pgc
      ecpg $<
all: $(PROGRAMS)
clean:
    rm -f $(PROGRAMS) $(PROGRAMS:%=%.c) $(PROGRAMS:%=%.o)

Test the converted application code to make sure that it functions correctly.