Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
We have found one issue within the code related to sub-query DDLs inside procedures, particularly when a sub-query with a WHERE clause is involved.
Example Problem:
In Oracle SQL, consider the scenario where a sub-query with a WHERE clause is utilized within a procedure with no space between where and ")".This is a valid syntax in oracle. Upon conversion to ora2pg code, a specific issue arises, noted as "alias0WHERE". This arises due to the following scenario:
Example DDL:
CREATE PROCEDURE GetEmployeesInDepartment(IN department_name VARCHAR(50))
BEGIN
-- Select employees belonging to the specified department using a subquery
SELECT * FROM (
SELECT id FROM departments WHERE name = department_name
)WHERE id=1;
END
Expected Result:
CREATE OR REPLACE PROCEDURE getemployeesindepartment (IN department_name varchar(50) AS $body$
BEGIN
-- Select employees belonging to the specified department using a subquery
PERFORM * FROM (
SELECT id FROM departments WHERE name = department_name
) alias0 WHERE id=1;
END;
$body$
LANGUAGE PLPGSQL
;
Current Result (Using Ora2Pg):
CREATE OR REPLACE PROCEDURE getemployeesindepartment (IN department_name varchar(50) AS $body$
BEGIN
-- Select employees belonging to the specified department using a subquery
PERFORM * FROM (
SELECT id FROM departments WHERE name = department_name
) alias0WHERE id=1;
END;
$body$
LANGUAGE PLPGSQL
;
Issue Overview:
The issue arises when there is a sub-query associated with a WHERE clause and the ")"and where clause dont have any space inbetween. During conversion with ora2pg, a space deficiency between "alias0" and "WHERE" leads to the concatenated term "alias0WHERE".
Issue Scenario:
Consider the following SQL queries:
Select from (select name from emp)WHERE id=1; (Issue: "emp)WHERE")
Select from (select name from emp) WHERE id=1; (Correctly formatted)
In the first scenario, the absence of space results in "emp)WHERE", which is not conducive to proper ddl conversion in ora2pg.
Issue Explanation:
Upon conversion, the space deficiency between "alias0" and "WHERE" causes the concatenation of terms, leading to erroneous ddl conversion.
Implemented Solution:
To rectify the issue, an extra space has been introduced in the Regex Substitution .
sub-query where clause issue inside procedure
Hi Team,
We have found one issue within the code related to sub-query DDLs inside procedures, particularly when a sub-query with a WHERE clause is involved.
Example Problem: In Oracle SQL, consider the scenario where a sub-query with a WHERE clause is utilized within a procedure with no space between where and ")".This is a valid syntax in oracle. Upon conversion to ora2pg code, a specific issue arises, noted as "alias0WHERE". This arises due to the following scenario:
Example DDL:
CREATE PROCEDURE GetEmployeesInDepartment(IN department_name VARCHAR(50)) BEGIN -- Select employees belonging to the specified department using a subquery SELECT * FROM ( SELECT id FROM departments WHERE name = department_name )WHERE id=1; END
Expected Result:
CREATE OR REPLACE PROCEDURE getemployeesindepartment (IN department_name varchar(50) AS $body$ BEGIN -- Select employees belonging to the specified department using a subquery PERFORM * FROM ( SELECT id FROM departments WHERE name = department_name ) alias0 WHERE id=1; END; $body$ LANGUAGE PLPGSQL ;
Current Result (Using Ora2Pg):
CREATE OR REPLACE PROCEDURE getemployeesindepartment (IN department_name varchar(50) AS $body$ BEGIN -- Select employees belonging to the specified department using a subquery PERFORM * FROM ( SELECT id FROM departments WHERE name = department_name ) alias0WHERE id=1; END; $body$ LANGUAGE PLPGSQL ;
Issue Overview: The issue arises when there is a sub-query associated with a WHERE clause and the ")"and where clause dont have any space inbetween. During conversion with ora2pg, a space deficiency between "alias0" and "WHERE" leads to the concatenated term "alias0WHERE".
Issue Scenario: Consider the following SQL queries:
Select from (select name from emp)WHERE id=1; (Issue: "emp)WHERE") Select from (select name from emp) WHERE id=1; (Correctly formatted) In the first scenario, the absence of space results in "emp)WHERE", which is not conducive to proper ddl conversion in ora2pg.
Issue Explanation: Upon conversion, the space deficiency between "alias0" and "WHERE" causes the concatenation of terms, leading to erroneous ddl conversion.
Implemented Solution: To rectify the issue, an extra space has been introduced in the Regex Substitution .