I am trying to import an oracle schema with ora2pg and running into an odd error when the import_all.sh script gets to a specific view.
The error is: psql:schema/views/RESERVER_VISIBLE_RESTRICTION_view.sql:10: ERROR: operator does not exist: character = integer LINE 1: ...r.resource_id = rg.resource_id and r.requester_vis_only = 1; A simple enough fix; Oracle happily accepts a number-like string as a number, and Postgres wants a string delimited, so I went into the ./sources/views folder and edited the view creation to add the required single quotes: -- Generated by Ora2Pg, the Oracle database Schema converter, version 24.0 -- Copyright 2000-2023 Gilles DAROLD. All rights reserved. -- DATASOURCE: dbi:Oracle:host=dhboracle2.pharmacy.arizona.edu<http://dhboracle2.pharmacy.arizona.edu>;sid=phmweb2;port=1521 SET client_encoding TO 'UTF8'; SET search_path = calendar3,oracle,public; \set ON_ERROR_STOP ON CREATE OR REPLACE VIEW reserver_visible_restriction (resource_id, resourcegroup_id, affil_id) AS select r.resource_id, rg.resourcegroup_id, a.affil_id FROM resources r, resourcegroupaffil rg, resourceaffil a where r.resource_id = a.resource_id and r.resource_id = rg.resource_id and r.requester_vis_only = '1’; I tested the view creation with that code interactively, and it worked. However when I re-run the import_all.sh script it errors out at the same place with the same error (in fact the pasted error code is from running the script AFTER I fixed it). The sql isn’t being cached anywhere in the system is it? I know that I modified the correct file. It happens even if I drop the database and recreate it. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
