This discussion is archived
3 Replies Latest reply: Oct 10, 2012 3:16 AM by mkirtley-Oracle RSS

Migration Procss from Postgesql to Oracle 10g

944300 Newbie
Currently Being Moderated
hi
any one can suggest me
how can i Migrate from Postgresql to Oracle 10g
is there any tool for this free witch i can i chek
i am getting Problem to convert function

Migration Process from Postgesql to Oracle 10g
-------------
CREATE OR REPLACE FUNCTION getgraphdatanew()
RETURNS SETOF sessiongraphdata AS
$BODY$
declare
v_row RECORD; --session%ROWTYPE;
v_row1 sessiongraphdata%ROWTYPE;
v_row2 RECORD;
v_cursor refcursor;
v_lastupd timestamp without time zone;

begin
     --if not exists(select sesno from sessiongraphdata) then
          truncate table sessiongraphdata; this would probably re-initialise the table and speed up
     --end if;
     --v_lastupd:=now();
     --first copy the sessions to the graphdata table
     --NOTE: only those sessions are copied which are not already in the graphdata

     --here may be a cursor can be used to avoid any updates between these two statements
     ---LOCK TABLE session IN SHARE UPDATE EXCLUSIVE MODE;
     delete from sessiongraphdata;
     insert into sessiongraphdata (sesno,actno,subsno,nasno,bytepulse,timepulse,start,lastupd,bytesin,
               bytesout,state,ipaddr,macaddr,closedt,closereason,lastbytesin,lastbytesout,internalid,cpno,snatip,qosno,userid)
          select sesno,actno,subsno,nasno,bytepulse,timepulse,start,lastupd,bytesin,
               bytesout,state,ipaddr,macaddr,closedt,closereason,lastbytesin,lastbytesout,internalid,cpno,snatip,qosno,userid
          from session where state !='W' ;

     delete from session where state='C' and sesno in (select sesno from sessiongraphdata where state='C');
     ---update session set lastbytesin=0, lastbytesout=0;

     insert into sessionhistory (sesno,actno,subsno, nasno,bytepulse,timepulse,start,lastupd,bytesin,
          bytesout,state,ipaddr,macaddr,closedt,closereason,lastbytesin,lastbytesout,internalid,cpno,snatip,qosno,userid)
     select sesno,actno,subsno, nasno,bytepulse,timepulse,start,lastupd,bytesin,
          bytesout,state,ipaddr,macaddr,closedt,closereason,lastbytesin,lastbytesout,internalid,cpno,snatip,qosno,userid
          from sessiongraphdata where state='C';

     open v_cursor for select * from sessiongraphdata;
     fetch v_cursor into v_row2;
     while found
     loop
          v_row1.actno=v_row2.actno;
          v_row1.lastbytesin=v_row2.lastbytesin;
          v_row1.lastbytesout=v_row2.lastbytesout;
          v_row1.lastupd=v_row2.lastupd;
          v_row1.sesno=v_row2.sesno;
          v_row1.subsno=v_row2.subsno;
          return next v_row1;
          fetch v_cursor into v_row2;
     end loop;
     return;
end;

$BODY$
LANGUAGE plpgsql VOLATILE
  • 1. Re: Migration Procss from Postgesql to Oracle 10g
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    There is the SQL*Developer migration workbench but his currently does not support the migration from Postgres to Oracle.
    I think your only option is to find a third party tool that may be able to help.
    There are some links to third party partners and support from the the following -

    Oracle Database Migration Technology

    http://www.oracle.com/technetwork/database/migration/index-084442.html

    under the heading -

    Finding More Help: Oracle Migration Partners and Support

    Regards,
    Mike
  • 2. Re: Migration Procss from Postgesql to Oracle 10g
    944300 Newbie
    Currently Being Moderated
    Thank you so much for you response, I went through the link. There are lot of tool available, however I did not find any tool which support PostgreSQL to Oracle. Can somebody advise me on that?
  • 3. Re: Migration Procss from Postgesql to Oracle 10g
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    If you can't find any tools that support a Postgres migration then all I can suggest is that you try one of the contacts under the heading -

    Finding More Help: Oracle Migration Partners and Support

    on the migration site -

    http://www.oracle.com/technetwork/database/migration/index-084442.html

    I made an internet search using the term "postgres migration to oracle tools" and found references to some tools that may be able to help. It looked like some were free and others were paid for but you could try this and see if any could help you.

    Regards,
    Mike

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points