search

utPLSQL / utPLSQL

Testing Framework for PL/SQL
https://www.utplsql.org/
Apache License 2.0
555 stars 184 forks source link

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

Open jashka34 opened 2 years ago

jashka34 commented 2 years ago

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
jgebal commented 2 years ago

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 commented 2 years ago

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

jgebal commented 2 years ago

Hi @jashka34 I think there are two options.

jashka34 commented 2 years ago

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

jgebal commented 2 years ago

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.

jashka34 commented 2 years ago

@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 commented 2 years ago
  • 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.

jgebal commented 2 years ago

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