{"action":"created","issue":{"url":"https://api.github.com/repos/darold/ora2pg/issues/183","repository_url":"https://api.github.com/repos/darold/ora2pg","labels_url":"https://api.github.com/repos/darold/ora2pg/issues/183/labels{/name}","comments_url":"https://api.github.com/repos/darold/ora2pg/issues/183/comments","events_url":"https://api.github.com/repos/darold/ora2pg/issues/183/events","html_url":"https://github.com/darold/ora2pg/issues/183","id":147811086,"number":183,"title":"Problems with varchar2 size overflow - import data directly","user":{"login":"bcosso","id":15462005,"avatar_url":"https://avatars.githubusercontent.com/u/15462005?v=3","gravatar_id":"","url":"https://api.github.com/users/bcosso","html_url":"https://github.com/bcosso","followers_url":"https://api.github.com/users/bcosso/followers","following_url":"https://api.github.com/users/bcosso/following{/other_user}","gists_url":"https://api.github.com/users/bcosso/gists{/gist_id}","starred_url":"https://api.github.com/users/bcosso/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bcosso/subscriptions","organizations_url":"https://api.github.com/users/bcosso/orgs","repos_url":"https://api.github.com/users/bcosso/repos","events_url":"https://api.github.com/users/bcosso/events{/privacy}","received_events_url":"https://api.github.com/users/bcosso/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2016-04-12T16:49:23Z","updated_at":"2016-04-12T17:32:29Z","closed_at":null,"body":"Hi, we have been having some problems when importing data directly from Ora2pg (instead of generate insert commands. The insert commands runs normally when executed manually in pgAdmin. \r\nAn overflow is happening when we insert with the E'data'. We already turned off standard_conforming_strings settings but didn't work. The inserted field, when not overflowed, inserts the entire \"  E'data' \", instead of \"data\" only, that's why is overflowing.\r\nI have created a workaround (I am not really a Perl developer) that worked for us so far, but hopefully you will find a better solution.\r\nI added to format_data_row function this code before the closing main \"for\" brackets:\r\n\r\n`\t\t######################################################################################\r\n\t\t#Changes due to migration errors of Null fields and characters like \"E'\", \"'\" and \"\\\\\"\r\n\t\tif ($row->[$idx] && $src_data_types->[$idx] =~ /(VARCHAR2|char|text|xml)/i){\r\n\t\t\t$self->logit('\\r\\n Is character Type:' . $row->[$idx] , 1);\r\n\t\t\t\r\n\t\t\twhile (index($row->[$idx], \"'\") != -1){\r\n\t\t\t\t$row->[$idx] = str_replace(\"'\", \"\", $row->[$idx]);\r\n\t\t\t}\r\n\t\t\t\r\n\t\t\twhile (index($row->[$idx], \"\\\\\\\\\") != -1){\r\n\t\t\t\t$row->[$idx] = str_replace(\"\\\\\\\\\", \"\\\\\", $row->[$idx]);\r\n\t\t\t}\r\n\t\t}\r\n\t\tif ($row->[$idx] eq \"null\" || $row->[$idx] eq \"NULL\"){\r\n\t\t\t$self->logit('\\r\\n Result Null Type:' . $src_data_types->[$idx] , 1);\r\n\t\t\t$row->[$idx] = undef;\r\n\t\t}\r\n\t\t\r\n\t\t$self->logit('\\r\\n Contents:' . $row->[$idx] . '--', 1);\r\n\t\t#######################################################################################`\r\n\r\nAnd also used the following function for replace:\r\n\r\n`sub str_replace {\r\n\tmy $replace_this = shift;\r\n\tmy $with_this  = shift; \r\n\tmy $string   = shift;\r\n\t\r\n\tmy $length = length($string);\r\n\tmy $target = length($replace_this);\r\n\t\r\n\tfor(my $i=0; $i<$length - $target + 1; $i++) {\r\n\t\tif(substr($string,$i,$target) eq $replace_this) {\r\n\t\t\t$string = substr($string,0,$i) . $with_this . substr($string,$i+$target);\r\n\t\t\treturn $string; #Comment this if you what a global replace\r\n\t\t}\r\n\t}\r\n\treturn $string;\r\n}\r\n\r\n`\r\n\r\n[Ora2Pg.pm.txt](https://github.com/darold/ora2pg/files/215500/Ora2Pg.pm.txt)\r\n"},"comment":{"url":"https://api.github.com/repos/darold/ora2pg/issues/comments/209020294","html_url":"https://github.com/darold/ora2pg/issues/183#issuecomment-209020294","issue_url":"https://api.github.com/repos/darold/ora2pg/issues/183","id":209020294,"user":{"login":"darold","id":538862,"avatar_url":"https://avatars.githubusercontent.com/u/538862?v=3","gravatar_id":"","url":"https://api.github.com/users/darold","html_url":"https://github.com/darold","followers_url":"https://api.github.com/users/darold/followers","following_url":"https://api.github.com/users/darold/following{/other_user}","gists_url":"https://api.github.com/users/darold/gists{/gist_id}","starred_url":"https://api.github.com/users/darold/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/darold/subscriptions","organizations_url":"https://api.github.com/users/darold/orgs","repos_url":"https://api.github.com/users/darold/repos","events_url":"https://api.github.com/users/darold/events{/privacy}","received_events_url":"https://api.github.com/users/darold/received_events","type":"User","site_admin":false},"created_at":"2016-04-12T17:32:28Z","updated_at":"2016-04-12T17:32:28Z","body":"There was bug in previous and current release when exporting data. If you use latest development code you may not have to fix anything. Please use COPY export type as INSERT is only given for convenience and it is really slow.\r\n\r\nLet us know."}}