OraFAQ Forum: SQL & PL/SQL » Could you please explain how SYS

Home » SQL & PL/SQL » SQL & PL/SQL » Could you please explain how SYS_DBURIGEN() works? (18)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Could you please explain how SYS_DBURIGEN() works? [message #690290]

Sat, 08 March 2025 15:22

Darth Waiter
Messages: 84
Registered: October 2020

Member

In an attempt to extract trigger bodies with a single select, I scavenged bits and pieces of information from all over the Internet and came up with this query that works although very slowly:

select cast(sys_dburigen(table_name, trigger_name, TRIGGER_BODY, 'text()').getclob() as varchar(4000)) AS TRIGGER_BODY
from ALL_TRIGGERS

I read https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_DBURIGEN.html but did not understand why the 1st, 2nd, 3d, and 4th parameters are required. Without the 4th, 'text()', I get XML. Without the 1st and 2nd, I get

Quote:

ORA-19003: Missing XML root element name

Am I doing it the optimal way, or is there a better way to extract trigger bodies (and potentially stored procedure, function, view, and other units of code, in a single select? Yes, it has to be a single SQL statement.

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690291 is a reply to message #690290]

Sat, 08 March 2025 15:40

Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I tried your query in 11.2 and it works quite well.

Another way is the good old "dbms_metadata.get_ddl":

select DBMS_METADATA.GET_DDL ('TRIGGER',trigger_name,owner) from all_triggers;

much faster.

[Updated on: Sat, 08 March 2025 15:42]

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690292 is a reply to message #690291]

Sat, 08 March 2025 15:42

Darth Waiter
Messages: 84
Registered: October 2020

Member

Can it be used in a single SQL statement?

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690293 is a reply to message #690292]

Sat, 08 March 2025 15:43

Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't understand your question.
Test the query and you will see it gives all the triggers definition.

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690294 is a reply to message #690293]

Sat, 08 March 2025 15:51

Darth Waiter
Messages: 84
Registered: October 2020

Member

Output of my query:

BEGIN\n  IF :new.hostid IS NULL THEN\n    :new.hostid := VNCR_SEQUENCE.NEXTVAL;\n  END IF;\nEND;
dbms_gsm_pooladmin.catRollback(:new.request, :new.old_instances);
dbms_gsm_pooladmin.requestDelete(:old.change_seq#, :old.request, :old.status);
dbms_gsm_pooladmin.requestDone(:old.request, :new.status);

Output of your query:

<Clob>
<Clob>
<Clob>

Even after I cast to varchar2(4000) it is still the same. I need varchar output from it.

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690295 is a reply to message #690294]

Sun, 09 March 2025 00:48

Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What SQL tool do you use? It seems it does not support CLOB.
I use SQL*Plus and it works well.

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690296 is a reply to message #690295]

Sun, 09 March 2025 01:35

Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

With SQL*Plus, in 18c:

SQL> @v

Oracle version: 18.14.0.0.210420 EE - JVM v1.8.0_161 - timezone files v31

SQL> select DBMS_METADATA.GET_DDL ('TRIGGER',trigger_name,owner) from all_triggers where rownum <=3;
DBMS_METADATA.GET_DDL('TRIGGER',TRIGGER_NAME,OWNER)
------------------------------------------------------------------------------------------------------------------------

  CREATE OR REPLACE NONEDITIONABLE TRIGGER "XDB"."XDB_RV_TRIG" INSTEAD OF insert or delete or update
on xdb.resource_view for each row
begin
  if inserting then
    xdb_rvtrig_pkg.rvtrig_ins(:new.res, :new.any_path);


  end if;

  if deleting then
     xdb_rvtrig_pkg.rvtrig_del(:old.res, :old.any_path);


  end if;

  if updating then
     xdb_rvtrig_pkg.rvtrig_upd(:old.res,    :new.res,
                               :old.any_path,   :new.any_path );
  end if;
end;
ALTER TRIGGER "XDB"."XDB_RV_TRIG" ENABLE

  CREATE OR REPLACE NONEDITIONABLE TRIGGER "XDB"."XDB$ACL$xd" after delete or update on "XDB"."XDB$ACL" for each row BEG
IN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('XDB','XDB$ACL', :old.sys_nc_oid$, 'F801D2E582364198BF99B1027401444
8' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('XDB','XDB$ACL', :old.sys_nc_oid$, 'F801D2E582364198BF9
9B10274014448', user ); END IF; END;
ALTER TRIGGER "XDB"."XDB$ACL$xd" ENABLE

  CREATE OR REPLACE NONEDITIONABLE TRIGGER "XDB"."XDB$RESCONFIG$xd" after delete or update on "XDB"."XDB$RESCONFIG" for
each row BEGIN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('XDB','XDB$RESCONFIG', :old.sys_nc_oid$, 'D478D69838784
848B83D7F8CC771A6A4' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('XDB','XDB$RESCONFIG', :old.sys_nc_oi
d$, 'D478D69838784848B83D7F8CC771A6A4', user ); END IF; END;
ALTER TRIGGER "XDB"."XDB$RESCONFIG$xd" ENABLE

3 rows selected.

If your SQL tool does not support CLOB you can do something like that:

SQL> With
  2    function trig_body (
  3      p_owner varchar2,   -- Trigger owner
  4      p_name varchar2,    -- Trigger name
  5      p_maxlg pls_integer -- Maximum output length
  6    ) return varchar2
  7    is
  8      res varchar2(32767);
  9    begin
 10      select trigger_body into res from all_triggers
 11      where owner = p_owner and trigger_name = p_name;
 12      return case
 13                when length(res) <= p_maxlg then res
 14                else substr(res,1,p_maxlg-5)||'[...]'
 15              end;
 16    exception when no_data_found then return null;
 17    end;
 18  select trig_body (owner,trigger_name,4000)
 19  from all_triggers
 20  where rownum <=3
 21  /
TRIG_BODY(OWNER,TRIGGER_NAME,4000)
------------------------------------------------------------------------------------------------------------------------
begin
  if inserting then
    xdb_rvtrig_pkg.rvtrig_ins(:new.res, :new.any_path);


  end if;

  if deleting then
     xdb_rvtrig_pkg.rvtrig_del(:old.res, :old.any_path);


  end if;

  if updating then
     xdb_rvtrig_pkg.rvtrig_upd(:old.res,    :new.res,
                               :old.any_path,   :new.any_path );
  end if;
end;
BEGIN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('XDB','XDB$ACL', :old.sys_nc_oid$, 'F801D2E582364198BF99B1027401
4448' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('XDB','XDB$ACL', :old.sys_nc_oid$, 'F801D2E582364198
BF99B10274014448', user ); END IF; END;
BEGIN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('XDB','XDB$RESCONFIG', :old.sys_nc_oid$, 'D478D69838784848B83D7F
8CC771A6A4' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('XDB','XDB$RESCONFIG', :old.sys_nc_oid$, 'D478
D69838784848B83D7F8CC771A6A4', user ); END IF; END;

3 rows selected.

[Updated on: Sun, 09 March 2025 01:35]

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690297 is a reply to message #690296]

Sun, 09 March 2025 01:43

Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And your query works well for me:

SQL> select cast(sys_dburigen(table_name, trigger_name, TRIGGER_BODY, 'text()').getclob() as varchar(4000)) AS TRIGGER_BODY
  2  from ALL_TRIGGERS
  3  where rownum <=3
  4  /
TRIGGER_BODY
------------------------------------------------------------------------------------------------------------------------
begin
  if inserting then
    xdb_rvtrig_pkg.rvtrig_ins(:new.res, :new.any_path);


  end if;

  if deleting then
     xdb_rvtrig_pkg.rvtrig_del(:old.res, :old.any_path);


  end if;

  if updating then
     xdb_rvtrig_pkg.rvtrig_upd(:old.res,    :new.res,
                               :old.any_path,   :new.any_path );
  end if;
end;
BEGIN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('XDB','XDB$ACL', :old.sys_nc_oid$, 'F801D2E582364198BF99B1027401
4448' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('XDB','XDB$ACL', :old.sys_nc_oid$, 'F801D2E582364198
BF99B10274014448', user ); END IF; END;
BEGIN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('XDB','XDB$RESCONFIG', :old.sys_nc_oid$, 'D478D69838784848B83D7F
8CC771A6A4' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('XDB','XDB$RESCONFIG', :old.sys_nc_oid$, 'D478
D69838784848B83D7F8CC771A6A4', user ); END IF; END;

3 rows selected.

Note that the first time the query is quite slow but I think it is just the time to load the dictionary in SGA: a simple COUNT(*) on ALL_TRIGGERS lasted a couple of minutes the first time and is immediate the next one.

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690298 is a reply to message #690297]

Sun, 09 March 2025 06:15

Darth Waiter
Messages: 84
Registered: October 2020

Member

Since it works well, I am really only interested in my original question: what is the meaning and purpose of parameters 1, 2, and 4 to the function.

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690299 is a reply to message #690298]

Sun, 09 March 2025 07:20

Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't know but the following link may help:

https://psoug.org/definition/SYS_DBURIGEN.htm

Quote:

In Oracle PL/SQL, SYS_DBURIGEN is a built in function which is used to generate a URL for a row object in a table. It accepts column name, an attribute name, and an optional rowid to generate the URL of DBURIType. The URL can then be used to retrieve an XML document from the database. This URL is similar to XML path which is used to retrieve a row from an XML document in the database. Note that the input column or attribute name must belong to the same table. If you want the URL to point only to the text of the document, then specify the optional 'text()' parameter.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_DBURIGEN.html

Quote:

SYS_DBURIGen takes as its argument one or more columns or attributes, and optionally a rowid, and generates a URL of data type DBURIType to a particular column or row object. You can then use the URL to retrieve an XML document from the database.

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690300 is a reply to message #690299]

Sun, 09 March 2025 10:31

Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

To understand how it works it is good to break down the query:

SQL> select sys_dburigen(table_name, trigger_name, TRIGGER_BODY) uri from ALL_TRIGGERS where rownum=1;
URI(URL, SPARE)
----------------------------------------------------------------------------------------------------------------------------
DBURITYPE('/PUBLIC/ALL_TRIGGERS/ROW[TABLE_NAME=''CWM$DIMENSION'' and TRIGGER_NAME=''CWM$DIMENSIONDEL'']/TRIGGER_BODY', NULL)

1 row selected.

So sys_dburigen generates a pseudo query with in its path the table name (/PUBLIC/ALL_TRIGGERS) and what it wants to get (/ROW) using the attributes for the WHERE clause: TABLE_NAME=''CWM$DIMENSION'' and TRIGGER_NAME=''CWM$DIMENSIONDEL'', and in the end the column you want (/TRIGGER_BODY).
The 2 first parameters are used to generate the WHERE clause, the last one the result column you want.

One step further:

SQL> select sys_dburigen(table_name, trigger_name, TRIGGER_BODY).getclob() uri from ALL_TRIGGERS where rownum=1;
URI
------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
 <TRIGGER_BODY>declare
  begin
     delete from olapsys.CwM2$AWDimLoad
      where Dim_IRID   = :old.irid
      and   Version_ID = 'CWM';
    delete  from olapsys.CwM2$AWCompSpecMembership
      where IRID in (select  csm.IRID
                       from  CwM2$AWCompositeSpec cs
                            ,CwM2$AWCompSpecMembership csm
                       where cs.IRID       = csm.CompSpec_IRID
                       and   cs.Version_ID = 'CWM'
                       and   csm.Dim_IRID  = :old.irid);
    delete  from olapsys.CwM2$AWCubeAggLevel
      where IRID in (select  cal.IRID
                       from  CwM2$AWCubeAgg ca
                            ,CwM2$AWCubeAggLevel cal
                       where ca.IRID       = cal.CubeAgg_IRID
                       and   ca.Version_ID = 'CWM'
                       and   cal.Dim_IRID  = :old.irid);
  end;</TRIGGER_BODY>

1 row selected.

The "getclob()" function forces the execution of the pseudo query (internally converted in a SQL one) and returns its result as an XML document.

Now the "text()" parameter:

SQL> select sys_dburigen(table_name, trigger_name, TRIGGER_BODY,'text()') uri from ALL_TRIGGERS where rownum=1;
URI(URL, SPARE)
------------------------------------------------------------------------------------------------------------------------------------------------------
DBURITYPE('/PUBLIC/ALL_TRIGGERS/ROW[TABLE_NAME=''CWM$DIMENSION'' and TRIGGER_NAME=''CWM$DIMENSIONDEL'']/TRIGGER_BODY/text()', NULL)

It indicates you want the content of this XML document (as the usual text() function on XML type):

SQL> select sys_dburigen(table_name, trigger_name, TRIGGER_BODY,'text()').getclob() uri from ALL_TRIGGERS where rownum=1;
URI
------------------------------------------------------------------------------------------------------------------------
declare
  begin
     delete from olapsys.CwM2$AWDimLoad
      where Dim_IRID   = :old.irid
      and   Version_ID = 'CWM';
    delete  from olapsys.CwM2$AWCompSpecMembership
      where IRID in (select  csm.IRID
                       from  CwM2$AWCompositeSpec cs
                            ,CwM2$AWCompSpecMembership csm
                       where cs.IRID       = csm.CompSpec_IRID
                       and   cs.Version_ID = 'CWM'
                       and   csm.Dim_IRID  = :old.irid);
    delete  from olapsys.CwM2$AWCubeAggLevel
      where IRID in (select  cal.IRID
                       from  CwM2$AWCubeAgg ca
                            ,CwM2$AWCubeAggLevel cal
                       where ca.IRID       = cal.CubeAgg_IRID
                       and   ca.Version_ID = 'CWM'
                       and   cal.Dim_IRID  = :old.irid);
  end;

1 row selected.

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690301 is a reply to message #690300]

Sun, 09 March 2025 10:48

Michel Cadot
Messages: 68753
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Documentation is clearer that I am:
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Data-Types.html#GUID-6C9AC925-4E3F-476D-BB63-5A70CC12FC40__GUID-4 734151A-3DB6-45C7-BAC1-B8D08C5F47E8

Quote:

DBURIType can be used to store DBURIRef values, which reference data inside the database. Storing DBURIRef values lets you reference data stored inside or outside the database and access the data consistently.

DBURIRef values use an XPath-like representation to reference data inside the database. If you imagine the database as an XML tree, then you would see the tables, rows, and columns as elements in the XML document. For example, the sample human resources user hr would see the following XML tree:
<HR> 
  <EMPLOYEES> 
    <ROW> 
      <EMPLOYEE_ID>205</EMPLOYEE_ID> 
      <LAST_NAME>Higgins</LAST_NAME> 
      <SALARY>12008</SALARY> 
      ..  
    </ROW> 
    ...  
  </EMPLOYEES> 
   
</HR> 
 
The DBURIRef is an XPath expression over this virtual XML document. So to reference the SALARY value in the EMPLOYEES table for the employee with employee number 205, you can write a DBURIRef as,
/HR/EMPLOYEES/ROW[EMPLOYEE_ID=205]/SALARY 
Using this model, you can reference data stored in CLOB columns or other columns and expose them as URLs to the external world.

Report message to a moderator

Re: Could you please explain how SYS_DBURIGEN() works? [message #690302 is a reply to message #690301]

Mon, 10 March 2025 18:19

Darth Waiter
Messages: 84
Registered: October 2020

Member

I see! URI is a very unfitting and confusing term for this function because it is counterintuitive. XMLPath would have been much more clear.

Report message to a moderator

Previous Topic:	sql performance (merged)
Next Topic:	Now you see it. Now you don't.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Mar 15 22:46:14 CDT 2025