BUGReport: Duplicated sources with 12c Multitenant

[Emoracle]

I'm working with version 10.0.5.1710 against a 12C Multitenant database with one PDB and the root-container.
When I look at the source of the shared packages I'm seeing twins. E.g. with UTL_MATCH:

CREATE OR REPLACE PACKAGE utl_match IS
PACKAGE utl_match IS
FUNCTION edit_distance(s1 IN VARCHAR2, s2 IN VARCHAR2)
FUNCTION edit_distance(s1 IN VARCHAR2, s2 IN VARCHAR2)
RETURN pls_integer;
RETURN pls_integer;
PRAGMA interface(c, edit_distance);
PRAGMA interface(c, edit_distance);

....

I guess you're are doing something like
select *
from dba_source
where owner = 'SYS'
and name = 'UTL_MATCH'
and type = 'PACKAGE'
order by line

but with 12c this only works within CDB$ROOT. You'll have to include the current container-id.

select *
from dba_source
where owner = 'SYS'
and name = 'UTL_MATCH'
and type = 'PACKAGE'
and origin_con_id = SYS_CONTEXT ('USERENV', 'CON_ID')
order by line

 

[Marco Kalter]

We'll check it out.

 

[William Robertson]

Just came across this exact issue today in PL/SQL Dev 14.0.6.1988.

I expect it will start to appear more often with growing take-up of cloud instance services and Oracle phasing out non-CDB installations.

 

[Marco Kalter]

PL/SQL Developer 14.0 does include the container-id in the where clause. To obtain some more diagnostic information, can you modify the PL/SQL Developer shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer 14\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the %APPDATA%\PLSQL Developer 14 directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer 14).

 

[William Robertson]

The issue came up when looking at MDSYS.SDO_UTIL. The SQL from the trace is

00:01:26.102 TOracleQuery 
[CODE=SQL]
select text from sys.dba_source
where  owner = 'MDSYS'
and    name  = 'SDO_UTIL'
and    type  = 'PACKAGE'
and    origin_con_id in (1, sys_context('userenv', 'con_id'))
order by line;

B645B40 Start Query.Execute
SQL = select text from sys.dba_source
where owner = wner
and name = :name
and type = :type
and origin_con_id in (1, sys_context('userenv', 'con_id'))
order by line
:OWNER = MDSYS
:NAME = SDO_GEOMETRY
:TYPE = TYPE
[/code]

which evaluates to
___SNIPPET___