Information command in SQLcl gives ORA-20005 error

Wouter Breeuwsma

Hi all,

I am investigating the new SQLcl tool with all the great features (version sqlcl-4.2.0.16.049.0843).

When I try to get information from the employees table in the HR schema I get some errors. I have entered:

info hr.employees;

and get:

ORA-20005: ORU-10034: context argument must be 1 or 2 or 3 or 4 or 5 or 6 or 7

ORA-06512: in "SYS.DBMS_UTILITY", regel 112

ORA-06512: in regel 1

This occurs on a 10g R2 EE database on a Virtual Box machine. I get the same error with SQL Developer 4.1.3.20.

What can be the reason for this?

On a 11XE database it is working with SQL Developer 4.1.3.20.

Regards, Wouter

Dear DBA Frank

Hello

I confirm that both the info and info+ commands are not compatible with version 10g of the database.  Ever since these new commands came out, I have tested them on 11g, 12c, and 10g, and while they work fine in 11g and 12c, they always give that ORU-10034 error in 10g.  Note that both can be run in SQLDeveloper proper, on top of SQLCL.

thatJeffSmith-Oracle

No formal support for 10g, we're using DBMS_UTILITY to 'resolve' the object you're trying to INFO - and that package apparently isn't on 10gR2

Wouter Breeuwsma

Hi Jeff,

Thanks for your reply.

I checked the package DBMS_UTILITY in both versions and there is a difference in implementation of the NAME_RESOLVE as far as I can see in the package header.

In another discussion (4.1.0.17.29 mess with security) I found the following check, which indeed fails in the 10gR2 version.

DECLARE

  l_schema     VARCHAR2 (128) ;

  l_part1      VARCHAR2 (128) ;

  l_part2      VARCHAR2 (128) ;

  l_dblink     VARCHAR2 (128) ;

  l_part1_type NUMBER;

  l_objid      NUMBER;

BEGIN

  DBMS_UTILITY.NAME_RESOLVE (name => 'dba_xml_schemas', context => 0, schema => l_schema, part1 => l_part1, part2 =>

  l_part2, dblink => l_dblink, part1_type => l_part1_type, object_number => l_objid) ;

END;

Error report -

ORA-20005: ORU-10034: context argument must be 1 or 2 or 3 or 4 or 5 or 6 or 7

ORA-06512: in "SYS.DBMS_UTILITY", regel 112

ORA-06512: in regel 9

Apparently in the 10gR2 version the value of context resolves to 0, which in turn is not accepted.

Regards, Wouter

thatJeffSmith-Oracle

Thanks for investigating that.

I'll pass it along to the devs, but we're still not officially supporting Oracle Database 10g.

SQLcl is a new product. How many years and versions of the Database do we go back? We've decided not to go back to 11+ years...

Wouter Breeuwsma

I can understand that, and it is fine. It just wasn't obvious.

Thanks, Wouter

Dear DBA Frank

Hello

I confirm that both the info and info+ commands are not compatible with version 10g of the database.  Ever since these new commands came out, I have tested them on 11g, 12c, and 10g, and while they work fine in 11g and 12c, they always give that ORU-10034 error in 10g.  Note that both can be run in SQLDeveloper proper, on top of SQLCL.

Wouter Breeuwsma

Thanks Frank for your confirmation.

Kris Rice-Oracle

The 'bug' here is that we use that name_resolve to get the underlying object and there is no object found for the 'types' that name_resolve knows about. You can see the context and the integers it supports in the doc

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_util.htm#i997271

vs. in 10.2 there's less types supported

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_util.htm#i997271