1 Reply Latest reply on Mar 16, 2017 9:30 PM by Vadim Tropashko-Oracle

    How display/edit source of operator objects (object_type = 'OPERATOR') in SQL Developer?

    user449716

      I recently learned about the Oracle operator object type.

       

      But when  I define an operator using SQL*PLUS, I an unable to find or edit its source in SQL Developer (such as via Shift-F4 in a SQL Worksheet.

       

      Example:

       

      CREATE OR REPLACE FUNCTION foo_nextday(

          p_date DATE

          )

      RETURN VARCHAR2

      AS

      BEGIN

        RETURN p_date + 1;

      END foo_nextday;

      /

       

      create or replace FUNCTION foo_nextday2(

          p_date_string varchar2

          )

        RETURN VARCHAR2

      AS

      begin

        return foo_nextday(to_date(p_date_string, 'YYYYMMDDHH24MI'));

      END foo_nextday2;

      /

       

      CREATE OR REPLACE OPERATOR op_foo_nextday

        BINDING

        (DATE) RETURN VARCHAR2 USING foo_nextday,

        (VARCHAR2) RETURN VARCHAR2 USING foo_nextday2;

       

       

      [2017-03-16 13:36:07 HL7Q_OWNER@odsd] SQL> col nextday format a20

      [2017-03-16 13:37:31 HL7Q_OWNER@odsd] SQL>

      [2017-03-16 13:37:32 HL7Q_OWNER@odsd] SQL> select op_foo_nextday(to_date('20170316', 'YYYYMMDD')) nextday from dual;

       

      NEXTDAY

      --------------------

      2017-03-17 00:00:00

       

       

      1 row selected.

       

      [2017-03-16 13:39:04 HL7Q_OWNER@odsd] SQL> select op_foo_nextday('20170316') nextday from dual;

       

      NEXTDAY

      --------------------

      2017-03-17 00:00:00

       

      1 row selected.

       

      [2017-03-16 13:39:08 HL7Q_OWNER@odsd] SQL>

       

       

      So the above all works..

       

      BUT, if I enter it all in an SQL Worksheet then highlight op_foo_nextday and enter Shift-F4, the following dialog appears:

       

       

      (Dialog that displays "PL/SQL unit OP_FOO_NEXTDAY does not exist).