Install error "ORA-38818: illegal reference to editioned object MYUSER.UT_EXECUTABLE"

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:

1. Download utPLSQL.zip v3.1.10 and unzip file.
2. cd utPLSQL/source/
3. Start sqlplus MYUSER/MYUSERPWD @install.sql MYUSER
4. 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

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?

@jgebal

utPLSQL framework cannot be installed un edition-enabled schema. I strongly suggest installing utPLSQL in separate schema (database-wide) that is not edition-enabled.

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? :)

Are you trying to install utPLSQL in your "private" schema?

Unfortunately, I do not have DBA rights. I only own one DB schema and tried to install in this schema.

Hi @jashka34 I think there are two options.

• I can try to make the utPLSQL framework installable in edition-enabled schema - no need to update documentation
• I can update documentation to say it's not possible It was not mentioned in documentation as we didn't see such use-cases before and utPLSQL was not tested for this scenario.

@jgebal

I can try to make the utPLSQL framework installable in edition-enabled schema - no need to update documentation

In my opinion, this is the best option. I'm sure it will be convenient for everyone. May be in a future versions?

I can update documentation to say it's not possible

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

The workaround for now would be for you to change all the source code and add: noneditionable to 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 noneditionable keyword). The noneditionable was 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 $3 Where $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.

@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!

• I can try to make the utPLSQL framework installable in edition-enabled schema - no need to update documentation

This would be my preference as well.

Let me see if I can make it happen. I think I had a branch open for this some time ago.