We are migrating the Oracle to PostgreSQL and we are facing the issue for the pgloader where i need to insert the compressed blob data in the XML_BILL_OBJ coloumn where it is tagged by and , I have replaced the encoding lang as well but still getting the error.
I have even tried of changing the datatype which i though may be the issue but it didnt worked.
1. show what you did,
CODE im working on -
LOAD CSV
FROM 'file Path.dat'
INTO postgresql://dbconnection detail
TARGET TABLE tablename
(
RAMP_II,
BILL_ISSUE_DT,
BILL_SEQ_NBR,
PRINT_LANG_CD,
ARCV_IND,
BILL_LOAD_DT,
PE,
ACCT_TYPE,
ACCT_TYPE_2,
ACCT_TYPE_3,
SAMPLE_CD,
PRESENT_MEDIA_CD,
ADJ_IND,
BILL_TEL_NBR,
OBLG_ID,
CONF_DT,
RAMP_BILL_ISSUE_DT,
PYMT_METH_CD,
TOT_BILL_AMT,
TOT_CURR_REV,
TOT_PAST_DUE_AMT,
LEC_ID,
GEOCODE,
XML_BILL_OBJ
)
WITH
fields terminated by '|',
fields optionally enclosed by '"',
fields escaped by '"'
--)
--fields optionally enclosed by '"',
--fields escaped by '"'
SET client_encoding to 'UTF-8'
;
2. show the result you got,
2.1- ERROR non whitespace after quoted data #<CSV-READER LINE-IDX:1 CHARACTER-LINE-IDX:4 CHARACTER-IDX:461
these error is popping when im inserting the compressed blob data into the xml_billl_obj column.
2.2 - ESRAP-PARSE-ERROR: At
fields terminated by '|',
fields optionally enclosed by '"' for column XML_BILL_OBJ
^ (Line 33, Column 33, Position 657)
In context COMMAND:
-For the ( fields terminated by '|', fields optionally enclosed by '"', fields escaped by '") when i try to assign the enclosed by field to only xml_bill_obj column it gives me syntax error
explain how the result is not what you expected.
pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 2.3.2
[ ] did you search for other similar issues?
I have searched all over te internet but not able to find any solution tried many ways to sort it out.
We are migrating the Oracle to PostgreSQL and we are facing the issue for the pgloader where i need to insert the compressed blob data in the XML_BILL_OBJ coloumn where it is tagged by and , I have replaced the encoding lang as well but still getting the error.
I have even tried of changing the datatype which i though may be the issue but it didnt worked.
1. show what you did, CODE im working on -
LOAD CSV FROM 'file Path.dat' INTO postgresql://dbconnection detail TARGET TABLE tablename ( RAMP_II, BILL_ISSUE_DT, BILL_SEQ_NBR, PRINT_LANG_CD, ARCV_IND, BILL_LOAD_DT, PE, ACCT_TYPE, ACCT_TYPE_2, ACCT_TYPE_3, SAMPLE_CD, PRESENT_MEDIA_CD, ADJ_IND, BILL_TEL_NBR, OBLG_ID, CONF_DT, RAMP_BILL_ISSUE_DT, PYMT_METH_CD, TOT_BILL_AMT, TOT_CURR_REV, TOT_PAST_DUE_AMT, LEC_ID, GEOCODE, XML_BILL_OBJ ) WITH fields terminated by '|', fields optionally enclosed by '"', fields escaped by '"' --) --fields optionally enclosed by '"', --fields escaped by '"'
SET client_encoding to 'UTF-8' ;
2. show the result you got,
2.1- ERROR non whitespace after quoted data #<CSV-READER LINE-IDX:1 CHARACTER-LINE-IDX:4 CHARACTER-IDX:461
2.2 - ESRAP-PARSE-ERROR: At
fields terminated by '|', fields optionally enclosed by '"' for column XML_BILL_OBJ ^ (Line 33, Column 33, Position 657)
In context COMMAND:
-For the ( fields terminated by '|', fields optionally enclosed by '"', fields escaped by '") when i try to assign the enclosed by field to only xml_bill_obj column it gives me syntax error
explain how the result is not what you expected.
pgloader --version
pgloader version "3.6.7~devel" compiled with SBCL 2.3.2
[ ] did you search for other similar issues? I have searched all over te internet but not able to find any solution tried many ways to sort it out.
[ ] how can I reproduce the bug?
find the above load file -
[ ] pgloader output you obtain
2024-08-08T10:16:09.011000Z LOG pgloader version "3.6.7~devel" 2024-08-08T10:16:09.336008Z ERROR PostgreSQL ["\"dps\".\"bills_bkp\""] Database error 22P02: invalid input syntax for type bytea CONTEXT: COPY bills_bkp, line 1, column xml_bill_obj: "\"x<9c><85>ZÝr<9b>È^R¾OÕy<87><89>vkë<9c>*[FÈNì¬C<8a>?Ûl^PÒ^BN¢ÜP^H<8d>-Ê^Rh^AÙÑy<8c>óħ{^FÐ<80>ÀÎE"¦ûë..." 2024-08-08T10:16:09.384010Z ERROR PostgreSQL ["\"dps\".\"bills_bkp\""] Database error 22021: invalid byte sequence for encoding "UTF8": 0x00 CONTEXT: COPY bills_bkp, line 1 2024-08-08T10:16:09.432012Z ERROR PostgreSQL ["\"dps\".\"bills_bkp\""] Database error 22021: invalid byte sequence for encoding "UTF8": 0x00 CONTEXT: COPY bills_bkp, line 1 2024-08-08T10:16:09.480013Z ERROR PostgreSQL ["\"dps\".\"bills_bkp\""] Database error 22021: invalid byte sequence for encoding "UTF8": 0x00 CONTEXT: COPY bills_bkp, line 2 2024-08-08T10:16:09.536014Z ERROR PostgreSQL ["\"dps\".\"bills_bkp\""] Database error 22007: invalid input syntax for type date: " X§9]~"¿<97>ø#権î^F<9e>îØNóýa$^?<84>¯×ÿ<8c>Ë=^N"ÚÂmí^G<9b>Óª,øþ<83>4ºúx!]<8e>¡x½/ám¦.!l^L^Z)<95>#^HÈ<92>Ò^K^B<8f><9e>^Rmú<83>à®mܱ?פ±¢«îÔ&à^]^SbÙ<84>i~Ú%d" CONTEXT: COPY bills_bkp, line 1, column bill_issue_dt: " X§9]~"¿<97>ø#権î^F<9e>îØNóýa$^?<84>¯×ÿ<8c>Ë=^N"ÚÂmí^G<9b>Óª,øþ<83>4ºúx!]<8e>¡x½/ám¦.!..." 2024-08-08T10:16:09.539014Z ERROR PostgreSQL ["\"dps\".\"bills_bkp\""] Database error 22021: invalid byte sequence for encoding "UTF8": 0x00 CONTEXT: COPY bills_bkp, line 3 2024-08-08T10:16:09.546014Z LOG report summary reset table name errors rows bytes total time
COPY Threads Completion 0 2 0.313s
samplrec.txt
The samplrec.dat is the input file which we have changed it to txt file.
Please let know what should be done here, Thank you in advance.