Issue oddgen/oddgen#26 - workaround for missing object_name children in navigator tree

issue_26a.sql

/*
* Copyright 2016 Philipp Salvisberg <philipp.salvisberg@trivadis.com>
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
*     http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
--
-- workaround - option a) unit as parameter
--
CREATE OR REPLACE PACKAGE issue_26a AUTHID CURRENT_USER IS
   SUBTYPE string_type IS VARCHAR2(1000 CHAR);
   SUBTYPE param_type IS VARCHAR2(60 CHAR);
   TYPE t_string IS TABLE OF string_type;
   TYPE t_param IS TABLE OF string_type INDEX BY param_type;
   TYPE t_lov IS TABLE OF t_string INDEX BY param_type;
   
   FUNCTION get_name RETURN VARCHAR2;

   FUNCTION get_object_types RETURN t_string;

   FUNCTION get_params(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2)
      RETURN t_param;

   FUNCTION get_lov(in_object_type IN VARCHAR2,
                    in_object_name IN VARCHAR2,
                    in_params      IN t_param) RETURN t_lov;

   FUNCTION generate(in_object_type IN VARCHAR2,
                     in_object_name IN VARCHAR2,
                     in_params      IN t_param) RETURN CLOB;
END issue_26a;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY issue_26a IS
   co_unit CONSTANT param_type := 'Function/Procedure';
   
   FUNCTION get_name RETURN VARCHAR2 IS
   BEGIN
      RETURN 'Issue 26a) Package Unit in LOV';
   END get_name;

   FUNCTION get_object_types RETURN t_string IS
   BEGIN
      RETURN NEW t_string('PACKAGE');
   END get_object_types;

   FUNCTION get_params(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2)
      RETURN t_param IS
      l_params t_param;
   BEGIN
      l_params(co_unit) := NULL;
      RETURN l_params;
   END get_params;

   FUNCTION get_lov(in_object_type IN VARCHAR2,
                    in_object_name IN VARCHAR2,
                    in_params      IN t_param) RETURN t_lov IS
      l_lovs  t_lov;
      l_units t_string;
   BEGIN
      SELECT procedure_name
        BULK COLLECT
        INTO l_units
        FROM user_procedures
       WHERE object_type = in_object_type
             AND object_name = in_object_name
             AND procedure_name IS NOT NULL
             AND (overload = 1 OR overload IS NULL)
       ORDER BY subprogram_id;
      l_lovs(co_unit) := l_units;
      RETURN l_lovs;
   END get_lov;

   FUNCTION generate(in_object_type IN VARCHAR2,
                     in_object_name IN VARCHAR2,
                     in_params      IN t_param) RETURN CLOB IS
   BEGIN
      RETURN '-- do something useful with ' || in_object_type || '.' || in_object_name || '.' || in_params(co_unit);
   END generate;
END issue_26a;
/
SHOW ERRORS

GRANT EXECUTE ON issue_26a TO PUBLIC;

issue_26b.sql

/*
* Copyright 2016 Philipp Salvisberg <philipp.salvisberg@trivadis.com>
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
*     http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
--
-- workaround - option b) unit as part of object_name
--
CREATE OR REPLACE PACKAGE issue_26b AUTHID CURRENT_USER IS
   SUBTYPE string_type IS VARCHAR2(1000 CHAR);
   SUBTYPE param_type IS VARCHAR2(60 CHAR);
   TYPE t_string IS TABLE OF string_type;

   FUNCTION get_name RETURN VARCHAR2;

   FUNCTION get_object_types RETURN t_string;

   FUNCTION get_object_names(in_object_type IN VARCHAR2)
      RETURN t_string;

   FUNCTION generate(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2) RETURN CLOB;
END issue_26b;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY issue_26b IS
   FUNCTION get_name RETURN VARCHAR2 IS
   BEGIN
      RETURN 'Issue 26b) Package Unit in Object Name';
   END get_name;

   FUNCTION get_object_types RETURN t_string IS
   BEGIN
      RETURN NEW t_string('PACKAGE');
   END get_object_types;

   FUNCTION get_object_names(in_object_type IN VARCHAR2)
      RETURN t_string IS
      l_object_names t_string;
   BEGIN
      SELECT object_name || '.' || procedure_name AS object_name
        BULK COLLECT
        INTO l_object_names
        FROM user_procedures
       WHERE object_type = in_object_type
             AND procedure_name IS NOT NULL
             AND (overload = 1 OR overload IS NULL)
       ORDER BY subprogram_id;
      RETURN l_object_names;
   END get_object_names;

   FUNCTION generate(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2) RETURN CLOB IS
   BEGIN
      RETURN '-- do something useful with ' || in_object_type || '.' || in_object_name;
   END generate;
END issue_26b;
/
SHOW ERRORS

GRANT EXECUTE ON issue_26b TO PUBLIC;