Repository navigation
Complex datatypes are not handled with ruby-plsql v 0.8.0 for oracle 19c #200
Description
Activity
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;Thanks for replying @bpom . I can confirm that there is NO hidden column in that table.
@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.Thanks @sundar-n , it does have datetime column.
could you please share what sort of special handling can be done for that.@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 can you provide a describe of the table? you can obfuscate column-names, if necessary, but I need to see the datatypes
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)@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@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@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@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:- 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:inblock (3 levels) in <top (required)>' # ./spec/spec_helper.rb:80:inblock (2 levels) in <top (required)>'Our error was occurring at the line "@schema.execute sql_block, sql"
in procedure.ensure_tmp_tables_createdThe 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 %ROWTYPEThank you for reporting this and for identifying the root cause. The issue is that
ensure_tmp_tables_createdgenerates DDL containing%ROWTYPEliterally, which is not valid in aCREATE GLOBAL TEMPORARY TABLEstatement.Could you provide the following details to help us reproduce and fix this?
- Ruby version
- Oracle Database version (output of
select * from v$version) - ruby-oci8 version
- A minimal reproducible example (table DDL, type/package definition, and the Ruby call that triggers the error)
The workaround of using a record type instead of
%ROWTYPEin the table type definition is confirmed to work.- added a commit that references this issue
on Apr 13, 2026 1 remaining item
- added 11 commits that reference this issue
on Apr 13, 2026 We were able to reproduce this issue. Here is a minimal reproduction:
CREATE TABLE test_rowtype_tbl (id NUMBER, name VARCHAR2(50)); CREATE OR REPLACE PACKAGE test_rowtype_pkg IS TYPE t_tab IS TABLE OF test_rowtype_tbl%ROWTYPE; FUNCTION test_fn(p_tab IN t_tab) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY test_rowtype_pkg IS FUNCTION test_fn(p_tab IN t_tab) RETURN NUMBER IS BEGIN RETURN p_tab.COUNT; END; END; /
plsql.test_rowtype_pkg.test_fn([{ id: 1, name: "test" }]) # => ORA-00911: %: invalid character
The root cause: on Oracle 18c+,
ALL_PLSQL_COLL_TYPESstoresELEM_TYPE_NAMEas"TEST_ROWTYPE_TBL%ROWTYPE". The code treated this as an OBJECT type, causingensure_tmp_tables_createdto embed the%ROWTYPEsuffix literally inCREATE GLOBAL TEMPORARY TABLEDDL.Fix: #262
- added a commit that references this issue
on Apr 13, 2026 - added a commit that references this issue
on Apr 13, 2026
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