ruby-plsql 0.8.0: Procedure with parameters referencing system packages fails

[joschug]

Hello @bpom and @yahonda,

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/>

[joschug]

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]

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]

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