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

@bpom

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

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;

@bpom

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

@AvneetBaweja

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

@sundar-n

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

@sundar-n

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

@bpom

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

@AvneetBaweja

@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

@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

@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

@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

@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:in block (3 levels) in <top (required)>' # ./spec/spec_helper.rb:80:in block (2 levels) in <top (required)>'

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

Thank you for reporting this and for identifying the root cause. The issue is that ensure_tmp_tables_created generates DDL containing %ROWTYPE literally, which is not valid in a CREATE GLOBAL TEMPORARY TABLE statement.

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 %ROWTYPE in the table type definition is confirmed to work.

Add test for TABLE OF %ROWTYPE parameter in package

Fix TABLE OF %ROWTYPE parameter in package (fixes rsim#200)

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_TYPES stores ELEM_TYPE_NAME as "TEST_ROWTYPE_TBL%ROWTYPE". The code treated this as an OBJECT type, causing ensure_tmp_tables_created to embed the %ROWTYPE suffix literally in CREATE GLOBAL TEMPORARY TABLE DDL.

Fix: #262

Merge pull request #262 from yahonda/fix-rowtype-tmp-table

Fix TABLE OF %ROWTYPE parameter in package (fixes rsim#200)

Navigation Menu

Repository navigation

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

1 remaining item

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

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

Description

Activity

bpom commented on Aug 17, 2021

AvneetBaweja commented on Aug 17, 2021

sundar-n commented on Aug 17, 2021

AvneetBaweja commented on Aug 17, 2021

AvneetBaweja commented on Aug 20, 2021

AvneetBaweja commented on Aug 24, 2021

bpom commented on Aug 26, 2021

AvneetBaweja commented on Aug 26, 2021

bpom commented on Aug 27, 2021

AvneetBaweja commented on Aug 30, 2021

bpom commented on Aug 30, 2021

AvneetBaweja commented on Aug 30, 2021

AvneetBaweja commented on Sep 13, 2021

yahonda commented on Apr 13, 2026

1 remaining item

yahonda commented on Apr 13, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions