11import datetime
22from collections import namedtuple
3+ from itertools import starmap , chain
34from operator import itemgetter
45
56import cx_Oracle as oracle
@@ -18,15 +19,8 @@ class Subprogram:
1819 AND object_id = :object_id
1920 AND subprogram_id = :subprogram_id
2021'''
21- subprogram_sql = f'''
22- SELECT owner, NVL(procedure_name, object_name) object_name, object_type
23- FROM all_procedures
24- WHERE owner = UPPER(:owner)
25- AND object_id = :object_id
26- AND subprogram_id = :subprogram_id
27- AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')
28- '''
29- # fixme: not gonna scale with user defined types in database
22+
23+ # fixme: not gonna scale with other types
3024 argument_mapping = {
3125 'VARCHAR2' : str ,
3226 'INTEGER' : int ,
@@ -36,16 +30,13 @@ class Subprogram:
3630 'BLOB' : oracle .BLOB
3731 }
3832
39- def __init__ (self , plsql , name , owner , object_id , subprogram_id ):
40- self .owner , self .object_id , self .subprogram_id = owner , object_id , subprogram_id
33+ def __init__ (self , plsql , name , definition ):
4134 self .plsql = plsql
4235 self .name = name
36+ self .definition = definition
4337
44- binds = {'owner' : self .owner , 'object_id' : self .object_id , 'subprogram_id' : self .subprogram_id }
45- self .definition = plsql .query (sql_query = self .subprogram_sql , binds = binds ).first
46-
47- binds = {'owner' : self .owner , 'object_name' : self .definition .object_name ,
48- 'object_id' : self .object_id , 'subprogram_id' : self .subprogram_id }
38+ binds = {'owner' : self .definition .owner , 'object_name' : self .definition .object_name ,
39+ 'object_id' : self .definition .object_id , 'subprogram_id' : self .definition .subprogram_id }
4940 self .arguments = list (plsql .query (sql_query = self .argument_sql , binds = binds ))
5041
5142 try :
@@ -100,17 +91,6 @@ def oracle_record(argument, mapping, record_type=None):
10091 setattr (record , key .upper (), value )
10192 return record
10293
103- rec_types = {
104- argument .argument_name .lower (): oracle_record (argument , kwargs [argument .argument_name .lower ()])
105- for argument
106- in self .arguments
107- if 'IN' in argument .in_out
108- if argument .data_type == 'PL/SQL RECORD'
109- }
110-
111- for argument_name , argument_value in rec_types .items ():
112- kwargs [argument_name ] = argument_value
113-
11494 def oracle_table (argument , list_of_recs ):
11595 table_type = self .plsql .connection .gettype (argument .extended_type )
11696 table = table_type .newobject ()
@@ -123,15 +103,22 @@ def oracle_table(argument, list_of_recs):
123103
124104 return list_of_recs
125105
126- table_types = {
127- argument .argument_name .lower (): oracle_table (argument , kwargs [argument .argument_name .lower ()])
128- for argument
129- in self .arguments
130- if 'IN' in argument .in_out
131- if argument .data_type == 'PL/SQL TABLE'
132- }
106+ def translate_types (conversion_func , data_type ):
107+ return {
108+ argument .argument_name .lower (): conversion_func (argument , kwargs [argument .argument_name .lower ()])
109+ for argument
110+ in self .arguments
111+ if 'IN' in argument .in_out
112+ if argument .data_type == data_type
113+ }
133114
134- for argument_name , argument_value in table_types .items ():
115+ translated_types = chain .from_iterable (
116+ translate_types (func , data_type ).items ()
117+ for func , data_type
118+ in [(oracle_record , 'PL/SQL RECORD' ), (oracle_table , 'PL/SQL TABLE' )]
119+ )
120+
121+ for argument_name , argument_value in translated_types :
135122 kwargs [argument_name ] = argument_value
136123
137124 if object_type == FUNCTION :
@@ -153,7 +140,7 @@ def retrieve_subprograms(attributes, plsql):
153140 attribute_len = len (attributes )
154141
155142 base_query = '''
156- SELECT owner, object_id, subprogram_id
143+ SELECT owner, object_id, subprogram_id, NVL(procedure_name, object_name) object_name, object_type
157144 FROM all_procedures
158145 WHERE object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE') AND {extra_where_predicates}
159146 '''
@@ -179,18 +166,45 @@ def query_subprograms(**kwargs):
179166 return query_subprograms (object_name = object_name )
180167
181168
169+ def match_parameters (subprograms , parameters , plsql ):
170+ # fixme: this sql is essentially the same as in when building the Subprogram
171+ argument_sql = f'''
172+ SELECT argument_name, data_type, defaulted, in_out, type_owner || '.' || type_name || '.' || type_subname extended_type
173+ FROM all_arguments
174+ WHERE owner = UPPER(:owner)
175+ AND object_name = :object_name
176+ AND object_id = :object_id
177+ AND subprogram_id = :subprogram_id
178+ '''
179+
180+ for subprogram in subprograms :
181+ binds = {'owner' : subprogram .owner , 'object_name' : subprogram .object_name ,
182+ 'object_id' : subprogram .object_id , 'subprogram_id' : subprogram .subprogram_id }
183+
184+ arguments = list (argument .argument_name .lower ()
185+ for argument
186+ in plsql .query (sql_query = argument_sql , binds = binds )
187+ if argument .argument_name is not None )
188+
189+ if set (arguments ) == set (parameters .keys ()):
190+ return subprogram
191+
192+
182193def resolve_subprogram (attributes , parameters , plsql ):
183194 assert attributes , 'Attributes should not be empty!'
184- _ = parameters
185195
186196 matching_subprograms = retrieve_subprograms (attributes , plsql )
187197
188- if len (matching_subprograms ) > 1 :
189- raise NotImplementedError ('Can not resolve to single subprogram!' )
190-
191198 if not matching_subprograms :
192199 raise AttributeError ('No such subprogram exists!' )
193200
201+ if len (matching_subprograms ) > 1 :
202+ matched = match_parameters (matching_subprograms , parameters , plsql )
203+
204+ if matched :
205+ return matched
206+ raise NotImplementedError ('Can not resolve to single subprogram!' )
207+
194208 return matching_subprograms [0 ]
195209
196210
@@ -207,7 +221,7 @@ def __getattr__(self, item):
207221
208222 def __call__ (self , ** parameters ):
209223 found_subprogram = resolve_subprogram (attributes = self .attributes , parameters = parameters , plsql = self .plsql )
210- subprogram = Subprogram (self .plsql , '.' .join (self .attributes ), ** found_subprogram . _asdict () )
224+ subprogram = Subprogram (self .plsql , '.' .join (self .attributes ), found_subprogram )
211225 return subprogram (** parameters )
212226
213227
@@ -224,15 +238,13 @@ def execute(self):
224238 cursor .execute (self .query )
225239
226240 if len (cursor .description ) > 1 :
227- record_type = namedtuple ('Result' , [column [0 ].lower () for column in cursor .description ])
228-
229- def result (rec ):
230- return record_type (* rec )
241+ record_type = namedtuple ('Record' , (column [0 ].lower () for column in cursor .description ))
242+ records = starmap (record_type , cursor )
231243 else :
232- result = itemgetter (0 )
244+ records = map ( itemgetter (0 ), cursor )
233245
234- for record in cursor :
235- yield result ( record )
246+ for record in records :
247+ yield record
236248
237249 @property
238250 def first (self ):
0 commit comments