Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
joschug opened this issue Oct 11, 2021 · 3 comments · May be fixed by #208
Open

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

joschug opened this issue Oct 11, 2021 · 3 comments · May be fixed by #208

Comments

@joschug
Copy link

joschug commented Oct 11, 2021 •
edited
Loading

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
Copy link
Author

joschug commented 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.

This was referenced Oct 11, 2021
Closed
Closed
@joschug
Copy link
Author

joschug commented 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 joschug linked a pull request Oct 27, 2021 that will close this issue
Open
@joschug
Copy link
Author

joschug commented 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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Improve 18c support joschug/ruby-plsql
1 participant
@joschug