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',
         '<resconfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd"
                     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                     xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd
                                         http://xmlns.oracle.com/xdb/XDBResConfig.xsd">
            <event -listeners>
              <listener>
                <description>XDB Event Logging - Render All</description>
                <schema>XDBA</schema>
                <source />HANDLE_XDB_EVENTS
                <language>PL/SQL</language>
                <events>
                 <render></render>
                </events>
              </listener>
            </event>
            <defaultchildconfig>
              <configuration>
                <path>/xdb/res/UploadHandling.xml</path>
              </configuration>
            </defaultchildconfig>
          </resconfig>');
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;
--
<resconfig xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd
http://xmlns.oracle.com/xdb/XDBResConfig.xsd" enable="true">
 <event -listeners set-invoker="false">
  <listener>
   <description>XDB Event Logging - Render All</description>
   <schema>XDBA</schema>
   <SOURCE />HANDLE_XDB_EVENTS
   <language>PL/SQL</language>
   <events>
    <render></render>
   </events>
  </listener>
 </event>
 <defaultchildconfig>
  <configuration>
   <path>/xdb/res/UploadHandling.xml</path>
  </configuration>
 </defaultchildconfig>
</resconfig>
--
SELECT xmlserialize(CONTENT dbms_resconfig.getlisteners('/public/json/content.json') 
       AS CLOB indent SIZE=1) AS xmlcontent 
FROM dual;
--
<event -listeners xmlns="http://xmlns.oracle.com/xdb/XDBResConfig.xsd" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResConfig.xsd 
http://xmlns.oracle.com/xdb/XDBResConfig.xsd">
 <listener>
  <description>XDB Event Logging - Render All</description>
  <schema>XDBA</schema>
  <SOURCE />HANDLE_XDB_EVENTS
  <language>PL/SQL</language>
  <events>
   <render></render>
  </events>
 </listener>
 <listener>
  <description>Category application</description>
  <schema>XDBA</schema>
  <SOURCE />HANDLE_XDB_EVENTS
  <language>PL/SQL</language>
  <events>
   <post -LinkIn></post>
   <post -Update></post>
   <render></render>
  </events>
 </listener>
</event>
--
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…
xdb_event_log_table

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

😉

Marco Gralike Written by: