0

I just created a docker image of Oracle 11g ee following this tutorial https://vitalflux.com/how-to-install-oracle-11g-ee-on-docker/ and loaded some tables inside.

The process worked fine and no errors happens during the load of a huge sql file (10K+) with sqlplus. But on the last step the process breaks. I could pinpoint the issue to the following line for recompilation:

@?/rdbms/admin/utlrp.sql

The error received is the following:

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.

   p_schemaname  dbms_id;
                 *
ERROR at line 5:
ORA-06550: line 5, column 18:
PLS-00201: identifier 'DBMS_ID' must be declared
ORA-06550: line 5, column 18:
PL/SQL: Item ignored
ORA-06550: line 6, column 18:
PLS-00201: identifier 'DBMS_ID' must be declared
ORA-06550: line 6, column 18:
PL/SQL: Item ignored
ORA-06550: line 62, column 34:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 62, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 65, column 33:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 64, column 12:
PL/SQL: Statement ignored



Function dropped.

I tried to find an answer on google but the best I could find is that there are some invalid objects. Which I couldn't find any.

SQL> SELECT owner,
  2         object_type,
  3         object_name,
  4         status
  5  FROM   dba_objects
  6  WHERE  status = 'INVALID'
  7  ORDER BY owner, object_type, object_name;

no rows selected

I am executing the process as sys with sysdba. Maybe the re-compilation is not necessary but I cannot remove it from the script.

A week ago I tried to do the same thing on a "real" Oracle 11g installation without docker. There the script worked. So I assume that there is something wrong with the container but I am not sure what exactly.

Did anybody encounter this issue before, do you have any ideas?

Thanks in advance

Version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
2
  • 11.2.0.1 is seriously bugged. Use 11.2.0.4. Better yet, go for 18c/19c version. Feb 9, 2019 at 18:58
  • @MichaelKutz, would love to :) I have already have a 12c image and it works. Unfortunately the customer has 11 ...
    – Oldfighter
    Feb 10, 2019 at 12:25

1 Answer 1

1

DBMS_ID is a subtype in DBMS_STANDARD starting with version 12c.

DBMS_ID does not exist in 11g.

You have a messed up environment with 12c scripts and a 11g database.

The code that fails is in $ORACLE_HOME/rdbms/admin/reenable_indexes.sql, which is called by $ORACLE_HOME/rdbms/admin/utlprp.sql, which is called by $ORACLE_HOME/rdbms/admin/utlrp.sql.

This is an environmental issue but we do not know what you have done so far. In a properly created and configured environment this would not happen.

If you want to use 11g, look for a 11g image. There must have been someone desperate enough to create one. Like this one: docker-oracle-ee-11g. No, I haven't tested it.

4
  • Thanks for the answers. The explanation really helps. I tried the same with the image you linked in your answer. Unfortunately I experience the same error message with the other image. It seems like all db objects were correctly created. Do you maybe have an idea what else could be wrong?
    – Oldfighter
    Feb 10, 2019 at 12:23
  • @Oldfighter I tested docker container from the link provided by Balazs it works fine.Invalidated an object and ran recompilation without any errors.
    – user153556
    Feb 10, 2019 at 18:43
  • @Oldfighter Let me guess. You connect to the 11g database remotely from somewhere else, where a 12c database is installed, and you executed @?/rdbms/admin/utlrp.sql in that session, and not locally inside the container. This syntax calls scripts local to the client, not the server, so the scripts are executed from the local 12c ORACLE_HOME, and not the 11g ORACLE_HOME installed in the container. If this the case, I suggest using DBMS_UTILITY.COMPILE_SCHEMA on the schema you work with, instead of running utlrp. docs.oracle.com/cd/E18283_01/appdev.112/e16760/… Feb 10, 2019 at 18:47
  • @BalazsPapp, that was it. I tested it and it works. Thank you very much, this saved me so much time and effort!
    – Oldfighter
    Feb 10, 2019 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.