Class: PLSQL::Procedure

Inherits:
Object
  • Object
show all
Extended by:
ProcedureClassMethods
Defined in:
lib/plsql/procedure.rb

Instance Method Summary collapse

Methods included from ProcedureClassMethods

find

Constructor Details

#initialize(schema, procedure, package = nil) ⇒ Procedure

Returns a new instance of Procedure.



28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/plsql/procedure.rb', line 28

def initialize(schema, procedure, package = nil)
  @schema = schema
  @procedure = procedure.to_s.upcase
  @package = package
  @arguments = {}
  @argument_list = {}
  @out_list = {}
  @return = {}
  @overloaded = false
  # RSI: due to 10gR2 all_arguments performance issue SELECT split into two statements
  # added condition to ensure that if object is package then package specification not body is selected
  object_id = @schema.connection.select_first("
    SELECT o.object_id
    FROM all_objects o
    WHERE o.owner = :owner
    AND o.object_name = :object_name
    AND o.object_type <> 'PACKAGE BODY'
    ", @schema.schema_name, @package ? @package : @procedure
  )[0] rescue nil
  num_rows = @schema.connection.select_all("
    SELECT a.argument_name, a.position, a.data_type, a.in_out, a.data_length, a.data_precision, a.data_scale, a.overload
    FROM all_arguments a
    WHERE a.object_id = :object_id
    AND a.owner = :owner
    AND a.object_name = :procedure_name
    AND NVL(a.package_name,'nil') = :package
    ", object_id, @schema.schema_name, @procedure, @package ? @package : 'nil'
  ) do |r|

    argument_name, position, data_type, in_out, data_length, data_precision, data_scale, overload = r

    @overloaded ||= !overload.nil?
    # if not overloaded then store arguments at key 0
    overload ||= 0
    @arguments[overload] ||= {}
    @return[overload] ||= nil
    
    if argument_name
      @arguments[overload][argument_name.downcase.to_sym] = {
        :position => position,
        :data_type => data_type,
        :in_out => in_out,
        :data_length => data_length,
        :data_precision => data_precision,
        :data_scale => data_scale
      }
    # if function has return value
    elsif position == 0 && in_out == 'OUT'
      @return[overload] = {
        :data_type => data_type,
        :in_out => in_out,
        :data_length => data_length,
        :data_precision => data_precision,
        :data_scale => data_scale
      }
    end
  end
  @overloads = @arguments.keys.sort
  @overloads.each do |overload|
    @argument_list[overload] = @arguments[overload].keys.sort {|k1, k2| @arguments[overload][k1][:position] <=> @arguments[overload][k2][:position]}
    @out_list[overload] = @argument_list[overload].select {|k| @arguments[overload][k][:in_out] =~ /OUT/}
  end
end

Instance Method Details

#exec(*args) ⇒ Object 



96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
 
# File 'lib/plsql/procedure.rb', line 96

def exec(*args)
  # find which overloaded definition to use
  # if definition is overloaded then match by number of arguments
  if @overloaded
    # named arguments
    if args.size == 1 && args[0].is_a?(Hash)
      number_of_args = args[0].keys.size
      overload = @argument_list.keys.detect do |ov|
        @argument_list[ov].size == number_of_args &&
        @arguments[ov].keys.sort_by{|k| k.to_s} == args[0].keys.sort_by{|k| k.to_s}
      end
    # sequential arguments
    # TODO: should try to implement matching by types of arguments
    else
      number_of_args = args.size
      overload = @argument_list.keys.detect do |ov|
        @argument_list[ov].size == number_of_args
      end
    end
    raise ArgumentError, "Wrong number of arguments passed to overloaded PL/SQL procedure" unless overload
  else
    overload = 0
  end

  sql = "BEGIN\n"
  sql << ":return := " if @return[overload]
  sql << "#{@schema.schema_name}." if @schema
  sql << "#{@package}." if @package
  sql << "#{@procedure}("

  # Named arguments
  args_list = []
  args_hash = {}
  if args.size == 1 and args[0].is_a?(Hash)
    sql << args[0].map do |k,v|
      raise ArgumentError, "Wrong argument passed to PL/SQL procedure" unless @arguments[overload][k]
      args_list << k
      args_hash[k] = v
      "#{k.to_s} => :#{k.to_s}"
    end.join(', ')
  # Sequential arguments
  else
    raise ArgumentError, "Too many arguments passed to PL/SQL procedure" if args.size > @argument_list[overload].size
    # Add missing arguments with nil value
    args = args + [nil]*(@argument_list[overload].size-args.size) if args.size < @argument_list[overload].size
    i = 0
    sql << args.map do |v|
      k = @argument_list[overload][i]
      i += 1
      args_list << k
      args_hash[k] = v
      ":#{k.to_s}"
    end.join(', ')
  end
  sql << ");\n"
  sql << "END;\n"

  cursor = @schema.connection.parse(sql)
  
  args_list.each do |k|
    data_type, data_length = plsql_to_ruby_data_type(@arguments[overload][k])
    cursor.bind_param(":#{k.to_s}", ruby_value_to_ora_value(args_hash[k], data_type),
                                    data_type, data_length, @arguments[overload][k][:in_out])
  end
  
  if @return[overload]
    data_type, data_length = plsql_to_ruby_data_type(@return[overload])
    cursor.bind_param(":return", nil, data_type, data_length, 'OUT')
  end
  
  cursor.exec

  # if function with output parameters
  if @return[overload] && @out_list[overload].size > 0
    result = [ora_value_to_ruby_value(cursor[':return']), {}]
    @out_list[overload].each do |k|
      result[1][k] = ora_value_to_ruby_value(cursor[":#{k}"])
    end
  # if function without output parameters
  elsif @return[overload]
    result = ora_value_to_ruby_value(cursor[':return'])
  # if procedure with output parameters
  elsif @out_list[overload].size > 0
    result = {}
    @out_list[overload].each do |k|
      result[k] = ora_value_to_ruby_value(cursor[":#{k}"])
    end
  # if procedure without output parameters
  else
    result = nil
  end
  cursor.close
  result
end

#overloaded? ⇒ Boolean

Returns:



92
93
94
 
# File 'lib/plsql/procedure.rb', line 92

def overloaded?
  @overloaded
end