Repository navigation
Install error "ORA-38818: illegal reference to editioned object MYUSER.UT_EXECUTABLE" #1161
Description
Describe the bug
Error installing utPLSQL v3.1.10 to my own schema.
Provide version info
utPLSQL v3.1.10
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
To Reproduce
Steps to reproduce the behavior:
- Download utPLSQL.zip v3.1.10 and unzip file.
cd utPLSQL/source/- Start
sqlplus MYUSER/MYUSERPWD @install.sql MYUSER - See error:
.....
create table ut_suite_cache
*
ERROR at line 1:
ORA-38818: illegal reference to editioned object XX_APEX.UT_EXECUTABLE
MYUSER schema definition:
SELECT editions_enabled
FROM dba_users
WHERE username = 'MYUSER'
---
Y
Activity
jgebal commentedon Nov 8, 2021
Hi @jashka34
utPLSQL framework cannot be installed un edition-enabled schema.
I strongly suggest installing utPLSQL in separate schema (database-wide) that is not edition-enabled.
That way, all users of DB can use the framework.
Are you trying to install utPLSQL in your "private" schema?
jashka34 commentedon Nov 9, 2021
@jgebal
I guessed about it. Thank you for confirming my suspicions.
But why then does the documentation not say a word about it (for example)? Probably need to create a issue about this? :)
Unfortunately, I do not have DBA rights. I only own one DB schema and tried to install in this schema.
jgebal commentedon Nov 9, 2021
Hi @jashka34
I think there are two options.
It was not mentioned in documentation as we didn't see such use-cases before and utPLSQL was not tested for this scenario.
jashka34 commentedon Nov 9, 2021
@jgebal
In my opinion, this is the best option. I'm sure it will be convenient for everyone. May be in a future versions?
Probably makes sense to do it for the current version.
In the meantime, I am negotiating with the my admins to create a addtional scheme for utPLSQL. They promise to help. :)
jgebal commentedon Nov 11, 2021
The workaround for now would be for you to change all the source code and add:
noneditionableto all packages/types/triggers/synonyms in utPLSQL installation.In order for us to do this in utPLSQL itself, we would need to drop support for Oracle 11g or would need to provide two separate sets of source code (with and without
noneditionablekeyword).The
noneditionablewas introduced in Oracle 12 and so we cannot simply add it without major complications or without dropping support.I have tested it locally and it worked like a charm on all DBs except 11g.
So this is something to consider, think about and discuss before making a decision.
The changes that are needed can be expressed with this regexp:
change from:
^(create *(or *replace *)?)((package|type|trigger|synonym) *(body)?)change to:
$1 noneditionable $3Where $1 represents the expression in first bracket and $3 in third bracket
i used it for global search and replace in source code using IntelliJ IDE.
Other editors might have different regexp support and other substitution (replace) syntax.
Hope this helps a bit for now.
jashka34 commentedon Nov 12, 2021
@jgebal
Thank you very much! I will definitely use this option next time.
At the moment my admins have created noneditionable schema for me. I have installed the framework and it works fine!
jeffreykemp commentedon May 24, 2022
This would be my preference as well.
jgebal commentedon May 24, 2022
Let me see if I can make it happen.
I think I had a branch open for this some time ago.
jgebal commentedon Nov 1, 2024
@amithdas99
This could be a good issue to start with.
As far as I remember it should be a pretty simple change in utPLSQL objects to make them explicitly non-editionable.
As for testing, the installation script for our github actions pipeline should create schema that is editionable and the installation of utPLSQL should still be successful - that would be a sufficient validation I think.
amithdas99 commentedon Nov 2, 2024
@jgebal Sure, I'll be checking this. Thank you!
amithdas99 commentedon Nov 2, 2024
@jgebal I actually have 11g database only installed in my system so when I try to add the 'noneditionable' clause to the 'create or replace package/procedure' I am getting an error. So would you suggest I download a database like 12g or above and install utlplsql and try to add this clause on all objects. Would that be a correct approach to this issue.
jgebal commentedon Nov 3, 2024
I use docker to run different database versions on my local machine eithout need to worry about the versions conflicts etc.
So it would be my recommendation to start like this.
The benefit is the ease to reset ( wipe clean ) the database.
Here are instructions on how to run free Iracle DB docker containers.
https://github.com/gvenzl/oci-oracle-xe
amithdas99 commentedon Nov 4, 2024
@jgebal thank you so much! Let me try this.
utPLSQL can be installed in editioned schema.