Complex datatypes are not handled with ruby-plsql v 0.8.0 for oracle 19c

[AvneetBaweja]

Hello @bpom and @yahonda

I see that the changes under this pull#186 request has been released into latest version of ruby-plsql gem (0.8.0).

I have installed the required gems on my machine but i am not able to work with complex datatypes in oracle 19c.

here is my list of gems LOCAL GEMS

bigdecimal (default: 1.2.8) builder (3.2.4) bundler (2.2.25) did_you_mean (1.0.0) diff-lcs (1.4.4) io-console (default: 0.4.5) json (default: 1.8.3) mini_portile2 (2.1.0) minitest (5.8.5) net-telnet (0.1.1) nokogiri (1.6.8.1 x64-mingw32) power_assert (0.2.6) psych (default: 2.1.0) rake (10.4.2) rdoc (default: 4.2.1) rspec (3.10.0) rspec-core (3.10.1) rspec-expectations (3.10.1) rspec-mocks (3.10.2) rspec-support (3.10.2) rspec_junit_formatter (0.4.1, 0.2.3) ruby-oci8 (2.2.8 x64-mingw32, 2.2.4 x64-mingw32) ruby-plsql (0.8.0) ruby-plsql-spec (0.5.0) rubygems-update (3.2.25) test-unit (3.1.5) thor (0.19.4)

function definition : type parameter_array_ty is table of table_name%ROWTYPE; function get_param_value(pi_params in parameter_array_ty, pi_parameter_id in number) return varchar2 is

And this is the error I am getting when trying to run a package function. control_sheets GIVEN a call to get_param_value WHEN parameter array is empty it returns null Failure/Error: plsql.control_sheet.get_param_value(nil,-1)

OCIError: ORA-00911: invalid character ORA-06512: at line 4 stmt.c:267:in oci8lib_230.so ./spec/unit/controlSheetTest.rb:213:in block (3 levels) in <top (required)>' ./spec/spec_helper.rb:80:in block (2 levels) in <top (required)>'

but if I call the function this way it works plsql.select_one "select control_sheet.get_param_value(null,-1) from dual"

Things are working fine in 11g oracle . Could you please help me here. Thanks

[bpom]

This problem occurs, if you have hidden columns on this table. Can you check, if the output of following statement lists columns with column_id = null

SELECT * FROM ALL_TAB_COLS WHERE OWNER = :owner AND TABLE_NAME = :table_name ORDER BY column_id;

[AvneetBaweja]

Thanks for replying @bpom . I can confirm that there is NO hidden column in that table.

[sundar-n]

@AvneetBaweja is there any column with timestamp datatype? On top of pull#186 changes, I faced an issue when the record has timestamp datatype. A special handling similar to boolean is required for timestamp.

[AvneetBaweja]

Thanks @sundar-n , it does have datetime column. could you please share what sort of special handling can be done for that.

[AvneetBaweja]

@sundar-n I can confirm thats its nothing to do with the date column. I created a very simple table with id and name and created a type of that table and used that type as a parameter for one of the function calls and ruby threw the same error.

[AvneetBaweja]

@bpom @yahonda @sundar-n could someone please help out here ?

[bpom]

@AvneetBaweja can you provide a describe of the table? you can obfuscate column-names, if necessary, but I need to see the datatypes

[AvneetBaweja]

here is the desc

Name Null? Type

---

ID NOT NULL NUMBER
PID NOT NULL NUMBER
VALUE1 VARCHAR2(500 BYTE) VALUE2 VARCHAR2(500 BYTE) LDATE DATE
USER VARCHAR2(30 BYTE)

[bpom]

@AvneetBaweja The datatypes in the table are all supported. Can you pls modify the gem-file procedure_call.rb and uncomment line 20

puts "DEBUG: sql = #{@sql.gsub("\n","
\n")}"

and provide the output?

Under Windows you will find the file in a path similar to this: RUBY_HOME\lib\ruby\gems\VERSION\ruby-plsql-0.8.0\lib\plsql

[AvneetBaweja]

@bpom By making the above change (uncomment line 20 in procedure_call.rb ) , it did not changed the error output. I am still getting the same error. OCIError: ORA-00911: invalid character ORA-06512: at line 4

[bpom]

@AvneetBaweja I did not expect to change the error output, but executing your test on the command line you should have a debug output in the console showing the procedure call passed to Oracle. This is the interesting part to see, why you have an invalid character in the processed statement

[AvneetBaweja]

@bpom I am not getting any debug output or any different output from what I was getting before making any changes to procedure_call.rb.

here is the output from console:

GIVEN a call to get_param_value WHEN parameter array is empty it returns null (FAILED - 1) Failures:

1) control_sheets GIVEN a call to get_param_value WHEN parameter array is empty it returns null

Failure/Error: plsql.control_sheet.get_param_value(nil,-1) OCIError: ORA-00911: invalid character ORA-06512: at line 4

stmt.c:267:in oci8lib_230.so

 # ./spec/unit/controlSheetTest.rb:204:in `block (3 levels) in <top (required)>'
 # ./spec/spec_helper.rb:80:in `block (2 levels) in <top (required)>'

[AvneetBaweja]

Our error was occurring at the line "@schema.execute sql_block, sql" in procedure.ensure_tmp_tables_created

The SQL being executed was:

CREATE GLOBAL TEMPORARY TABLE ruby_196669274_5159229_23_1 (element [schemaName].[tableName]%ROWTYPE, i__ NUMBER(38) ) ON COMMIT PRESERVE ROWS ;

Error report - SQL Error: ORA-00911: invalid character

911. 00000 - "invalid character" Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. Action:

Resolution: We changed our table type definition to be a table of record type, instead of a table of %ROWTYPE