Many organizations are often seeking to migrate from their commercial relational databases, such as Oracle, to open-source databases like PostgreSQL. When migrating from Oracle to PostgreSQL databases, the most time-consuming and complex part is converting database structures, code, and storage objects such as packages, procedures, functions, and triggers.
Converting or modernizing from Oracle to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition can become more complex and challenging when the code conversion equivalent native functions aren’t available in PostgreSQL.
For example, Oracle database provides LOB (large objects) data types such as BLOB and CLOB. To operate on these data types, Oracle provides the native DBMS_LOB package, which provides subprograms that can be used to access and interact with specific parts of LOBs or full LOBs.
Oracle databases have specific data types such as varrays (variable-size arrays), which store the number of elements as a single object, and associative arrays (formerly known as PL/SQL tables or index-by tables), which are sets of key-value pairs that are complex to convert because PostgreSQL doesn’t have a native package or function similar to Oracle DBMS_LOB to interact with LOBs and data types similar to Oracle collections such as varrays and associative arrays.
You can use the AWS Schema Conversion Tool (AWS SCT) and AWS SCT extension packs to manually convert complex Oracle code objects to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL.
In this post, we explain how you can convert Oracle code objects that contain DBMS_LOB procedures and Oracle collections such as varrays and associative arrays from Oracle to the Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL equivalent by using AWS SCT, the AWS SCT extension pack, and manual code conversion.
AWS SCT converts your existing database schema from one database engine to another. It can convert relational OLTP schemas or data warehouse schemas. If the schema from your source database can’t be converted automatically, AWS SCT provides guidance on how you can create an equivalent schema in your target RDS database. AWS SCT has additional features for integration with AWS SCT extension packs. The AWS SCT extension pack is an add-on module that emulates functions present in a source database that are required when converting objects to the target database. When you install and apply the AWS SCT extension pack, it creates an aws_oracle_ext schema on the target database. AWS SCT automatically applies the extension pack when installed on the target database. It also shows the code where the extensions are applied to the target database code.
Solution overview
In this solution, we have taken a sample Oracle code that contains the DBMS_LOB package, which writes and appends to LOB object. It also contains data types such as varrays and associative arrays to store the values of variables.
We explain the steps to convert this sample Oracle code to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL, which helps you simulate and convert such complex codes when you encounter them in your environment.
The following diagram provides an overview of the solution.
The workflow consists of the following high-level steps:
- Use a source Oracle database that contains sample procedures with DBMS_LOB and Oracle collections.
- Run AWS SCT to convert the Oracle code, which partially converts the code with errors.
- Perform a manual code conversion using the AWS SCT extension pack with the aws_oracle_ext schema.
- Apply and deploy the completed converted code to the target RDS for PostgreSQL or Amazon Aurora PostgreSQL database.
Prerequisites
Complete the following prerequisite steps:
- Create a source Oracle database if you don’t already have one.
- Create an Amazon Aurora PostgreSQL cluster or an RDS for PostgreSQL instance if you don’t already have one. For instructions, refer to Create an Aurora PostgreSQL DB cluster or Create a PostgreSQL DB instance, respectively.
- Make sure database client software are installed to connect to the source and target databases.
- Create the demo_user schema in the source and target databases.
Create database objects in the source Oracle database
Complete the following steps to set up your source table:
- Create an empty table:
CREATE TABLE DEMO_USER.PRODUCT_TBL
(ID NUMBER(8,0),
PRDNM VARCHAR2(25 BYTE),
STOCK CLOB,
Details CLOB ) ;
- Create the procedure
update_product_details, which inserts the product ID, product name, delivery period, and detailed text of three products into the demo_user.product_tbl table:
create or replace procedure demo_user.update_product_details
as
type t_prd_names is varray(3) of varchar2(20) not null;
t_prd t_prd_names := t_prd_names('Mobile','Tablet','Laptop');
TYPE del_sch IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(10);
v_del_sch del_sch;
DET_CLOB CLOB;
DET_TXT CLOB ;
v_amount number(8);
v_offset number(2);
begin
v_del_sch('Laptop') := 'Ready for Delivery in 3 days';
v_del_sch('Tablet') := 'Ready for Delivery in 7 days';
v_del_sch('Mobile') := 'Ready for Delivery in 5 days';
v_amount := 6;
v_offset := 1;
for i in 1..3
loop
DBMS_LOB.CREATETEMPORARY(DET_CLOB, TRUE);
DBMS_LOB.OPEN(DET_CLOB, DBMS_LOB.LOB_READWRITE);
if t_prd(i) = 'Mobile'
then
DBMS_LOB.WRITE(DET_CLOB,v_amount, v_offset,t_prd(i));
DET_TXT := ' is a portable telephone that can make and receive calls over a radio frequency link while the user
is moving within a telephone service area, as opposed to a fixed-location phone (landline phone).';
DBMS_LOB.WRITEAPPEND(DET_CLOB, LENGTH(DET_TXT), DET_TXT);
insert into demo_user.product_tbl values (i,t_prd(i),v_del_sch(t_prd(i)),DET_CLOB);
end if;
if t_prd(i) = 'Tablet'
then
DBMS_LOB.WRITE(DET_CLOB,v_amount, v_offset,t_prd(i));
DET_TXT := ' is a mobile device, typically with a mobile operating system and touchscreen display processing circuitry, and a rechargeable battery in a single, thin and flat package.';
DBMS_LOB.WRITEAPPEND(DET_CLOB, LENGTH(DET_TXT), DET_TXT);
insert into demo_user.product_tbl values (i,t_prd(i),v_del_sch(t_prd(i)),DET_CLOB);
end if;
if t_prd(i) = 'Laptop'
then
DBMS_LOB.WRITE(DET_CLOB,v_amount, v_offset,t_prd(i));
DET_TXT := ' is known either as a laptop or notebook for short, is a small, portable personal computer(PC).';
DBMS_LOB.WRITEAPPEND(DET_CLOB, LENGTH(DET_TXT), DET_TXT);
insert into demo_user.product_tbl values (i,t_prd(i),v_del_sch(t_prd(i)),DET_CLOB);
end if;
end loop; end;
This procedure loops through each product name as listed in the varrays, reads the delivery period details for that product as listed in the associate array, uses DBMS_LOB subroutines to formulate the detailed text of the product, and finally inserts it as records into the demo_user.product_tbl table.
In this procedure, the varray is to list the product names in the variable t_prd_names('Mobile','Tablet','Laptop') and the associative array is to set the delivery time period for the specific product, such as the following:
v_del_sch('Laptop') := 'Ready for Delivery in 3 days';
v_del_sch('Tablet') := 'Ready for Delivery in 7 days';
v_del_sch('Mobile') := 'Ready for Delivery in 5 days';
The dbms_lob.write subroutine writes the product name to the buffer as the first word to the DET_CLOB buffer, starting from the v_offset value (which is 1) and writing up to the max bytes as specified in the v_amount variable:
DBMS_LOB.WRITE(DET_CLOB,v_amount, v_offset,t_prd(i));
The dbms_lob.writeappend subroutine appends the text for each product name as the value specified in the DET_TXT variable and formulates the detailed text of the product:
DBMS_LOB.WRITEAPPEND(DET_CLOB, LENGTH(DET_TXT), DET_TXT);
Inserts in the demo_user.product_tbl table consist of the product ID, name, delivery period, and detailed text of the product:
insert into demo_user.product_tbl values (i,t_prd(i),v_del_sch(t_prd(i)),DET_CLOB);
- Run the procedure and query the table:
SQL> select * from demo_user.product_tbl;
no rows returned.
SQL> call demo_user.update_product_details();
PL/SQL procedure completed successfully
SQL> select * from demo_user.product_tbl;
Configure AWS SCT
After you complete the preceding steps in the Oracle source database, the next step is to install the AWS SCT on your local or centralized server. After you install the AWS SCT, download the Oracle and PostgreSQL database drivers and update AWS SCT to connect to the source and target databases.
After a successful connection to both databases, create mapping rules in AWS SCT and apply the aws_oracle_ext extension pack to the target PostgreSQL database. For converting the schema, the AWS SCT tool displays the demo_user schema on the source database in the left pane. Choose (right-click) the schema demo_user on the source and choose Convert schema. AWS SCT will generate the equivalent code in PostgreSQL. You can review the converted procedure and apply the changes to the target database.
During conversion, AWS SCT creates a product_tbl table, an update_product_details$del_sch$c type, and an update_product_details() procedure in the demo_user schema in the target RDS for PostgreSQL or Amazon Aurora PostgreSQL database.
AWS SCT creates the following DDL for the PostgreSQL table and type:
CREATE TABLE demo_user.product_tbl (
id numeric(8) NULL,
prdnm varchar(25) NULL,
stock text NULL,
details text NULL
);
CREATE TYPE demo_user."update_product_details$del_sch$c" AS (column_value varchar(64));
Then it creates a converted package as shown in the following code. Observe the comments that AWS SCT provides.
CREATE OR REPLACE PROCEDURE demo_user.update_product_details()
LANGUAGE plpgsql
AS $procedure$
DECLARE
aws_oracle_ext$array_id$temporary BIGINT;
t_prd demo_user.update_product_details$t_prd_names
/*
[5340 - Severity CRITICAL - PostgreSQL doesn't support the UPDATE_PRODUCT_DETAILS.T_PRD_NAMES function. Revise your code to use another function or create a user-defined function.]
:= t_prd_names('Mobile','Tablet','Laptop')
*/;
v_del_sch VARCHAR(100) := 'v_del_sch';
DET_CLOB TEXT;
DET_TXT TEXT;
v_amount NUMERIC(8);
v_offset NUMERIC(2);
BEGIN
aws_oracle_ext$array_id$temporary := aws_oracle_ext.array$create_array(p_array_name => 'v_del_sch', p_procedure_name => 'demo_user.update_product_details', pIndexBy => 'VARCHAR2(10)');
PERFORM aws_oracle_ext.array$add_fields_to_array(p_array_id => aws_oracle_ext$array_id$temporary, p_fields => '[{"":"CHARACTER VARYING(64)"}]');
PERFORM aws_oracle_ext.array$create_storage_table(p_array_name => 'v_del_sch', p_procedure_name => 'demo_user.update_product_details', p_cast_type_name => 'demo_user.update_product_details$del_sch$c', pWithData => FALSE);
PERFORM aws_oracle_ext.array$set_value(p_array_path => 'v_del_sch[''Laptop'']', p_procedure_name => 'demo_user.update_product_details', p_value => 'Ready for Delivery in 3 days'::CHARACTER VARYING(64));
PERFORM aws_oracle_ext.array$set_value(p_array_path => 'v_del_sch[''Tablet'']', p_procedure_name => 'demo_user.update_product_details', p_value => 'Ready for Delivery in 7 days'::CHARACTER VARYING(64));
PERFORM aws_oracle_ext.array$set_value(p_array_path => 'v_del_sch[''Mobile'']', p_procedure_name => 'demo_user.update_product_details', p_value => 'Ready for Delivery in 5 days'::CHARACTER VARYING(64));
v_amount := 6;
v_offset := 1;
FOR i IN 1..3 LOOP
CALL aws_oracle_ext.dbms_lob$createtemporary(lob_loc => DET_CLOB);
/*
[5340 - Severity CRITICAL - PostgreSQL doesn't support the SYS.DBMS_LOB.OPEN(CLOB,BINARY_INTEGER) function. Revise your code to use another function or create a user-defined function.]
DBMS_LOB.OPEN(DET_CLOB, DBMS_LOB.LOB_READWRITE)
*/
IF t_prd(i) = 'Mobile' THEN
/*
[9996 - Severity CRITICAL - Transformer error occurred in plSqlStatement. Please submit report to developers.]
DBMS_LOB.WRITE(DET_CLOB,v_amount, v_offset,t_prd(i))
*/
DET_TXT := ' is a portable telephone that can make and receive calls over a radio frequency link while the user
is moving within a telephone service area, as opposed to a fixed-location phone (landline phone).'::TEXT;
CALL aws_oracle_ext.dbms_lob$writeappend(lob_loc => DET_CLOB, amount => LENGTH(DET_TXT::TEXT), buffer => DET_TXT);
/*
[5030 - Severity CRITICAL - AWS SCT can't convert the usage of objects with the unsupported Oracle - database-1.xxxxxxxxxx.us-east-1.rds.amazonaws.com.DEMO_USER.UPDATE_PRODUCT_DETAILS.T_PRD_NAMES data type. AWS SCT created a method stub. To replace it, convert your source code manually.]
insert into demo_user.product_tbl values (i,t_prd(i),v_del_sch(t_prd(i)),DET_CLOB)
*/
END IF;
/*
[5030 - Severity CRITICAL - AWS SCT can't convert the usage of objects with the unsupported Oracle - database-1.xxxxxxxx.us-east-1.rds.amazonaws.com.DEMO_USER.UPDATE_PRODUCT_DETAILS.T_PRD_NAMES data type. AWS SCT created a method stub. To replace it, convert your source code manually.]
if t_prd(i) = 'Tablet'
then
DBMS_LOB.WRITE(DET_CLOB,v_amount, v_offset,t_prd(i));
DET_TXT := ' is a mobile device, typically with a mobile operating system and touchscreen display processing circuitry, and a rechargeable battery in a single, thin and flat package.';
DBMS_LOB.WRITEAPPEND(DET_CLOB, LENGTH(DET_TXT), DET_TXT);
insert into demo_user.product_tbl values (i,t_prd(i),v_del_sch(t_prd(i)),DET_CLOB);
end if
*/
/*
[5030 - Severity CRITICAL - AWS SCT can't convert the usage of objects with the unsupported Oracle - database-1.xxxxxxxxxx.us-east-1.rds.amazonaws.com.DEMO_USER.UPDATE_PRODUCT_DETAILS.T_PRD_NAMES data type. AWS SCT created a method stub. To replace it, convert your source code manually.]
if t_prd(i) = 'Laptop'
then
DBMS_LOB.WRITE(DET_CLOB,v_amount, v_offset,t_prd(i));
DET_TXT := ' is known either as a laptop or notebook for short, is a small, portable personal computer(PC).';
DBMS_LOB.WRITEAPPEND(DET_CLOB, LENGTH(DET_TXT), DET_TXT);
insert into demo_user.product_tbl values (i,t_prd(i),v_del_sch(t_prd(i)),DET_CLOB);
end if
*/
END LOOP;
PERFORM aws_oracle_ext.array$clear_procedure('demo_user.update_product_details');
END;$procedure$;
AWS SCT has identified that it couldn’t convert portions of code when it interprets code involving DBMS_LOB subroutines and varray data types. In response, it annotates the code with error codes 5340 and 5030, flagging them with a Severity Critical message, prompting the need for manual code intervention. Therefore, it’s essential to address these issues and manually convert the code with the appropriate solutions.
AWS SCT conversion issues and solutions
In this section, we address the issues identified by AWS SCT and resolve them manually with the equivalent PostgreSQL code.
For our first issue, AWS SCT didn’t convert the Oracle varray data type as marked with error code 5340 as critical:
/*
[5340 - Severity CRITICAL - PostgreSQL doesn't support the UPDATE_PRODUCT_DETAILS.T_PRD_NAMES function. Revise your code to use another function or create a user-defined function.]
:= t_prd_names('Mobile','Tablet','Laptop')
*/;
Because AWS SCT didn’t convert the varray type, we declare it as array in the manually converted code:
/* Declaring values t_prd variable which is equivalent to varrays declaration in Oracle code */
t_prd varchar[];
and assign values to this array
/* Assigning values t_prd variable as values are assigned varrays in Oracle code */
t_prd := array['Mobile','Tablet','Laptop'];
In PostgreSQL, large objects are considered as text and native string operations are performed. It doesn’t have built-in subroutines like dbms_lob.open, so AWS SCT returns the following error:
[5340 - Severity CRITICAL - PostgreSQL doesn't support SYS.DBMS_LOB.OPEN(CLOB,BINARY_INTEGER) function. Revise your code to use another function or create a user-defined function.]
DBMS_LOB.OPEN(DET_CLOB, DBMS_LOB.LOB_READWRITE)
Because large objects are considered text in PostgreSQL, the specific procedure to open or close LOBs is not needed. However, the AWS SCT extension pack creates equivalent procedures for DBMS_LOB for certain subroutines, like aws_oracle_ext.dbms_lob$write and aws_oracle_ext.dbms_lob$writeappend in its additional schema, which are used in the manually converted code:
call aws_oracle_ext.dbms_lob$write
(
lob_loc => DET_CLOB,
amount => v_amount,
"offset" => v_offset,
buffer => t_prd[i]
);
call aws_oracle_ext.dbms_lob$writeappend
(
lob_loc => DET_CLOB,
amount => LENGTH(DET_TXT::text),
buffer => DET_TXT
);
Associative array conversion
In this section, we explain how associative arrays are converted by AWS SCT using the extension pack.Associative arrays in Oracle are handled by AWS SCT using aws_oracle_ext.array procedures along with its subroutines.aws_oracle_ext.array$add_fields_to_array is equivalent to a declaration of column and data type in an associative array in Oracle. In Oracle code, it would be as follows:
TYPE del_sch IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(10);
v_del_sch del_sch;
In the AWS SCT converted code for PostgreSQL, it uses the add_fields_to_array procedure for table definition:
aws_oracle_ext$array_id$temporary := aws_oracle_ext.array$create_array
(
p_array_name => 'v_del_sch',
p_procedure_name => 'demo_user.update_product_details',
pIndexBy => 'VARCHAR2(10)'
);
perform aws_oracle_ext.array$add_fields_to_array
(
p_array_id => aws_oracle_ext$array_id$temporary,
p_fields => '[{"":"CHARACTER VARYING(64)"}]'
);
In the AWS SCT converted code for PostgreSQL, it assigns a temporary table:
aws_oracle_ext.array$create_storage_table : SCT would create type objects while converting the code as storage table.
perform aws_oracle_ext.array$create_storage_table
(
p_array_name => 'v_prd_typ',
p_procedure_name => 'demo_user.update_product_details',
p_cast_type_name => 'demo_user.update_product_details$prd_type$c',
pWithData => false
);
aws_oracle_ext.array$set_value is equivalent to assigning values to associative arrays in Oracle. In Oracle code, it would be as follows:
v_del_sch('Laptop') := 'Ready for Delivery in 3 days';
v_del_sch('Tablet') := 'Ready for Delivery in 7 days';
v_del_sch('Mobile') := 'Ready for Delivery in 5 days';
In the AWS SCT converted code for PostgreSQL, it would be as follows:
perform aws_oracle_ext.array$set_value
(
p_array_path => 'v_del_sch[''Laptop'']',
p_procedure_name => 'demo_user.update_product_details',
p_value => 'Ready for Delivery in 3 days'::character varying(64)
);
perform aws_oracle_ext.array$set_value
(
p_array_path => 'v_del_sch[''Tablet'']',
p_procedure_name => 'demo_user.update_product_details',
p_value => 'Ready for Delivery in 7 days'::character varying(64)
);
perform aws_oracle_ext.array$set_value
(
p_array_path => 'v_del_sch[''Mobile'']',
p_procedure_name => 'demo_user.update_product_details',
p_value => 'Ready for Delivery in 5 days'::character varying(64)
);
aws_oracle_ext.array$get_value is equivalent to getting values for a given index in Oracle associative arrays. In Oracle code, it would be as follows:
v_del_sch(t_prd(i)), where i is the variable to represent product names in associative arrays.
In the manually converted code, we call the aws_oracle_ext.array$get_value procedure to retrieve the value, which is the delivery time period for a given product name such as Mobile:
v_stock :=(aws_oracle_ext.array$get_value
(
p_array_value_path => 'v_del_sch[''Mobile'']',
p_procedure_name => 'demo_user.update_product_details',
p_value_datatype => null::demo_user.update_product_details$del_sch$c)
).column_value;
Manually convert code in the target database
As explained earlier in the issues and solutions section, we apply all the solutions to the procedure code manually and deploy it into the target PostgreSQL environment:
CREATE OR REPLACE PROCEDURE demo_user.update_product_details()
LANGUAGE plpgsql
AS $procedure$
DECLARE
aws_oracle_ext$array_id$temporary BIGINT;
/* Declaring values t_prd variable which is equivalent to varrays declaration in Oracle code */
t_prd varchar[];
v_del_sch VARCHAR(100) := 'v_del_sch';
DET_CLOB TEXT;
DET_TXT TEXT;
v_amount NUMERIC(8);
v_offset NUMERIC(2);
v_stock CHARACTER VARYING(64);
BEGIN
aws_oracle_ext$array_id$temporary := aws_oracle_ext.array$create_array(p_array_name => 'v_del_sch', p_procedure_name => 'demo_user.update_product_details', pIndexBy => 'VARCHAR2(10)');
PERFORM aws_oracle_ext.array$add_fields_to_array(p_array_id => aws_oracle_ext$array_id$temporary, p_fields => '[{"":"CHARACTER VARYING(64)"}]');
PERFORM aws_oracle_ext.array$create_storage_table(p_array_name => 'v_del_sch', p_procedure_name => 'demo_user.update_product_details', p_cast_type_name => 'demo_user.update_product_details$del_sch$c', pWithData => FALSE);
PERFORM aws_oracle_ext.array$set_value(p_array_path => 'v_del_sch[''Laptop'']', p_procedure_name => 'demo_user.update_product_details', p_value => 'Ready for Delivery in 3 days'::CHARACTER VARYING(64));
PERFORM aws_oracle_ext.array$set_value(p_array_path => 'v_del_sch[''Tablet'']', p_procedure_name => 'demo_user.update_product_details', p_value => 'Ready for Delivery in 7 days'::CHARACTER VARYING(64));
PERFORM aws_oracle_ext.array$set_value(p_array_path => 'v_del_sch[''Mobile'']', p_procedure_name => 'demo_user.update_product_details', p_value => 'Ready for Delivery in 5 days'::CHARACTER VARYING(64));
v_amount := 6;
v_offset := 1;
/* Assigning values t_prd variable as values are assigned varrays in Oracle code */
t_prd := array['Mobile','Tablet','Laptop'];
FOR i IN 1..3 LOOP
call aws_oracle_ext.dbms_lob$createtemporary(lob_loc => DET_CLOB);
IF t_prd[i] = 'Mobile' then
/* Adding manual converted code here to include dbms_lob.write and get the value for stock column*/
CALL aws_oracle_ext.dbms_lob$write(lob_loc => DET_CLOB, amount => v_amount, "offset" => v_offset, buffer => t_prd[i]);
v_stock := (aws_oracle_ext.array$get_value(p_array_value_path => 'v_del_sch[''Mobile'']', p_procedure_name => 'demo_user.update_product_details', p_value_datatype => NULL::demo_user.update_product_details$del_sch$c)).column_value;
/* Added manual converted code above to include dbms_lob.write and get the value for stock column */
DET_TXT := ' is a portable telephone that can make and receive calls over a radio frequency link while the user is moving within a telephone service area, as opposed to a fixed-location phone (landline phone).'::TEXT;
CALL aws_oracle_ext.dbms_lob$writeappend(lob_loc => DET_CLOB, amount => LENGTH(DET_TXT::TEXT), buffer => DET_TXT);
/* Added manual converted code above to assign v_stock variable value to stock column */
insert into demo_user.product_tbl values (i,t_prd[i],v_stock,DET_CLOB);
END IF;
IF t_prd[i] = 'Tablet' then
/* Adding manual converted code here to include dbms_lob.write and get the value for stock column*/
CALL aws_oracle_ext.dbms_lob$write(lob_loc => DET_CLOB, amount => v_amount, "offset" => v_offset, buffer => t_prd[i]);
v_stock := (aws_oracle_ext.array$get_value(p_array_value_path => 'v_del_sch[''Tablet'']', p_procedure_name => 'demo_user.update_product_details', p_value_datatype => NULL::demo_user.update_product_details$del_sch$c)).column_value;
/* Added manual converted code above to include dbms_lob.write and get the value for stock column */
DET_TXT := ' is a mobile device, typically with a mobile operating system and touchscreen display processing circuitry, and a rechargeable battery in a single, thin and flat package.'::TEXT;
CALL aws_oracle_ext.dbms_lob$writeappend(lob_loc => DET_CLOB, amount => LENGTH(DET_TXT::TEXT), buffer => DET_TXT);
/* Added manual converted code above to assign v_stock variable value to stock column */
insert into demo_user.product_tbl values (i,t_prd[i],v_stock,DET_CLOB);
END IF;
IF t_prd[i] = 'Laptop' then
/* Adding manual converted code here to include dbms_lob.write and get the value for stock column*/
CALL aws_oracle_ext.dbms_lob$write(lob_loc => DET_CLOB, amount => v_amount, "offset" => v_offset, buffer => t_prd[i]);
v_stock := (aws_oracle_ext.array$get_value(p_array_value_path => 'v_del_sch[''Laptop'']', p_procedure_name => 'demo_user.update_product_details', p_value_datatype => NULL::demo_user.update_product_details$del_sch$c)).column_value;
/* Added manual converted code above to include dbms_lob.write and get the value for stock column */
DET_TXT := ' is known either as a laptop or notebook for short, is a small, portable personal computer(PC).'::TEXT;
CALL aws_oracle_ext.dbms_lob$writeappend(lob_loc => DET_CLOB, amount => LENGTH(DET_TXT::TEXT), buffer => DET_TXT);
/* Added manual converted code above to assign v_stock variable value to stock column */
insert into demo_user.product_tbl values (i,t_prd[i],v_stock,DET_CLOB);
END IF;
END LOOP;
PERFORM aws_oracle_ext.array$clear_procedure('demo_user.update_product_details');
END;
$procedure$
;
Run the procedure in Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL:
SQL> select * from demo_user.product_tbl;
SQL> call demo_user.update_product_details();
SQL> select * from demo_user.product_tbl;
Compare the result set in the source and target
To test the solution, we must compare the results set in the product_tbl table, both in the source and target, to confirm that they match.
In the source Oracle database, use the following code:
SQL> select * from demo_user.product_tbl;
In the target database, use the following code:
SQL> select * from demo_user.product_tbl;
We can confirm that the records result set matches.
Clean up
To avoid ongoing costs, clean up the resources that you created for this post which you no longer need as part of this solution.
In Oracle, use the following code:
drop table demo_user.product_tbl;
drop procedure demo_user.update_product_details;
In Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL, use the following code:
drop table demo_user.product_tbl;
drop procedure demo_user.update_product_details;
drop type demo_user.update_product_details$del_sch$c;
Summary
In this post, we demonstrated how to convert Oracle database packages and procedures that have DBMS_LOB and varrays or associative arrays to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL using the AWS SCT extension pack with minimal changes. Implement this solution in a non-production environment and test it thoroughly so that it matches the expected outcomes.
If you have any questions or comments about this post, use the comments section to post your thoughts.
About the Authors
Chithra Krishnamurthy is a Database Consultant with the Professional Services team, working at Amazon Web Services. She works with enterprise customers to help them achieve their business outcomes by providing technical guidance for Database Migrations to AWS and provide solutions for High Availability and DR strategies.
Sagar Patel is a Senior Database Specialty Architect with the Professional Services team at Amazon Web Services. He works as a database migration specialist to provide technical guidance and help Amazon customers migrate their on-premises databases to AWS.
Chithra Krishnamurthy is a Database Consultant with the Professional Services team, working at Amazon Web Services. She works with enterprise customers to help them achieve their business outcomes by providing technical guidance for Database Migrations to AWS and provide solutions for High Availability and DR strategies.
Sagar Patel is a Senior Database Specialty Architect with the Professional Services team at Amazon Web Services. He works as a database migration specialist to provide technical guidance and help Amazon customers migrate their on-premises databases to AWS.