Rows (total)<\/td> ~8,800<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nThe absence of stored procedures and triggers keeps the PL\/SQL conversion effort at zero. The complexity comes from three places: how Oracle handles IDENTITY columns, a BLOB column that stores JSON, and four views \u2014 three of which use Oracle-specific SQL functions that PostgreSQL does not support.<\/p>\n\n\n\n
ora2pg rates the CO schema A-3<\/strong>: straightforward migration, estimated three person-hours. The cost drivers are the three non-portable views.<\/p>\n\n\n\nProblem 1: IDENTITY Columns Have No Explicit Sequences<\/h3>\n\n\n\n CO uses GENERATED BY DEFAULT ON NULL AS IDENTITY<\/code> on every primary key column. Oracle manages the underlying sequences internally \u2014 there are no CREATE SEQUENCE<\/code> statements in the schema, and no sequence-trigger pairs like the HR schema used.<\/p>\n\n\n\nora2pg converts the Oracle syntax correctly: GENERATED BY DEFAULT ON NULL AS IDENTITY<\/code> se convierte GENERATED BY DEFAULT AS IDENTITY<\/code> in PostgreSQL. That part requires no manual work.<\/p>\n\n\n\nThe problem comes after the data load.<\/p>\n\n\n\n
When ora2pg exports data via COPY, it exports the actual row IDs from Oracle \u2014 1, 2, 3, … up to whatever the highest ID is. PostgreSQL's COPY protocol accepts those values and inserts them directly into the IDENTITY columns. The IDENTITY sequence is not called during a COPY load. After the load completes, each IDENTITY sequence still sits at its initial value of 1.<\/p>\n\n\n\n
The first INSERT into customers<\/code> after migration will attempt to use customer_id = 1<\/code>. That row already exists. The insert fails with duplicate key value violates unique constraint<\/code>.<\/p>\n\n\n\nThe fix is to advance each IDENTITY sequence past the highest loaded value:<\/p>\n\n\n
\nSET search_path = co, public;\nSELECT setval(pg_get_serial_sequence('co.customers', 'customer_id'), (SELECT MAX(customer_id) FROM co.customers));\nSELECT setval(pg_get_serial_sequence('co.orders', 'order_id'), (SELECT MAX(order_id) FROM co.orders));\nSELECT setval(pg_get_serial_sequence('co.shipments', 'shipment_id'), (SELECT MAX(shipment_id) FROM co.shipments));\nSELECT setval(pg_get_serial_sequence('co.stores', 'store_id'), (SELECT MAX(store_id) FROM co.stores));\nSELECT setval(pg_get_serial_sequence('co.products', 'product_id'), (SELECT MAX(product_id) FROM co.products));\n<\/pre><\/div>\n\n\npg_get_serial_sequence<\/code> resolves the sequence name from the table and column names. This avoids hardcoding sequence names, which vary between ora2pg versions.<\/p>\n\n\n\nRun this block immediately after the data load, before any application touches the migrated database. On a production schema, missed sequences cause INSERT failures that are easy to miss in testing if the test data set uses different IDs than production.<\/p>\n\n\n\n
Problem 2: BLOB Storing JSON Must Become JSONB, Not BYTEA<\/h3>\n\n\n\n products.product_details<\/code> is declared as BOLO<\/code> in Oracle with a CHECK (product_details IS JSON)<\/code> constraint. <\/p>\n\n\n\nOracle databases before 21c store JSON as BLOB or CLOB \u2014 there is no native JSON type.<\/p>\n\n\n\n
ora2pg maps all BLOB columns to BYTEA<\/code> by default. <\/p>\n\n\n\nPara product_image<\/code> y stores.logo<\/code>, that is correct \u2014 they store binary image data. <\/p>\n\n\n\nPara product_details<\/code>, it is wrong.<\/p>\n\n\n\nBYTEA<\/code> in PostgreSQL stores raw bytes. There are no JSON operators, no JSON functions, no validation on insert. The product_reviews<\/code> view queries product_details<\/code> as JSON using jsonb_array_elements<\/code> after the rewrite (see Problem 5 below). That function requires a JSONB<\/code> argument \u2014 it will fail with function jsonb_array_elements(bytea) does not exist<\/code> if the column is BYTEA<\/code>.<\/p>\n\n\n\nLa soluci\u00f3n es un MODIFICAR_TIPO<\/code> override before running the TABLE export:<\/p>\n\n\n\nMODIFY_TYPE PRODUCTS:PRODUCT_DETAILS:jsonb\n<\/pre><\/div>\n\n\nThis is part of a longer MODIFICAR_TIPO<\/code> line \u2014 see Step 6 below for the complete directive.<\/p>\n\n\n\nJSONB<\/code> enforces valid JSON on every insert automatically. The CHECK (product_details IS JSON)<\/code> constraint is therefore redundant after the column type change. It also uses Oracle syntax \u2014 PostgreSQL has no IS JSON<\/code> predicate \u2014 so it must be removed from the exported DDL before loading. See Problem 4.<\/p>\n\n\n\nProblem 3: FK Columns Are NUMBER(38), PKs Are bigint \u2014 Type Mismatch<\/h3>\n\n\n\n FORCE_IDENTITY_BIGINT 1<\/code> causes ora2pg to export IDENTITY primary key columns as entero largo<\/code>. PG_INTEGER_TYPE 1<\/code> convierte desnudo N\u00daMERO<\/code> (no precision) columns to entero largo<\/code>.<\/p>\n\n\n\nThe FK columns in CO \u2014 orders.customer_id<\/code>, orders.store_id<\/code>, order_items.order_id<\/code>, and others \u2014 are declared as N\u00daMERO(38)<\/code> in Oracle. That is Oracle's internal representation of an integer column declared without precision. Because the precision is explicitly 38<\/code>, PG_INTEGER_TYPE 1<\/code> does not apply. ora2pg maps them to num\u00e9rico(38)<\/code>.<\/p>\n\n\n\nThe result: primary key columns are entero largo<\/code>, foreign key columns referencing them are num\u00e9rico(38)<\/code>. PostgreSQL requires that FK and PK columns have the same type. Loading the DDL succeeds, but creating the FK constraints fails:<\/p>\n\n\n\nERROR: foreign key constraint "orders_customer_id_fk" cannot be implemented\nDETAIL: Key columns "customer_id" and "customer_id" are of incompatible types: numeric and bigint.\n<\/pre><\/div>\n\n\nThe fix is to add all FK columns and other integer columns to MODIFICAR_TIPO<\/code>:<\/p>\n\n\n\nMODIFY_TYPE INVENTORY:STORE_ID:bigint,INVENTORY:PRODUCT_ID:bigint,INVENTORY:PRODUCT_INVENTORY:integer,ORDERS:CUSTOMER_ID:bigint,ORDERS:STORE_ID:bigint,ORDER_ITEMS:ORDER_ID:bigint,ORDER_ITEMS:LINE_ITEM_ID:integer,ORDER_ITEMS:PRODUCT_ID:bigint,ORDER_ITEMS:QUANTITY:integer,ORDER_ITEMS:SHIPMENT_ID:bigint,SHIPMENTS:STORE_ID:bigint,SHIPMENTS:CUSTOMER_ID:bigint\n<\/pre><\/div>\n\n\nMODIFICAR_TIPO<\/code> reads only the first line of the directive. All overrides \u2014 including the JSONB override from Problem 2 and the num\u00e9rico<\/code> overrides from below \u2014 must be on a single comma-separated line.<\/p>\n\n\n\nAlso: do not include type precision inside MODIFICAR_TIPO<\/code> (write num\u00e9rico<\/code>, no numeric(10,2)<\/code>).<\/p>\n\n\n\nThe comma inside parentheses is treated as a delimiter and breaks parsing.<\/p>\n\n\n\n
Problem 4: The IS JSON Check Constraint Uses Oracle Syntax<\/h3>\n\n\n\n ora2pg exports the products_json_c<\/code> constraint as:<\/p>\n\n\n\nCONSTRAINT products_json_c CHECK (product_details IS JSON)\n<\/pre><\/div>\n\n\nPostgreSQL has no IS JSON<\/code> predicate. Loading the table DDL fails immediately with a syntax error.<\/p>\n\n\n\nThe constraint is also redundant. JSONB<\/code> in PostgreSQL validates JSON on every insert. Any row that fails JSON validation is rejected before it reaches storage. The IS JSON<\/code> constraint is doing nothing that the column type does not already do.<\/p>\n\n\n\nThe fix: delete the constraint line from the exported DDL before loading.<\/p>\n\n\n
\nsed -i '\/products_json_c\/d' \/home\/fernando\/ora2pg-co\/output\/CO_tables_fixed.sql\n<\/pre><\/div>\n\n\nVerificar<\/p>\n\n\n
\ngrep 'products_json_c' \/home\/fernando\/ora2pg-co\/output\/CO_tables_fixed.sql\n# Expected: no output\n<\/pre><\/div>\n\n\nThe TEST report will show a CHECK CONSTRAINTS DIFF for the products<\/code> table \u2014 one constraint in Oracle, zero in PostgreSQL. This is expected and correct.<\/p>\n\n\n\nProblem 5: Three Views Use Oracle-Specific SQL Functions<\/h3>\n\n\n\n CO has four views. One (product_orders<\/code>) uses standard SQL and converts without changes. Three use functions that have no direct PostgreSQL equivalent:<\/p>\n\n\n\ncustomer_order_products<\/code> \u2014 LISTAGG with ON OVERFLOW<\/h4>\n\n\n\nOracle LISTAGG(expr, separator ON OVERFLOW TRUNCATE '...' WITH COUNT)<\/code> concatenates values into a delimited string with overflow handling. <\/p>\n\n\n\nPostgreSQL STRING_AGG(expr, separator ORDER BY ...)<\/code> is the equivalent \u2014 no overflow option.<\/p>\n\n\n\n-- Oracle\nLISTAGG(p.product_name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)\n WITHIN GROUP (ORDER BY oi.line_item_id) items\n\n-- PostgreSQL\nSTRING_AGG(p.product_name, ', ' ORDER BY oi.line_item_id) items\n<\/pre><\/div>\n\n\nstore_orders<\/code> \u2014 GROUPING_ID<\/h4>\n\n\n\nOracle GROUPING_ID(col1, col2)<\/code> returns an integer bit vector encoding which columns are aggregated in a GROUPING SETS<\/code> query. <\/p>\n\n\n\nPostgreSQL GROUPING(col1, col2)<\/code> does exactly the same thing \u2014 the function name differs, the semantics are identical. <\/p>\n\n\n\nThe replacement is one-for-one.<\/p>\n\n\n
\n-- Oracle\nCASE grouping_id(store_name, order_status)\n WHEN 1 THEN 'STORE TOTAL'\n ...\n\n-- PostgreSQL\nCASE grouping(s.store_name, o.order_status)\n WHEN 1 THEN 'STORE TOTAL'\n ...\n<\/pre><\/div>\n\n\nproduct_reviews<\/code> \u2014 JSON_TABLE<\/h4>\n\n\n\nOracle JSON_TABLE<\/code> expands a JSON array into rows using a relational projection.<\/p>\n\n\n\nPostgreSQL has no JSON_TABLE<\/code> equivalent before version 15 (it was added in PostgreSQL 15 as part of the SQL\/JSON standard, but with different syntax). <\/p>\n\n\n\nFor PostgreSQL 14 and earlier \u2014 or for consistent cross-version behaviour \u2014 rewrite using jsonb_array_elements<\/code>:<\/p>\n\n\n\n-- Oracle\nFROM products p,\n JSON_TABLE(p.product_details, '$'\n COLUMNS (NESTED PATH '$.reviews[*]'\n COLUMNS (rating INTEGER PATH '$.rating',\n review VARCHAR2(4000) PATH '$.review'))) r\n\n-- PostgreSQL\nFROM products p,\n jsonb_array_elements(p.product_details->'reviews') AS review_row\n<\/pre><\/div>\n\n\nIn the PostgreSQL version, each JSON array element is exposed as a jsonb<\/code> value named review_row<\/code>. <\/p>\n\n\n\nColumn values are extracted with the ->><\/code> operator: review_row->>'rating'<\/code> y review_row->>'review'<\/code>. <\/p>\n\n\n\nThe cast ::INTEGER<\/code> on the rating field is required because ->><\/code> always returns texto<\/code>.<\/p>\n\n\n\nThis rewrite depends on product_details<\/code> being JSONB<\/code> \u2014 if the column is BYTEA<\/code> (Problem 2 not fixed), the view fails immediately.<\/p>\n\n\n\nEjecutando la Migraci\u00f3n: Cada Comando en Orden<\/h2>\n\n\n\nMedio ambiente<\/h3>\n\n\n\n Dos m\u00e1quinas virtuales en la misma red. srv1<\/code> (192.168.0.180) runs Oracle 19c with the CO schema in PDB pdb1<\/code>. srv2<\/code> (192.168.0.181) ejecuta Ubuntu con PostgreSQL 18 y ora2pg instalado. Todos los comandos a continuaci\u00f3n se ejecutan en srv2<\/code> a menos que se indique lo contrario.<\/p>\n\n\n\nStep 1 \u2014 Gather Oracle statistics<\/h3>\n\n\n\n Run on srv1<\/code> before generating the report. ora2pg's effort estimates use Oracle's stored statistics \u2014 stale stats produce inaccurate counts.<\/p>\n\n\n\n-- On Oracle (srv1)\nBEGIN\n DBMS_STATS.GATHER_SCHEMA_STATS('CO');\n DBMS_STATS.GATHER_DICTIONARY_STATS;\nEND;\n\/\n<\/pre><\/div>\n\n\nPaso 2: cree el directorio de salida<\/h3>\n\n\n\nmkdir -p \/home\/fernando\/ora2pg-co\/output\n<\/pre><\/div>\n\n\nStep 3 \u2014 Base ora2pg.conf<\/h3>\n\n\n\n# \/etc\/ora2pg\/ora2pg.conf\nORACLE_DSN dbi:Oracle:host=192.168.0.180;service_name=pdb1;port=1521\nORACLE_USER system\nORACLE_PWD <system password>\nSCHEMA CO\nEXPORT_SCHEMA 1\nCREATE_SCHEMA 1\nOUTPUT_DIR \/home\/fernando\/ora2pg-co\/output\n<\/pre><\/div>\n\n\nUtilice sistema<\/code>, no co<\/code> \u2014 la exportaci\u00f3n de COPIA dice v$base de datos<\/code>, which requires DBA privilege.<\/p>\n\n\n\nPaso 4: Generar el informe de evaluaci\u00f3n<\/h3>\n\n\n\nora2pg -t SHOW_REPORT --estimate_cost --dump_as_html \\\n > \/home\/fernando\/ora2pg-co\/output\/report-co.html\n<\/pre><\/div>\n\n\nOpen the HTML in a browser. CO should score A-3<\/strong>. The cost drivers are the three non-portable views.<\/p>\n\n\n\nPaso 5 \u2014 Analizar tipos de columna<\/h3>\n\n\n\nora2pg -t SHOW_COLUMN > \/home\/fernando\/ora2pg-co\/output\/columns-co.txt\n<\/pre><\/div>\n\n\nKey columns to check:<\/p>\n\n\n\nColumn<\/th> ora2pg default<\/th> Correct type<\/th> Reason<\/th><\/tr><\/thead> PRODUCTS.UNIT_PRICE<\/code><\/td>doble precisi\u00f3n<\/code><\/td>num\u00e9rico<\/code><\/td>Monetary \u2014 floating point causes rounding errors<\/td><\/tr> ORDER_ITEMS.UNIT_PRICE<\/code><\/td>doble precisi\u00f3n<\/code><\/td>num\u00e9rico<\/code><\/td>