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.
Hi Team,
Due to the different datatype mapping, example INT is parsed as BIGINT from MYSQL, it has created an issue where certain functions are concerned since their body is passed as is and a type mismatch occurs.
Please consider this example below for better understanding.
drop table if exists bar;
create table bar(
id int,
p_name varchar(10)
);
drop function if exists foo;
delimiter //
create function foo (p_id int)
returns varchar(20)
reads sql data
begin
return (
select p_name from bar where p_id=id
);
end//
delimiter ;
CREATE OR REPLACE VIEW v1
as
select foo(id) as p_name
from bar;
This schema when exported, is exported as:
CREATE TABLE bar (
id bigint,
p_name varchar(10)
) ;
CREATE OR REPLACE FUNCTION foo (p_id integer) RETURNS varchar AS $body$
BEGIN
return(select p_name from bar where p_id=id);
end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
CREATE OR REPLACE VIEW v22 AS select foo(bar.id) AS p_name FROM bar;
The table and the function get created fine. But the view creation errors out with:
ERROR: function foo(bigint) does not exist
LINE 1: CREATE OR REPLACE VIEW v22 AS select foo(bar.id) AS p_name F...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
As you can see, since the table column is of type BIGINT but the parameter is INT, it errors out.
The same view definition if run as:
CREATE OR REPLACE VIEW v22 AS select foo(bar.id::integer) AS p_name FROM bar;
Will work fine.
Regarding this I had a query, if the max/min values of signed integers are the same in MYSQL and PG, what is the need to map them to a bigger datatype?
Hi Team, Due to the different datatype mapping, example INT is parsed as BIGINT from MYSQL, it has created an issue where certain functions are concerned since their body is passed as is and a type mismatch occurs.
Please consider this example below for better understanding.
This schema when exported, is exported as:
The table and the function get created fine. But the view creation errors out with:
As you can see, since the table column is of type
BIGINT
but the parameter isINT
, it errors out.The same view definition if run as:
Will work fine.
Regarding this I had a query, if the max/min values of signed integers are the same in MYSQL and PG, what is the need to map them to a bigger datatype?
Thank You for your insights and help.