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
00911. 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