# Migrate legacy applications from Oracle Pro\$1C to ECPG Migrate legacy applications from Oracle Pro\$1C to ECPG *Sai Parthasaradhi and Mahesh Balumuri, Amazon Web Services* ## Summary Most legacy applications that have embedded SQL code use the Oracle Pro\$1C 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\$1C code to its equivalent in PostgreSQL ECPG.  For more information about Pro\$1C, see the [Oracle documentation](https://docs.oracle.com/cd/E11882_01/appdev.112/e10825/pc_01int.htm#i2415). For a brief introduction to ECPG, see the [Additional information](#migrate-legacy-applications-from-oracle-pro-c-to-ecpg-additional) 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 ](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html)– 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 ### Set the build environment on CentOS or RHEL | Task | Description | Skills 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\$1Loader** 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\$1C 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\$1C to ECPG. | App developer, App owner | ### Convert Pro\$1C code to ECPG | Task | Description | Skills 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\$1C 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\$1C 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\$1C 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\$1C 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\$1C 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\$1C 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\$1CURSOR variables. | Rewrite the `SQL_CURSOR` variable and its implementation.Pro\$1C 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. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-legacy-applications-from-oracle-pro-c-to-ecpg.html) | 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 | ### Compile ECPG programs | Task | Description | Skills 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 | ### Test the application | Task | Description | Skills required | | --- | --- | --- | | Test the code. | Test the converted application code to make sure that it functions correctly. | App developer, App owner, Test engineer | ## Related resources + [ECPG - Embedded SQL in C](https://www.postgresql.org/docs/current/static/ecpg.html) (PostgreSQL documentation) + [Error Handling](https://www.postgresql.org/docs/12/ecpg-errors.html) (PostgreSQL documentation) + [Why Use the Oracle Pro\$1C/C\$1\$1 Precompiler](https://docs.oracle.com/cd/E11882_01/appdev.112/e10825/pc_01int.htm#i2415) (Oracle documentation) ## Additional information PostgreSQL has an embedded SQL precompiler, ECPG, which is equivalent to the Oracle Pro\$1C 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.