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.
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"