How to get user defined OPERATORs in oracle

Question

I have been racking my brain for some time but I am unable to find a way to retrieve the Operator definition in either Toad or SQL Developer.

We have a user defined operator in our support Application Database and I can see it in all_objects dictionary but I am not able to retrieve its definition.

Is there a way to do that?

I have already been through the oracle documentation but I can't seem to find anything about operators, except how to create one.

Oracle Link 1

Oracle Link 2

Answer 1

Simply use dbms_metadata package and specifically get_ddl() function to extract DDL for an operator as you would use it to extract DDL for tables, views, and other database objects:

Here is an example:

create or replace function multf(
  arg1 in number,
  arg2 in number 
 ) return number
 is
 begin
   return arg1 * arg2;
 end;
 /

create or replace operator mult
  binding (number, number)
  return number
  using multf;
 /


 select dbms_metadata.get_ddl('OPERATOR', 'MULT')
  from dual

Result of the query:

OP_DDL                                                                          
--------------------------------------------------------

  CREATE OR REPLACE OPERATOR "NK"."MULT" BINDING
    (NUMBER, NUMBER) RETURN NUMBER
       USING "MULTF"