0

I have my hands on an Oracle .dmp file and want to import some tables it contains to our PostgreSQL database. The answers here recommend migration tools on a live Oracle DB, but the table (and therefore, the DB) we're most interested in is too big for the free Oracle license.
The answer to that question put me down an interesting path: using impdp to convert tables in the .dmp file to .sql scripts. The problem now seems to be making them compatible with PostgreSQL. Each row appears to be INSERTd with a format:

  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
           4,9.15265846187305E-07,4,5740,1,3.49431637447542E+35,4.42973871612322E+35,37,1,5740,4.42973871612322E+35,
           TO_DATE('2016-08-08 22:07:25',df),'434C4F5345442020202020202020202020202020202020202020202020202020','55504C4F41444544202020202020202020202020202020202020202020202020',nv,2,nv;

where the stmt appears to be duplicating each row by also inserting it into the `"SYS"."IMPDP_STATS" table: 

stmt varchar(300) = 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';

Using SQLines Studio hasn't really helped clean up the PostgreSQL script.

Improve this question
2
  • Short answer: you can't Commented Oct 18, 2016 at 18:26
  • You can try to use sed or awk to massage the SQL file. If the task is too complicated for these tools, write a Perl script. Using prepared statements for the INSERT is a good idea. Commented Oct 19, 2016 at 7:36
Related questions
1993
How can I change a PostgreSQL user password?
1701
How can I drop all the tables in a PostgreSQL database?
812
How to import CSV file data into a PostgreSQL table
Load 6 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.