Module: PLSQL::ProcedureCommon

Included in:
Procedure, Type::TypeProcedure
Defined in:
lib/plsql/procedure.rb

Overview

:nodoc:

Constant Summary collapse

PLSQL_COMPOSITE_TYPES = 
['PL/SQL RECORD', 'PL/SQL TABLE', 'TABLE', 'VARRAY', 'REF CURSOR'].freeze
PLSQL_COLLECTION_TYPES = 
['PL/SQL TABLE', 'TABLE', 'VARRAY'].freeze

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#argument_list ⇒ Object (readonly)

Returns the value of attribute argument_list.



61
62
63
 
# File 'lib/plsql/procedure.rb', line 61

def argument_list
  @argument_list
end

#arguments ⇒ Object (readonly)

Returns the value of attribute arguments.



61
62
63
 
# File 'lib/plsql/procedure.rb', line 61

def arguments
  @arguments
end

#out_list ⇒ Object (readonly)

Returns the value of attribute out_list.



61
62
63
 
# File 'lib/plsql/procedure.rb', line 61

def out_list
  @out_list
end

#package ⇒ Object (readonly)

Returns the value of attribute package.



62
63
64
 
# File 'lib/plsql/procedure.rb', line 62

def package
  @package
end

#procedure ⇒ Object (readonly)

Returns the value of attribute procedure.



62
63
64
 
# File 'lib/plsql/procedure.rb', line 62

def procedure
  @procedure
end

#return ⇒ Object (readonly)

Returns the value of attribute return.



61
62
63
 
# File 'lib/plsql/procedure.rb', line 61

def return
  @return
end

#schema ⇒ Object (readonly)

Returns the value of attribute schema.



62
63
64
 
# File 'lib/plsql/procedure.rb', line 62

def schema
  @schema
end

#schema_name ⇒ Object (readonly)

Returns the value of attribute schema_name.



62
63
64
 
# File 'lib/plsql/procedure.rb', line 62

def schema_name
  @schema_name
end

Class Method Details

.type_to_sql(metadata) ⇒ Object

return type string from metadata that can be used in DECLARE block or table definition



65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/plsql/procedure.rb', line 65

