ORA-31145 – “Duplicate listener, %s, found in resource configuration”

Today I had an error on my test environment which surprised me…initially…

While I was testing a JSON_TABLE on a JSON document which I put in the XDB Repository environment via FTP, it signaled this ORA-31145 error.

$> oerr ORA 31145

31145, 00000, "Duplicate listener, %s, found in resource configuration"
//  *Cause:  Duplicate listener source was found in resource configuration.
//  *Action: Remove duplicates and resubmit statement.
//

Apparently it looks like I somehow created two XDB Event listeners associated, via a resource configuration file, on the same XDB repository resource (=file/folder). In this case on my JSON file “content.json” in folder /public/json, I was experimenting with.

The statement I attempted to execute:

select xdburitype('/public/json/content.json').getclob() 
       as content
  from dual;

The function XDBURITYPE (part of the “UriFactory” family) gets the needed content straight out the XDB Repository from with the database. Then I remembered that I had been playing with XDB Repository events, a while ago, based on my code in “HOWTO: Using the Oracle XMLDB Repository to Automatically Shred Windows Office Documents“.

For fun I wanted to, based on the mentioned post, debug/log all events going on in the XDB Repository via using package HANDLE_XDB_EVENTS.

I created a XDB resource via:

--
-- Map all things in the repository via "DBMS_RESCONFIG.addRepositoryResConfig"
--
-- This needs the event handler PL/SQL package to be accessable for all database users
--
GRANT EXECUTE on XDBA.HANDLE_XDB_EVENTS to PUBLIC;
-- 
-- Create new resource to log rendering of all xdb repository actions
--
DECLARE
  b BOOLEAN := FALSE;
BEGIN
  b := DBMS_XDB.createResource(
         '/xdb/res/RenderAll.xml',
         '
            
              
                XDB Event Logging - Render All
                XDBA
                HANDLE_XDB_EVENTS
                PL/SQL
                
                 
                
              
            
            
              
                /xdb/res/UploadHandling.xml
              
            
          ');
END;
/
--
commit;
--
-- Add the resource to XDB repository listener events
--
BEGIN
  DBMS_RESCONFIG.addRepositoryResConfig('/xdb/res/RenderAll.xml', NULL);
END;
/
--
commit;
--

This now got me in problems due to that I had forgotten that I also had the same package linked to /public via a different resource file and all under / via the DBMS_RESCONFIG.addRepositoryResConfig method.

This is shown via, eg., the following statements

--
SELECT xmlserialize(CONTENT dbms_resconfig.getRepositoryResConfig(0) 
       as CLOB indent size=1) as xmlcontent 
FROM dual;
--

 
  
   XDB Event Logging - Render All
   XDBA
   HANDLE_XDB_EVENTS
   PL/SQL
   
    
   
  
 
 
  
   /xdb/res/UploadHandling.xml
  
 

--
SELECT xmlserialize(CONTENT dbms_resconfig.getlisteners('/public/json/content.json') 
       as CLOB indent size=1) as xmlcontent 
FROM dual;
--

 
  XDB Event Logging - Render All
  XDBA
  HANDLE_XDB_EVENTS
  PL/SQL
  
   
  
 
 
  Category application
  XDBA
  HANDLE_XDB_EVENTS
  PL/SQL
  
   
   
   
  
 

--
SELECT column_value as "Local XDB Resource File" 
FROM TABLE(dbms_resconfig.getresconfigpaths('/public/json/content.json'));
--
Local XDB Resource File
---------------------------
/xdb/res/UploadHandling.xml
--

As you can notice…there is a double entry that triggers my XDB_EVENT based package “HANDLE_XDB_EVENTS” via /xdb/res/UploadHandling.xml.

I solved the issue for now (I don’t want yet my default logging experiments to disappear) via

--
execute dbms_resconfig.deleteresconfig('/public/json/content.json', 0);
commit;
--
SELECT column_value as "Local XDB Resource File" 
FROM TABLE(dbms_resconfig.getresconfigpaths('/public/json/content.json'));
--
no rows selected.
--

If I now make another attempt via my initial statement, I get the JSON output…

--
select xdburitype('/public/json/content.json').getclob() 
       as content
  from dual;
--
"[
  {
    "_id": "5524f7142530caad9a9bf1d9",
    "index": 0,
    "guid": "da5c2b59-d3ef-4ec9-ad6a-726de68ee0bb",
    "isActive": true,
    "balance": "$3,691.06",
    "picture": "http://placehold.it/32x32",
    "age": 33,
    "eyeColor": "brown",
    "name": "Anita Kane",
    "gender": "female",
...
....
....
...
]"

and…

Yep, and also still my experimental output via my XDB Event logging package…

😉