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.
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