Migrating Postgres to Oracle 11g
mfz, January 23, 2010 - 8:39 pm UTC
Tom -
I am working on a requirement of migrating Postgres on Linux to Oracle 11g on Windows . The databases are of less than 7 GB .
What is the best way to move the data , data with other objects ( stored procedures & functions) ?
I understand not all stored procedures can be used directly.
I would need to refactor the code to adapt to Oracle's locking / multiversioing features .
Jon, May 05, 2010 - 11:34 am UTC
I have the same issue on the following code but migrating from Postgres to 11gR2. Im trying to adjust code to Oracle but have NO idea at all:
CREATE OR REPLACE FUNCTION "f_TISS"(parm1 in number, parm2 in number)
RETURN SETOF "TISS" IS
declare
ret "TISS";
i int;
ordem int;
declare reg record;
begin
ret."TISS_cip" := parm1;
ret."TISS_visita" := parm2;
i := 0;
ordem := 0;
for reg in
<big_select>
loop
i := i + 1;
if (i > 5) then
ordem := ordem + 1;
ret."TISS_ordem" := ordem;
return next ret;
i := 1;
end if;
raise notice '%) - [%]', i, reg."Exa_nome";
if (i > 1) then
ret."TISS_det_1" := ret."TISS_det_1" || '# ';
ret."TISS_det_2" := ret."TISS_det_2" || '# ';
else
ret."TISS_det_1" := '';
ret."TISS_det_2" := '';
end if;
ret."TISS_det_1" := ret."TISS_det_1" || reg."Exa_nome";
ret."TISS_det_2" :=
ret."TISS_det_2"
|| reg."MPacExa_dataInclusao"
|| '; ' || reg."Qtdchcv_codigoAmb"
|| '; ' || reg."Exa_nome"
|| '; ' || cast((reg.valorch * reg.qtdch) as varchar)
|| '; ' || cast((reg.valorch * reg.qtdch) as varchar);
end loop;
if (i < 5) then
while (i < 5) loop
i := i + 1;
ret."TISS_det_1" := ret."TISS_det_1" || '# ';
ret."TISS_det_2" := ret."TISS_det_2" || '# ';
end loop;
raise notice 'Preenchido det_1 [%]', ret."TISS_det_1";
raise notice 'Preenchido det_2 [%]', ret."TISS_det_2";
ordem := ordem + 1;
ret."TISS_ordem" = ordem;
end if;
return next ret;
return;
end;
I know the basics of PL/SQL. So im really confused about this conversion. Dont now even how to start.
I appreciate some help from you guys.
Thanks in advance.
May 06, 2010 - 2:06 pm UTC
so, you give me a piece of code that doesn't work and expect me to be able to convert it into something that can?
And you don't even tell us what the goal of the procedure is? what the constraints are? what the inputs are? what the outputs are? What is trying to be done?
You know, the specification???
I have no clue what this code is supposed to do - that would be mandatory as step one for anyone to port anything.
Regarding porting functions to oracle
Jeetu, September 13, 2010 - 7:05 am UTC
Hi Tom,
I am in quest of porting db2 functions to Oracle.In our current db2 functions repository there are several functions which are returning "Tables" as such, following is a pseudo code
CREATE FUNCTION Some_Name ( some_input VARCHAR(128) )
returns table ( Some_int integer ) language sql reads sql data
return SOME_SQL_QUERY
And interestingly these returning tables from two or three functions are used in other function with left and right outer joins between them.
So I had two options of replicating the same functionality in oracle
1. Use table functions with object types.But the "joins" would not work.
2. Use ref cursors to fetch and use temporary tables to store data and replace joined functions with these tables. But I am not happy with this solution since creating GTT dynamically for multiple times looks semantically not good and I fear there would be DB performance issue.
Could you please share your opinion in this case.
Thanks
Jeetu
September 13, 2010 - 2:34 pm UTC
1) why wouldn't joins work?
and when porting, you should be aware of the fact that sometimes you have to RE-ENGINEER, re-implement, do things differently.
If you move from Oracle to DB2 - you would.
If you move from DB2 to Oracle - you would.
This looks like a "view" to me - why would you not use a view?
migrating from postgresql to oracle
Bharathi, December 25, 2012 - 11:55 pm UTC
Hi Tom,
I am migrating some functions from postgres to oracle. I've some problem while migration. The problem are "schema change between either other" and "some data type mis-match". Thanks in advance.
Regards
Bharathi
January 04, 2013 - 10:47 am UTC
sorry, there is so little information here that I actually have no idea what you are talking about.
"schema change between either other" and "some data
type mis-match".
they sound like partial error messages from some tool - but you don't really say what they are.