Complex datatypes are not handled with ruby-plsql v 0.8.0 for oracle 19c about ruby-plsql HOT 13 OPEN

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;

from ruby-plsql.

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

from ruby-plsql.

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

from ruby-plsql.

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

from ruby-plsql.

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

from ruby-plsql.

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

from ruby-plsql.

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

from ruby-plsql.

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)

from ruby-plsql.

@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

from ruby-plsql.

@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

from ruby-plsql.

@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

from ruby-plsql.

@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)>'

from ruby-plsql.

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

from ruby-plsql.