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.
This regression was noticed while migrating the Sakila schema of MySQL. Earlier the import used to go through but now it fails on a particular procedure(rewards_report). This was seen after the commit 8b64f1a543c9e63d3eff80d43b83a74298bfb104.
The major difference which I could notice throwing the error:
Before regression:
CREATE OR REPLACE PROCEDURE rewards_report ( min_monthly_purchases smallint , min_dollar_amount_purchased decimal(10,2) , INOUT count_rewardees integer ) AS $body$
After regression:
CREATE OR REPLACE PROCEDURE rewards_report ( min_monthly_purchases smallint , min_dollar_amount_purchased decimal(10,2) AS $body$
The source procedure for reference :
-- Procedure structure for procedure `rewards_report`
--
DELIMITER //
CREATE PROCEDURE rewards_report (
IN min_monthly_purchases TINYINT UNSIGNED
, IN min_dollar_amount_purchased DECIMAL(10,2)
, OUT count_rewardees INT
)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT 'Provides a customizable report on best customers'
proc: BEGIN
DECLARE last_month_start DATE;
DECLARE last_month_end DATE;
/* Some sanity checks... */
IF min_monthly_purchases = 0 THEN
SELECT 'Minimum monthly purchases parameter must be > 0';
LEAVE proc;
END IF;
IF min_dollar_amount_purchased = 0.00 THEN
SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
LEAVE proc;
END IF;
/* Determine start and end time periods */
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
SET last_month_end = LAST_DAY(last_month_start);
/*
Create a temporary storage area for
Customer IDs.
*/
CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
/*
Find all customers meeting the
monthly purchase requirements
*/
INSERT INTO tmpCustomer (customer_id)
SELECT p.customer_id
FROM payment AS p
WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
GROUP BY customer_id
HAVING SUM(p.amount) > min_dollar_amount_purchased
AND COUNT(customer_id) > min_monthly_purchases;
/* Populate OUT parameter with count of found customers */
SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
/*
Output ALL customer information of matching rewardees.
Customize output as needed.
*/
SELECT c.*
FROM tmpCustomer AS t
INNER JOIN customer AS c ON t.customer_id = c.customer_id;
/* Clean up */
DROP TABLE tmpCustomer;
END //
DELIMITER ;
DELIMITER $$
Hi Team,
This regression was noticed while migrating the Sakila schema of MySQL. Earlier the import used to go through but now it fails on a particular procedure(rewards_report). This was seen after the commit
8b64f1a543c9e63d3eff80d43b83a74298bfb104
.The major difference which I could notice throwing the error:
Before regression:
After regression:
The source procedure for reference :
Thank You for your help.