Strange error trying to import with Ora2PG
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
On 10/3/23 10:32, Johnson, Bruce E - (bjohnson) wrote:
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:
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'm betting that every time you run import_all.sh it rewrites the
scripts. Pretty sure if you look at the file the creation script will
have been rewritten to its original from.
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 GroupInstitutions do not have opinions, merely customs
--
Adrian Klaver
adrian.klaver@aklaver.com
That’s not it. export_schema.sh writes the scripts, and then emits a ora2pg command to pull in the data (I've managed to do this a dozen times so far working out how to do things. :-)
The dates on the files in the sources directory are consistent with that, and don’t change when I run the script.
-rw-rw-r-- 1 johnson johnson 872 Sep 27 22:02 AHSC_SCHEDULE_ROUNDED_view.sql
-rw-rw-r-- 1 johnson johnson 817 Sep 27 22:02 COP_COLLEGE_HOUR_view.sql
-rw-rw-r-- 1 johnson johnson 628 Oct 3 17:06 RESERVER_VISIBLE_RESTRICTION_view.sql
-rw-rw-r-- 1 johnson johnson 515 Sep 27 22:02 RESOURCEAFFIL_WITHCODE_view.sql
-rw-rw-r-- 1 johnson johnson 535 Sep 27 22:02 UA_COLLEGE_LOOKUP_view.sql
On Oct 3, 2023, at 2:27 PM, Adrian Klaver <adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>> wrote:
External Email
On 10/3/23 10:32, Johnson, Bruce E - (bjohnson) wrote:
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:
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'm betting that every time you run import_all.sh it rewrites the scripts. Pretty sure if you look at the file the creation script will have been rewritten to its original from.
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
--
Adrian Klaver
adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
On 10/4/23 06:24, Johnson, Bruce E - (bjohnson) wrote:
That’s not it. export_schema.sh writes the scripts, and then emits a
ora2pg command to pull in the data (I've managed to do this a dozen
times so far working out how to do things. :-)The dates on the files in the sources directory are consistent with
that, and don’t change when I run the script.-rw-rw-r-- 1 johnson johnson 872 Sep 27 22:02 AHSC_SCHEDULE_ROUNDED_view.sql
-rw-rw-r-- 1 johnson johnson 817 Sep 27 22:02 COP_COLLEGE_HOUR_view.sql
-rw-rw-r-- 1 johnson johnson 628 Oct 3 17:06
RESERVER_VISIBLE_RESTRICTION_view.sql
-rw-rw-r-- 1 johnson johnson 515 Sep 27 22:02
RESOURCEAFFIL_WITHCODE_view.sql
-rw-rw-r-- 1 johnson johnson 535 Sep 27 22:02 UA_COLLEGE_LOOKUP_view.sql
Alright.
In your previous post you said "I tested the view creation with that
code interactively, and it worked."
Does that mean you ran:
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’;
in a psql session by itself?
What happens if you do?:
psql ... -f RESERVER_VISIBLE_RESTRICTION_view.sql
When you run the import_all.sh does it show where it is pulling the
scripts from?
If you search the source file directory for
"reserver_visible_restriction" is there another file that contains it?
On Oct 3, 2023, at 2:27 PM, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:External Email
On 10/3/23 10:32, Johnson, Bruce E - (bjohnson) wrote:
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: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'm betting that every time you run import_all.sh it rewrites the
scripts. Pretty sure if you look at the file the creation script will
have been rewritten to its original from.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--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology GroupInstitutions do not have opinions, merely customs
--
Adrian Klaver
adrian.klaver@aklaver.com
On Oct 4, 2023, at 8:38 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
If you search the source file directory for "reserver_visible_restriction" is there another file that contains it?
Sigh…that was it…Thank you.
There are two ‘views’ directories, one under ’sources/views’ and the other under /schema/views. Clearly the script is running the other one.
~/oracle_migration$ tree calendar3
calendar3
├── config
│ ├── ora2pg.conf
│ └── ora2pg.conf.orig
SNIP
├── schema
│ ├── dblinks
│
SNIP
│ └── views
│ ├── AHSC_SCHEDULE_ROUNDED_view.sql
│ ├── COP_COLLEGE_HOUR_view.sql
│ ├── RESERVER_VISIBLE_RESTRICTION_view.sql
│ ├── RESOURCEAFFIL_WITHCODE_view.sql
│ ├── UA_COLLEGE_LOOKUP_view.sql
│ └── view.sql
└── sources
SNIP
└── views
├── AHSC_SCHEDULE_ROUNDED_view.sql
├── COP_COLLEGE_HOUR_view.sql
├── RESERVER_VISIBLE_RESTRICTION_view.sql
├── RESOURCEAFFIL_WITHCODE_view.sql
├── UA_COLLEGE_LOOKUP_view.sql
└── view.sql
29 directories, 127 files
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs