def find(schema, procedure, package = nil, override_schema_name = nil)
      if package.nil?
        if (row = schema.select_first(
          "SELECT #{procedure_object_id_src(schema)}.object_id
          FROM all_procedures p, all_objects o
          WHERE p.owner = :owner
            AND p.object_name = :object_name
            AND o.owner = p.owner
            AND o.object_name = p.object_name
            AND o.object_type in ('PROCEDURE', 'FUNCTION')",
            schema.schema_name, procedure.to_s.upcase))
          new(schema, procedure, nil, nil, row[0])
        # search for synonym
        elsif (row = schema.select_first(
          "SELECT o.owner, o.object_name, #{procedure_object_id_src(schema)}.object_id
          FROM all_synonyms s, all_objects o, all_procedures p
          WHERE s.owner IN (:owner, 'PUBLIC')
            AND s.synonym_name = :synonym_name
            AND o.owner = s.table_owner
            AND o.object_name = s.table_name
            AND o.object_type IN ('PROCEDURE','FUNCTION')
            AND o.owner = p.owner
            AND o.object_name = p.object_name
            ORDER BY DECODE(s.owner, 'PUBLIC', 1, 0)",
            schema.schema_name, procedure.to_s.upcase))
          new(schema, row[1], nil, row[0], row[2])
        else
          nil
        end
      elsif package && (row = schema.select_first(
        # older Oracle versions do not have object_id column in all_procedures
        "SELECT #{procedure_object_id_src(schema)}.object_id
        FROM all_procedures p, all_objects o
        WHERE p.owner = :owner
          AND p.object_name = :object_name
          AND p.procedure_name = :procedure_name
          AND o.owner = p.owner
          AND o.object_name = p.object_name
          AND o.object_type = 'PACKAGE'",
            override_schema_name || schema.schema_name, package, procedure.to_s.upcase))
        new(schema, procedure, package, override_schema_name, row[0])
      else
        nil
      end
    end
def find(schema, procedure, package = nil, override_schema_name = nil)
      if package.nil?
        if (row = schema.select_first(
          "SELECT #{procedure_object_id_src(schema)}.object_id
          FROM all_procedures p, all_objects o
          WHERE p.owner = :owner
            AND p.object_name = :object_name
            AND o.owner = p.owner
            AND o.object_name = p.object_name
            AND o.object_type in ('PROCEDURE', 'FUNCTION')",
            schema.schema_name, procedure.to_s.upcase))
          new(schema, procedure, nil, nil, row[0])
        # search for synonym
        elsif (row = schema.select_first(
          "SELECT o.owner, o.object_name, #{procedure_object_id_src(schema)}.object_id
          FROM all_synonyms s, all_objects o, all_procedures p
          WHERE s.owner IN (:owner, 'PUBLIC')
            AND s.synonym_name = :synonym_name
            AND o.owner = s.table_owner
            AND o.object_name = s.table_name
            AND o.object_type IN ('PROCEDURE','FUNCTION')
            AND o.owner = p.owner
            AND o.object_name = p.object_name
            ORDER BY DECODE(s.owner, 'PUBLIC', 1, 0)",
            schema.schema_name, procedure.to_s.upcase))
          new(schema, row[1], nil, row[0], row[2])
        else
          nil
        end
      elsif package && (row = schema.select_first(
        # older Oracle versions do not have object_id column in all_procedures
        "SELECT #{procedure_object_id_src(schema)}.object_id
        FROM all_procedures p, all_objects o
        WHERE p.owner = :owner
          AND p.object_name = :object_name
          AND p.procedure_name = :procedure_name
          AND o.owner = p.owner
          AND o.object_name = p.object_name
          AND o.object_type = 'PACKAGE'",
            override_schema_name || schema.schema_name, package, procedure.to_s.upcase))
        new(schema, procedure, package, override_schema_name, row[0])
      else
        nil
      end
    end
:nodoc: