Category: Uncategorized

Debugging stored procedures in Amazon RDS for PostgreSQL might pose a challenge if you are accustomed to using a GUI tool. The absence of support for the pldbgapi extension in Amazon RDS for PostgreSQL complicates this process. In this blog post, I suggest a workaround based on deploying an additional PostgreSQL on an Amazon Windows EC2 instance as a debugging platform.

Assuming that you used AWS Schema Conversion Tool (SCT) with the Oracle extension pack, the code converted from Oracle to PostgreSQL will contain several calls to objects in schemas named aws_operators, aws_oracle_context, aws_oracle_data, and aws_oracle_ext. These modules emulate functions present in Oracle that are required for the code to execute in PostgreSQL. However, after the conversion by AWS SCT, you may still need to debug a stored procedure to resolve an issue.

The first step is to get the DDL for the objects created as part of the extension pack. The process is as follows:

– Start the AWS SCT tool,
– connect to the target database,
– right click on the target schema in the right-hand box,
– click on the “Apply Extension Pack for” Oracle in this case

 

 

 

 

 

In the “Apply extension pack for Oracle” dialogue box, progress to “Step 9: Function emulation” and click on the “Save scripts to” button on the bottom right.

Provide the name of a folder (preferably a new empty folder) as the target for the SQL files. The DDL files will be created as shown below

As of January 2024, this process will create 1506 files. This is subject to change as AWS releases newer versions of SCT.

The next step is to deploy an Amazon EC2 Windows instance. After the instance has been created, connect to the instance and download the PostgreSQL software from EnterpriseDB (EDB). I chose the EDB distribution as it includes several extensions preinstalled including pldbgapi.

The PostgreSQLTutorial website provides the steps to be followed to install PostgreSQL. After PostgreSQL has been installed, create a new database for this test. In this new database, create the pldbgapi extension with the SQL command

CREATE EXTENSION IF NOT EXISTS pldbgapi;

Confirm that the extension has been created. I am using PGAdmin to connect to my Amazon RDS PostgreSQL instance

 

 

 

 

 

 

After the extension has been created, copy the DDL files created from SCT to a location on the Windows server

(There are 1,509 files shown above as I created three extra files. Please ignore them.)

After some testing, I identified that the best way to deploy them was via Windows batch file with commands as shown below

@echo off
setlocal
set PGPASSWORD=password-goes-here
"C:\Program Files\PostgreSQL\15\bin\psql.exe" -h localhost -U postgres -d dctestdb -f "C:/dean/ssct-oracle-extension-pack-ddl/0001-aws_lambda.sql" -L "C:/dean/ssct-oracle-extension-pack-ddl/sct-ddl.log"
"C:\Program Files\PostgreSQL\15\bin\psql.exe" -h localhost -U postgres -d dctestdb -f "C:/dean/ssct-oracle-extension-pack-ddl/0002-dblink_transaction.sql" -L "C:/dean/ssct-oracle-extension-pack-ddl/sct-ddl.log"
.
.
.
All other files here
.
.
.

The output of the DDL operations will be in the log file specified in the above batch file

 

 

 

 

 

 

 

 

 

 

 

After the DDL files have been executed, the database has the aws_operators, aws_oracle_context, aws_oracle_data, and aws_oracle_ext schemas defined

 

 

 

 

 

 

 

 

 

At this point, you can extract the DDL for a stored procedure (and related objects) from Amazon RDS and create it in the Windows EC2 PostgreSQL database and you will be able to debug the stored procedure via the GUI.

Maintaining IT certifications can be a time-consuming commitment, often involving retesting and juggling busy schedules. This time I decided to try CompTIA’s CertMaster Continuing Education (CE) for Security+ to extend my certification.

This self-paced e-learning program proved to be an efficient and engaging avenue to refresh and expand my existing knowledge base. Instead of cramming for a one-off test, I had the flexibility to delve deeper into key security concepts, revisiting essential skills and acquiring new ones. While working at my own pace, I was able to maintain my certification while enhancing my skill set and staying ahead of the curve in the ever-changing world of cybersecurity.

Feels good to have received my CompTIA Security+ certification.

If you are attempting to compile Pro*C code and get the error “PCC-F-02104, Unable to connect to Oracle”, perform the following steps:

Instead of connecting to the Oracle database as

sqlplus userid/password@somelistener.domain.com:1521/MYPRODDB

and then attempting to compile, edit your tnsnames.ora to add the below entry (change appropriately to reflect your database SID or service, listener, port etc.)

MY_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SOMELISTENER.DOMAIN.COM)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = MYPRODDB)
    )
  )

Export this entry as follows

export TWO_TASK=MY_DB

And now connect as

sqlplus userid/password

i.e. without specifying the usual connect string.

In case you are wondering about the name “TWO_TASK”, Tom Kyte explains it best:
“because in days gone by – the 1980’s – two tasks (having two tasks working together) just made ‘sense’”.

Very excited to have passed the AWS Certified DevOps Engineer – Professional exam.