Обсуждение: ORA-24345: A Truncation or null fetch error occurred -ora2pg
Hi team ,
I am getting the below error while fetching the data from Oracle 12c using ora2pg.
DBD::Oracle::st fetchall_arrayref failed: ORA-24345: A Truncation or null fetch error occurred (DBD SUCCESS_WITH_INFO: OCIStmtFetch, LongReadLen too small and/or LongTruncOk not set)ERROR no statement executing (perhaps you need to call execute first) [for Statement "SELECT "USERS_ID","NAME","USERS" FROM "GBOPSUI"."USER_GROUP_USERS_V5" a"] at /usr/local/share/perl5/Ora2Pg.pm line 14110.
Initially did not have LongReadLen set, so I thought this was the cause. But, I have set LongReadLen, on the db handle, equal to 90000000.
Thanks,
Daulat
On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram <Daulat.Ram@exponential.com> wrote: > Initially did not have LongReadLen set, so I thought this was the cause. But, I have set LongReadLen, on the db handle,equal to 90000000. Apparently this is an oracle problem because it acceppted data longer than its type, so my guess would be that in your table you have a char(n) column that could be enlarged before the migration. <https://support.oracle.com/knowledge/Siebel/476591_1.html> Hope this helps. And please report the version of ora2pg when asking for help. Luca
On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram <Daulat.Ram@exponential.com> wrote: > Initially did not have LongReadLen set, so I thought this was the cause. But, I have set LongReadLen, on the db handle,equal to 90000000. Apparently this is an oracle problem because it acceppted data longer than its type, so my guess would be that in your table you have a char(n) column that could be enlarged before the migration. <https://support.oracle.com/knowledge/Siebel/476591_1.html> Hope this helps. And please report the version of ora2pg when asking for help. Luca
H, We are using below the ora2pg version and the data types for tables. bash-4.2$ ora2pg -v Ora2Pg v20.0 bash-4.2$ SQL> SELECT distinct data_type FROM dba_tab_columns WHERE owner='GBOP; DATA_TYPE -------------------------------------------------------------------------------- TIMESTAMP(6) FLOAT CLOB NUMBER CHAR DATE VARCHAR2 BLOB SQL> We are getting the same issue for tables which are having blob, clob and char data types. Thanks, Daulat -----Original Message----- From: Luca Ferrari <fluca1978@gmail.com> Sent: Tuesday, August 13, 2019 8:32 PM To: Daulat Ram <Daulat.Ram@exponential.com> Cc: pgsql-performance@lists.postgresql.org; pgsql-general@lists.postgresql.org Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram <Daulat.Ram@exponential.com> wrote: > Initially did not have LongReadLen set, so I thought this was the cause. But, I have set LongReadLen, on the db handle,equal to 90000000. Apparently this is an oracle problem because it acceppted data longer than its type, so my guess would be that in your tableyou have a char(n) column that could be enlarged before the migration. <https://support.oracle.com/knowledge/Siebel/476591_1.html> Hope this helps. And please report the version of ora2pg when asking for help. Luca
H, We are using below the ora2pg version and the data types for tables. bash-4.2$ ora2pg -v Ora2Pg v20.0 bash-4.2$ SQL> SELECT distinct data_type FROM dba_tab_columns WHERE owner='GBOP; DATA_TYPE -------------------------------------------------------------------------------- TIMESTAMP(6) FLOAT CLOB NUMBER CHAR DATE VARCHAR2 BLOB SQL> We are getting the same issue for tables which are having blob, clob and char data types. Thanks, Daulat -----Original Message----- From: Luca Ferrari <fluca1978@gmail.com> Sent: Tuesday, August 13, 2019 8:32 PM To: Daulat Ram <Daulat.Ram@exponential.com> Cc: pgsql-performance@lists.postgresql.org; pgsql-general@lists.postgresql.org Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram <Daulat.Ram@exponential.com> wrote: > Initially did not have LongReadLen set, so I thought this was the cause. But, I have set LongReadLen, on the db handle,equal to 90000000. Apparently this is an oracle problem because it acceppted data longer than its type, so my guess would be that in your tableyou have a char(n) column that could be enlarged before the migration. <https://support.oracle.com/knowledge/Siebel/476591_1.html> Hope this helps. And please report the version of ora2pg when asking for help. Luca
On 8/13/19 10:34 AM, Daulat Ram wrote: > H, > > We are using below the ora2pg version and the data types for tables. > > bash-4.2$ ora2pg -v > Ora2Pg v20.0 > bash-4.2$ > > SQL> SELECT distinct data_type FROM dba_tab_columns WHERE owner='GBOP; > > DATA_TYPE > -------------------------------------------------------------------------------- > TIMESTAMP(6) > FLOAT > CLOB > NUMBER > CHAR > DATE > VARCHAR2 > BLOB > > SQL> > > We are getting the same issue for tables which are having blob, clob and char data types. The ora2pg issue below seems to have more information on this: https://github.com/darold/ora2pg/issues/342 > > Thanks, > Daulat > > -----Original Message----- > From: Luca Ferrari <fluca1978@gmail.com> > Sent: Tuesday, August 13, 2019 8:32 PM > To: Daulat Ram <Daulat.Ram@exponential.com> > Cc: pgsql-performance@lists.postgresql.org; pgsql-general@lists.postgresql.org > Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg > > On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram <Daulat.Ram@exponential.com> wrote: >> Initially did not have LongReadLen set, so I thought this was the cause. But, I have set LongReadLen, on the db handle,equal to 90000000. > > Apparently this is an oracle problem because it acceppted data longer than its type, so my guess would be that in yourtable you have a > char(n) column that could be enlarged before the migration. > <https://support.oracle.com/knowledge/Siebel/476591_1.html> > Hope this helps. > And please report the version of ora2pg when asking for help. > > Luca > -- Adrian Klaver adrian.klaver@aklaver.com
On 8/13/19 10:34 AM, Daulat Ram wrote: > H, > > We are using below the ora2pg version and the data types for tables. > > bash-4.2$ ora2pg -v > Ora2Pg v20.0 > bash-4.2$ > > SQL> SELECT distinct data_type FROM dba_tab_columns WHERE owner='GBOP; > > DATA_TYPE > -------------------------------------------------------------------------------- > TIMESTAMP(6) > FLOAT > CLOB > NUMBER > CHAR > DATE > VARCHAR2 > BLOB > > SQL> > > We are getting the same issue for tables which are having blob, clob and char data types. The ora2pg issue below seems to have more information on this: https://github.com/darold/ora2pg/issues/342 > > Thanks, > Daulat > > -----Original Message----- > From: Luca Ferrari <fluca1978@gmail.com> > Sent: Tuesday, August 13, 2019 8:32 PM > To: Daulat Ram <Daulat.Ram@exponential.com> > Cc: pgsql-performance@lists.postgresql.org; pgsql-general@lists.postgresql.org > Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg > > On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram <Daulat.Ram@exponential.com> wrote: >> Initially did not have LongReadLen set, so I thought this was the cause. But, I have set LongReadLen, on the db handle,equal to 90000000. > > Apparently this is an oracle problem because it acceppted data longer than its type, so my guess would be that in yourtable you have a > char(n) column that could be enlarged before the migration. > <https://support.oracle.com/knowledge/Siebel/476591_1.html> > Hope this helps. > And please report the version of ora2pg when asking for help. > > Luca > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian ,
We have the below output. What we need to change.
bash-4.2$ ora2pg -c ora2pg.bidder.conf -t SHOW_ENCODING
Current encoding settings that will be used by Ora2Pg:
Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8
Oracle NLS_NCHAR AL32UTF8
Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6
Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
PostgreSQL CLIENT_ENCODING UTF8
Perl output encoding ''
Showing current Oracle encoding and possible PostgreSQL client encoding:
Oracle NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
Oracle NLS_NCHAR WE8MSWIN1252
Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6
Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
PostgreSQL CLIENT_ENCODING WIN1252
bash-4.2$
thanks
-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, August 13, 2019 11:27 PM
To: Daulat Ram <Daulat.Ram@exponential.com>; Luca Ferrari <fluca1978@gmail.com>
Cc: pgsql-performance@lists.postgresql.org; pgsql-general@lists.postgresql.org
Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg
On 8/13/19 10:34 AM, Daulat Ram wrote:
> H,
>
> We are using below the ora2pg version and the data types for tables.
>
> bash-4.2$ ora2pg -v
> Ora2Pg v20.0
> bash-4.2$
>
> SQL> SELECT distinct data_type FROM dba_tab_columns WHERE owner='GBOP;
>
> DATA_TYPE
> --------------------------------------------------------------------------------
> TIMESTAMP(6)
> FLOAT
> CLOB
> NUMBER
> CHAR
> DATE
> VARCHAR2
> BLOB
>
> SQL>
>
> We are getting the same issue for tables which are having blob, clob and char data types.
The ora2pg issue below seems to have more information on this:
https://github.com/darold/ora2pg/issues/342
>
> Thanks,
> Daulat
>
> -----Original Message-----
> From: Luca Ferrari <fluca1978@gmail.com>
> Sent: Tuesday, August 13, 2019 8:32 PM
> To: Daulat Ram <Daulat.Ram@exponential.com>
> Cc: pgsql-performance@lists.postgresql.org; pgsql-general@lists.postgresql.org
> Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg
>
> On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram <Daulat.Ram@exponential.com> wrote:
>> Initially did not have LongReadLen set, so I thought this was the cause. But, I have set LongReadLen, on the db
handle,equal to 90000000.
>
> Apparently this is an oracle problem because it acceppted data longer than its type, so my guess would be that in
yourtable you have a
> char(n) column that could be enlarged before the migration.
> <https://support.oracle.com/knowledge/Siebel/476591_1.html>
> Hope this helps.
> And please report the version of ora2pg when asking for help.
>
> Luca
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi Adrian ,
We have the below output. What we need to change.
bash-4.2$ ora2pg -c ora2pg.bidder.conf -t SHOW_ENCODING
Current encoding settings that will be used by Ora2Pg:
Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8
Oracle NLS_NCHAR AL32UTF8
Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6
Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
PostgreSQL CLIENT_ENCODING UTF8
Perl output encoding ''
Showing current Oracle encoding and possible PostgreSQL client encoding:
Oracle NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
Oracle NLS_NCHAR WE8MSWIN1252
Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6
Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
PostgreSQL CLIENT_ENCODING WIN1252
bash-4.2$
thanks
-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, August 13, 2019 11:27 PM
To: Daulat Ram <Daulat.Ram@exponential.com>; Luca Ferrari <fluca1978@gmail.com>
Cc: pgsql-performance@lists.postgresql.org; pgsql-general@lists.postgresql.org
Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg
On 8/13/19 10:34 AM, Daulat Ram wrote:
> H,
>
> We are using below the ora2pg version and the data types for tables.
>
> bash-4.2$ ora2pg -v
> Ora2Pg v20.0
> bash-4.2$
>
> SQL> SELECT distinct data_type FROM dba_tab_columns WHERE owner='GBOP;
>
> DATA_TYPE
> --------------------------------------------------------------------------------
> TIMESTAMP(6)
> FLOAT
> CLOB
> NUMBER
> CHAR
> DATE
> VARCHAR2
> BLOB
>
> SQL>
>
> We are getting the same issue for tables which are having blob, clob and char data types.
The ora2pg issue below seems to have more information on this:
https://github.com/darold/ora2pg/issues/342
>
> Thanks,
> Daulat
>
> -----Original Message-----
> From: Luca Ferrari <fluca1978@gmail.com>
> Sent: Tuesday, August 13, 2019 8:32 PM
> To: Daulat Ram <Daulat.Ram@exponential.com>
> Cc: pgsql-performance@lists.postgresql.org; pgsql-general@lists.postgresql.org
> Subject: Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg
>
> On Tue, Aug 13, 2019 at 10:23 AM Daulat Ram <Daulat.Ram@exponential.com> wrote:
>> Initially did not have LongReadLen set, so I thought this was the cause. But, I have set LongReadLen, on the db
handle,equal to 90000000.
>
> Apparently this is an oracle problem because it acceppted data longer than its type, so my guess would be that in
yourtable you have a
> char(n) column that could be enlarged before the migration.
> <https://support.oracle.com/knowledge/Siebel/476591_1.html>
> Hope this helps.
> And please report the version of ora2pg when asking for help.
>
> Luca
>
--
Adrian Klaver
adrian.klaver@aklaver.com