Migrate legacy applications from Oracle Pro*C to ECPG - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsToolsEpicsRelated resourcesAdditional information

Migrate legacy applications from Oracle Pro*C to ECPG

Created by Sai Parthasaradhi (AWS) and Mahesh Balumuri (AWS)

Environment: PoC or pilot

Source: Oracle

Target: PostgreSQL

R Type: Re-architect

Workload: Oracle

Technologies: Migration; Databases

Summary

Most legacy applications that have embedded SQL code use the Oracle Pro*C precompiler to access the database. When you migrate these Oracle databases to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition, you have to convert your application code to a format that’s compatible with the precompiler in PostgreSQL, which is called ECPG. This pattern describes how to convert Oracle Pro*C code to its equivalent in PostgreSQL ECPG. 

For more information about Pro*C, see the Oracle documentation. For a brief introduction to ECPG, see the Additional information section.

Prerequisites and limitations

Prerequisites 

  • An active AWS account

  • An Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible database

  • An Oracle database running on premises

Tools

  • The PostgreSQL packages listed in the next section.

  • AWS CLI – The AWS Command Line Interface (AWS CLI) is an open-source tool for interacting with AWS services through commands in your command-line shell. With minimal configuration, you can run AWS CLI commands that implement functionality equivalent to that provided by the browser-based AWS Management Console from a command prompt.

Epics

TaskDescriptionSkills required
Install PostgreSQL packages.

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
App developer, DevOps engineer
Install the header files and libraries.

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/
App developer, DevOps engineer
Configure the environment path variable.

Set the environment path for PostgreSQL client libraries.

export PATH=$PATH:/usr/pgsql-12/bin
App developer, DevOps engineer
Install additional software as necessary.

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
App developer, DevOps engineer
Install the AWS CLI.

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
App developer, DevOps engineer
Identify the programs to be converted.

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

App developer, App owner
TaskDescriptionSkills required
Remove unwanted headers.

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

App owner, App developer
Update variable declarations.

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);
App developer, App owner
Update ROWNUM functionality.

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;
App developer, App owner
Update function parameters to use alias variables.

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;
}
App developer, App owner
Update struct types.

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;
App developer, App owner
Modify logic to fetch from cursors.

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;
App developer, App owner
Modify package calls that don't have return values.

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;
}
App developer, App owner
Rewrite SQL_CURSOR variables.

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;
App developer, App owner
Apply common migration patterns.

  • Change SQL queries so they’re compatible with PostgreSQL.

  • Move anonymous blocks, when they aren’t supported in ECPG, to the database.

  • Remove dbms_application_info logic, which isn’t supported by PostgreSQL.

  • Move EXEC SQL COMMIT statements after the cursor close. If you commit queries while in the loop to fetch the records from the cursor, the cursor is closed and a cursor doesn’t exist error is displayed.

  • For information about handling exceptions in ECPG and error codes, see Error Handling in the PostgreSQL documentation.

App developer, App owner
Enable debugging, if required.

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

ECPGdebug(1, stderr);
App developer, App owner
TaskDescriptionSkills required
Create an executable file for ECPG.

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
App developer, App owner
Create a make file for compilation.

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)
App developer, App owner
TaskDescriptionSkills required
Test the code.

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

App developer, App owner, Test engineer

Related resources

Additional information

PostgreSQL has an embedded SQL precompiler, ECPG, which is equivalent to the Oracle Pro*C precompiler. ECPG converts C programs that have embedded SQL statements to standard C code by replacing the SQL calls with special function calls. The output files can then be processed with any C compiler tool chain.

Input and output files

ECPG converts each input file you specify on the command line to the corresponding C output file. If an input file name doesn’t have a file extension, .pgc is assumed. The file's extension is replaced by .c to construct the output file name. However, you can override the default output file name by using the -o option.

If you use a dash (-) as the input file name, ECPG reads the program from standard input and writes to standard output, unless you override that by using the -o option.

Header files

When the PostgreSQL compiler compiles the pre-processed C code files, it looks for the ECPG header files in the PostgreSQL include directory. Therefore, you might have to use the -I option to point the compiler to the correct directory (for example, -I/usr/local/pgsql/include).

Libraries

Programs that use C code with embedded SQL have to be linked against the libecpg library. For example, you can use the linker options -L/usr/local/pgsql/lib -lecpg.

Converted ECPG applications call functions in the libpq library through the embedded SQL library (ecpglib), and communicate with the PostgreSQL server by using the standard frontend/backend protocol.