Module: PLSQL::ProcedureClassMethods

Included in:
Procedure
Defined in:
lib/plsql/procedure.rb

Overview

:nodoc:

Instance Method Summary collapse

Instance Method Details

#find(schema, procedure, package = nil, override_schema_name = nil) ⇒ Object 



3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
# File 'lib/plsql/procedure.rb', line 3

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