Repository navigation
ruby-plsql 0.8.0: Procedure with parameters referencing system packages fails #204
Description
we're in the process of upgrading our Oracle database from 12.2 to 19c. We've got a suite of ~7.000 Ruby tests that run fine against 12. Some fail against 19c.
I've traced the first issue down to how referenced data types in a procecdure specification are parsed.
Concrete example:
procedure write_zv_output(
p_text db_types.t_big_db_str,
p_log_kontext_kz m_log.log_kontext_kz%type default 'DEBUG',
p_quelle varchar2 default null,
p_dbms_output boolean default true,
p_file sys.utl_file.file_type default null,
p_log_disabled boolean default false,
p_parent_reference m_log.parent_reference%type default null,
p_parent_reference_type_kz m_log.parent_reference_type_kz%type default null);
Calling this procedure as
plsql.m_logpack.write_zv_output(p_text: l_text, p_log_kontext_kz: 'DEBUG', p_quelle: nil, p_dbms_output: true, p_file: nil, p_log_disabled: false)
leads to
OCIError:
ORA-06550: Zeile 4, Spalte 1:
PLS-00103: Fand das Symbol ")" als eines der folgenden erwartet wurde:
<an identifier> <a double-quoted delimited-identifier>
current delete exists prior
ORA-06550: Zeile 13, Spalte 0:
PLS-00103: Fand das Symbol "end-of-file" als eines der folgenden erwartet wurde:
end not pragma final instantiable persistable order
overriding static member constructor map
# stmt.c:267:in oci8lib_260.bundle
# /Users/jsg/.rvm/gems/ruby-2.6.3/gems/ruby-oci8-2.2.9/lib/oci8/cursor.rb:137:in `exec'
# /Users/jsg/.rvm/gems/ruby-2.6.3/gems/ruby-plsql-0.8.0/lib/plsql/oci_connection.rb:108:in `exec'
# /Users/jsg/.rvm/gems/ruby-2.6.3/gems/ruby-plsql-0.8.0/lib/plsql/procedure_call.rb:31:in `exec'
# /Users/jsg/.rvm/gems/ruby-2.6.3/gems/ruby-plsql-0.8.0/lib/plsql/procedure.rb:546:in `exec'
# /Users/jsg/.rvm/gems/ruby-2.6.3/gems/ruby-plsql-0.8.0/lib/plsql/package.rb:67:in `method_missing'
# ./spec/packages/m_logpack_spec.rb:203:in `block (4 levels) in <top (required)>'
Enabling debug output in procedure_call.rb:20 leads to:
DEBUG: sql = BEGIN<br/>
DBMS_OUTPUT.ENABLE(20000);<br/>
SYS.DBMS_OUTPUT.PUT_LINE(:a);<br/>
END;<br/>
DEBUG: sql = DECLARE<br/>
l_p_dbms_output BOOLEAN;<br/>
TYPE t_p_file IS RECORD (<br/>
);<br/>
l_p_file t_p_file;<br/>
l_p_log_disabled BOOLEAN;<br/>
BEGIN<br/>
l_p_dbms_output := (:p_dbms_output = 1);<br/>
l_p_log_disabled := (:p_log_disabled = 1);<br/>
DBMS_OUTPUT.ENABLE(20000);<br/>
MSRVLOG.M_LOGPACK.WRITE_ZV_OUTPUT(:p_text, :p_log_kontext_kz, :p_quelle, l_p_dbms_output, l_p_file, l_p_log_disabled);<br/>
END;<br/>
ruby-plsql generates a record type for p_file, which references utl_file.file_type.
When I change the reference to sys.utl_file.file_type, the procedure call works flawlessly. The resulting debug SQL:
DEBUG: sql = BEGIN<br/>
DBMS_OUTPUT.ENABLE(20000);<br/>
SYS.DBMS_OUTPUT.PUT_LINE(:a);<br/>
END;<br/>
DEBUG: sql = DECLARE<br/>
l_p_dbms_output BOOLEAN;<br/>
l_p_file SYS.UTL_FILE.FILE_TYPE;<br/>
l_p_log_disabled BOOLEAN;<br/>
BEGIN<br/>
l_p_dbms_output := (:p_dbms_output = 1);<br/>
l_p_log_disabled := (:p_log_disabled = 1);<br/>
DBMS_OUTPUT.ENABLE(20000);<br/>
MSRVLOG.M_LOGPACK.WRITE_ZV_OUTPUT(p_text => :p_text, p_log_kontext_kz => :p_log_kontext_kz, p_quelle => :p_quelle, p_dbms_output => l_p_dbms_output, p_file => l_p_file, p_log_disabled => l_p_log_disabled);<br/>
END;<br/>
Activity
joschug commentedon Oct 11, 2021
I've tested a bit more. The problem seems to stem from the select statement against
all_arguments(around line 220 in procedure.rb). If the data type of the argument is a public synonym (like utl_file.file_type), the select returns the wrong result.I know too little about the big picture to fix this properly; I assume an intermediate lookup of the synonym needs to happen and argument_metadata should be populated with the resolved type owner and type name.
joschug commentedon Oct 11, 2021
I've updated PR #205, performance is now much improved.
I've also opened PR #206 which contains a small fix for a related problem.
joschug commentedon Oct 27, 2021
I've spent some more time with the library and fixed all issues that occured with our codebase. There were a couple of cases that @bpom didn't have to deal with, like package types declared as rowtypes, type references across schemas, and a few more.
PR #208