cybertec-postgresql / ora_migrator

Tools for Oracle to PostgreSQL migration
Other
109 stars 31 forks source link

public.oracle_test_table throws an error when is called on a table that has no text column #15

Closed postgresql007 closed 5 years ago

postgresql007 commented 5 years ago

public.oracle_test_table() throws an error when is called on a table that has no text column.

CREATE OR REPLACE FUNCTION oracle_import_errors(server text)
RETURNS int8 AS
$$
        DECLARE
                v_total_count           int8;
                v_tables                record;
        BEGIN
                DROP TABLE IF EXISTS pgsql_stage.oracle_error;
                CREATE TABLE pgsql_stage.oracle_error (
                        id              serial8         PRIMARY KEY,
                        schema          name            NOT NULL,
                        table_name      name            NOT NULL,
                        rowid           text            NOT NULL,
                        message         text            NOT NULL,
                        UNIQUE (schema, table_name, rowid)
                );

                v_total_count := 0;

                FOR v_tables IN SELECT  *
                        FROM    pgsql_stage.tables
                        WHERE   migrate = true
                LOOP
                        RAISE NOTICE 'processing % in %', v_tables.table_name, v_tables.schema;
                        INSERT INTO pgsql_stage.oracle_error (schema, table_name, rowid, message)
                                SELECT  v_tables.schema, v_tables.table_name, rowid, message
                                FROM    oracle_test_table(server, v_tables.schema, v_tables.table_name) AS a;
                END LOOP;

                RETURN v_total_count;
        END;
$$ LANGUAGE 'plpgsql';

otherwise this one fails.