--blob_to_lo option apply lo_from_bytea(0, decode( twice when importing data

[EParisot]

Hi, I'm facing an issue with --blob_to_lo option.

I need to migrate blobs from Oracle to PG, and got errors stating that "l" is not a valid hex value... of course it is not !

When I look at the logs I see:

INSERT INTO MY_TABLE (pk,resource_version,description,file_name,data_lob,fk_doc_resource,status,version,create_user,modify_user,create_date,modify_date) VALUES (?,?,?,?,lo_from_bytea(0, decode(?, 'hex')),?,?,?,?,?,?,?)17439,5,E'v0.42',E'PSTL7070-Content.jrxml',lo_from_bytea(0, decode(E'6c6f5 ....

It seems to me that the transformation is applyed twice...

I tryed to comment lines 9267, 9268, 9269 and 9271 in Ora2Pg.pm in order to have VALUES (?, ?, ?, ?, ?) value1, value2, ..., lo_from_bytea(0, decode(E'6c6f5...

but then I got values directly without the (?, ?, ?, ?...), and got another error...

Some help please ?

[EParisot]

Update:, I finally made it work, the trick was to not comment the 9267, 9268, 9269, 9271 lines as stated in prev comment but lines 19426, 19427, 19437, 19438, 19439, 19440, 19441 from Ora2Pg.pm instead, so I basicaly did the opposite :

if ($generic_type eq 'BLOB')
        {
            # Get an hexa representation of the blob data
            $col = unpack("H*",$col);
            if (!$self->{standard_conforming_strings}) {
                $col = "'$col'";
            } else {
                $col = "E'$col'";
            }
            # if (!$self->{blob_to_lo})
            # {
                if (!$self->{pg_dsn}) {
                    $col = "decode($col, 'hex')";
                }
                else
                {
                    # with prepare just send the data
                    $col =~ s/^[E]?'//;
                    $col =~ s/'$//;
                }
            # }
            # else
            # {
            #   $col = "lo_from_bytea(0, decode($col, 'hex'))";
            # }
        }

I dont know why the script tryes to convert BLOBs twice but this workaround works.

For completeness sake, what I did is :

1. edit the export_schema.sh script :

if [ "$etype" = "TABLE" ];
        then
                echo "Running: ora2pg -p -t $etype -o $ltype.sql -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf" --blob_to_lo
                ora2pg -p -t $etype -o $ltype.sql -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf --blob_to_lo
        else
                echo "Running: ora2pg -p -t $etype -o $ltype.sql -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf"
                ora2pg -p -t $etype -o $ltype.sql -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf
        fi

instead of

echo "Running: ora2pg -p -t $etype -o $ltype.sql -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf"
ora2pg -p -t $etype -o $ltype.sql -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf

and

 if [ "$etype" = "TABLE" ];
        then
                echo "Running: ora2pg -t $etype -o $ltype.sql -b $namespace/sources/${ltype}s -c $namespace/config/ora2pg.conf" --blob_to_lo
                ora2pg -t $etype -o $ltype.sql -b $namespace/sources/${ltype}s -c $namespace/config/ora2pg.conf --blob_to_lo
        else
                echo "Running: ora2pg -t $etype -o $ltype.sql -b $namespace/sources/${ltype}s -c $namespace/config/ora2pg.conf"
                ora2pg -t $etype -o $ltype.sql -b $namespace/sources/${ltype}s -c $namespace/config/ora2pg.conf
        fi

instead of

echo "Running: ora2pg -t $etype -o $ltype.sql -b $namespace/sources/${ltype}s -c $namespace/config/ora2pg.conf"
ora2pg -t $etype -o $ltype.sql -b $namespace/sources/${ltype}s -c $namespace/config/ora2pg.conf

2. edit the import_all.sh script:

# Import data directly from PostgreSQL
        if confirm "Would you like to import data from Oracle database directly into PostgreSQL?" ; then
            echo "Running: ora2pg$IMPORT_JOBS$PARALLEL_TABLES -c config/ora2pg.conf -t INSERT --pg_dsn \"$pgdsn_defined\" --pg_user $DB_OWNER --blob_to_lo"
            if [ $DEBUG -eq 0 ]; then
                ora2pg$IMPORT_JOBS$PARALLEL_TABLES -c config/ora2pg.conf -t INSERT --pg_dsn "$pgdsn_defined" --pg_user $DB_OWNER --blob_to_lo
                if [ $? -ne 0 ]; then
                    die "an error occurs when importing data."
                fi
            fi
        fi

instead of

# Import data directly from PostgreSQL
        if confirm "Would you like to import data from Oracle database directly into PostgreSQL?" ; then
            echo "Running: ora2pg$IMPORT_JOBS$PARALLEL_TABLES -c config/ora2pg.conf -t COPY --pg_dsn \"$pgdsn_defined\" --pg_user $DB_OWNER"
            if [ $DEBUG -eq 0 ]; then
                ora2pg$IMPORT_JOBS$PARALLEL_TABLES -c config/ora2pg.conf -t COPY --pg_dsn "$pgdsn_defined" --pg_user $DB_OWNER
                if [ $? -ne 0 ]; then
                    die "an error occurs when importing data."
                fi
            fi
        fi

Basically, appending --blob_to_lo to every ora2pg call and replace COPY by INSERT

[darold]

I'm not able to reproduce this issue with latest development code, if you can try it and still have the issue please post your ora2pg.conf file.

About your second fix, commit 6bbf29c allow use of --blob_to_lo with --init_project to have ora2pg command in script export_schema.sh using this option.

[EParisot]

Great thank I'll look at it next month !

[EParisot]

Well I installed the 24 version but still had to edit scripts to avoid the ERROR: invalid hexadecimal digit: "l"

[darold]

Right, I forgot to change the the generation of the import_all.sh script too, commit 24a23ca should fix that.

[EParisot]

Ok I cloned the repo again but still got a different but related error:

ERROR: invalid hexadecimal digit: "'"