def self.type_to_sql(metadata) #:nodoc:
  case metadata[:data_type]
  when 'NUMBER'
    precision, scale = metadata[:data_precision], metadata[:data_scale]
    "NUMBER#{precision ? "(#{precision}#{scale ? ",#{scale}": ""})" : ""}"
  when 'VARCHAR2', 'CHAR'
    length = case metadata[:char_used]
    when 'C' then "#{metadata[:char_length]} CHAR"
    when 'B' then "#{metadata[:data_length]} BYTE"
    else
      metadata[:data_length]
    end
    "#{metadata[:data_type]}#{length && "(#{length})"}"
  when 'NVARCHAR2', 'NCHAR'
    length = metadata[:char_length]
    "#{metadata[:data_type]}#{length && "(#{length})"}"
  when 'PL/SQL TABLE', 'TABLE', 'VARRAY', 'OBJECT'
    metadata[:sql_type_name]
  else
    metadata[:data_type]
  end
end

Instance Method Details

#collection_type?(data_type) ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


239
240
241
 
# File 'lib/plsql/procedure.rb', line 239

def collection_type?(data_type) #:nodoc:
  PLSQL_COLLECTION_TYPES.include? data_type
end

#composite_type?(data_type) ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


234
235
236
 
# File 'lib/plsql/procedure.rb', line 234

def composite_type?(data_type) #:nodoc:
  PLSQL_COMPOSITE_TYPES.include? data_type
end

#construct_argument_list_for_overloads ⇒ Object

:nodoc:



201
202
203
204
205
206
207
 
# File 'lib/plsql/procedure.rb', line 201

def construct_argument_list_for_overloads #:nodoc:
  @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

#ensure_tmp_tables_created(overload) ⇒ Object

:nodoc:



209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
 
# File 'lib/plsql/procedure.rb', line 209

def ensure_tmp_tables_created(overload) #:nodoc:
  return if @tmp_tables_created.nil? || @tmp_tables_created[overload]
  @tmp_table_names[overload] && @tmp_table_names[overload].each do |table_name, argument_metadata|
    sql = "CREATE GLOBAL TEMPORARY TABLE #{table_name} (\n"
      element_metadata = argument_metadata[:element]
      case element_metadata[:data_type]
      when 'PL/SQL RECORD'
        fields_metadata = element_metadata[:fields]
        fields_sorted_by_position = fields_metadata.keys.sort_by{|k| fields_metadata[k][:position]}
        sql << fields_sorted_by_position.map do |field|
          metadata = fields_metadata[field]
          "#{field} #{ProcedureCommon.type_to_sql(metadata)}"
        end.join(",\n")
      else
        sql << "element #{ProcedureCommon.type_to_sql(element_metadata)}"
      end
      sql << ",\ni__ NUMBER(38)\n"
    sql << ") ON COMMIT PRESERVE ROWS\n"
    sql_block = "DECLARE\nPRAGMA AUTONOMOUS_TRANSACTION;\nBEGIN\nEXECUTE IMMEDIATE :sql;\nEND;\n"
    @schema.execute sql_block, sql
  end
  @tmp_tables_created[overload] = true
end

#get_argument_metadata ⇒ Object

get procedure argument metadata from data dictionary



89
90
91
92
93
94
95
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
190
191
192
193
194
195
196
197
198
199
 
# File 'lib/plsql/procedure.rb', line 89

def get_argument_metadata #:nodoc:
  @arguments = {}
  @argument_list = {}
  @out_list = {}
  @return = {}
  @overloaded = false

  # store reference to previous level record or collection metadata
  previous_level_argument_metadata = {}

  # store tmp tables for each overload for table parameters with types defined inside packages
  @tmp_table_names = {}
  # store if tmp tables are created for specific overload
  @tmp_tables_created = {}

  # subprogram_id column is available just from version 10g
  subprogram_id_column = (@schema.connection.database_version <=> [10, 2, 0, 2]) >= 0 ? 'subprogram_id' : 'NULL'

  @schema.select_all(
    "SELECT #{subprogram_id_column}, object_name, TO_NUMBER(overload), argument_name, position, data_level,
          data_type, in_out, data_length, data_precision, data_scale, char_used,
          char_length, type_owner, type_name, type_subname
    FROM all_arguments
    WHERE object_id = :object_id
    AND owner = :owner
    AND object_name = :procedure_name
    ORDER BY overload, sequence",
    @object_id, @schema_name, @procedure
  ) do |r|

    subprogram_id, object_name, overload, argument_name, position, data_level,
        data_type, in_out, data_length, data_precision, data_scale, char_used,
        char_length, type_owner, type_name, type_subname = r

    @overloaded ||= !overload.nil?
    # if not overloaded then store arguments at key 0
    overload ||= 0
    @arguments[overload] ||= {}
    @return[overload] ||= nil
    @tmp_table_names[overload] ||= []

    sql_type_name = type_owner && "#{type_owner == 'PUBLIC' ? nil : "#{type_owner}."}#{type_name}#{type_subname ? ".#{type_subname}" : nil}"

    tmp_table_name = nil
    # type defined inside package
    if type_subname
      if collection_type?(data_type)
        raise ArgumentError, "#{data_type} type #{sql_type_name} definition inside package is not supported as part of other type definition," <<
          " use CREATE TYPE outside package" if data_level > 0
        # if subprogram_id was not supported by all_arguments view
        # then generate unique ID from object_name and overload
        subprogram_id ||= "#{object_name.hash % 10000}#{overload}"
        tmp_table_name = "#{Connection::RUBY_TEMP_TABLE_PREFIX}#{@schema.connection.session_id}_#{@object_id}_#{subprogram_id}_#{position}"
      elsif data_type != 'PL/SQL RECORD'
        # raise exception only when there are no overloaded procedure definitions
        # (as probably this overload will not be used at all)
        raise ArgumentError, "Parameter type #{sql_type_name} definition inside package is not supported, use CREATE TYPE outside package" if overload == 0
      end
    end

    argument_metadata = {
      :position => position && position.to_i,
      :data_type => data_type,
      :in_out => in_out,
      :data_length => data_length && data_length.to_i,
      :data_precision => data_precision && data_precision.to_i,
      :data_scale => data_scale && data_scale.to_i,
      :char_used => char_used,
      :char_length => char_length && char_length.to_i,
      :type_owner => type_owner,
      :type_name => type_name,
      :type_subname => type_subname,
      :sql_type_name => sql_type_name
    }
    if tmp_table_name
      @tmp_table_names[overload] << [(argument_metadata[:tmp_table_name] = tmp_table_name), argument_metadata]
    end

    if composite_type?(data_type)
      case data_type
      when 'PL/SQL RECORD'
        argument_metadata[:fields] = {}
      end
      previous_level_argument_metadata[data_level] = argument_metadata
    end

    # if function has return value
    if argument_name.nil? && data_level == 0 && in_out == 'OUT'
      @return[overload] = argument_metadata
    # if parameter
    else
      # top level parameter
      if data_level == 0
        # sometime there are empty IN arguments in all_arguments view for procedures without arguments (e.g. for DBMS_OUTPUT.DISABLE)
        @arguments[overload][argument_name.downcase.to_sym] = argument_metadata if argument_name
      # or lower level part of composite type
      else
        case previous_level_argument_metadata[data_level - 1][:data_type]
        when 'PL/SQL RECORD'
          previous_level_argument_metadata[data_level - 1][:fields][argument_name.downcase.to_sym] = argument_metadata
        when 'PL/SQL TABLE', 'TABLE', 'VARRAY', 'REF CURSOR'
          previous_level_argument_metadata[data_level - 1][:element] = argument_metadata
        end
      end
    end
  end
  # if procedure is without arguments then create default empty argument list for default overload
  @arguments[0] = {} if @arguments.keys.empty?

  construct_argument_list_for_overloads
end

#overloaded? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


243
244
245
 
# File 'lib/plsql/procedure.rb', line 243

def overloaded? #:nodoc:
  @overloaded
end