This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new 991a571011 Fixes #7057 : bind Oracle national character and LOB
columns with the… (#8446)
991a571011 is described below
commit 991a57101190a35669b36fcafa16c5d2171ddac4
Author: Bart Maertens <[email protected]>
AuthorDate: Mon Sep 21 13:31:29 2026 +0200
Fixes #7057 : bind Oracle national character and LOB columns with the…
(#8446)
* Fixes #7057 : bind Oracle national character and LOB columns with the
JDBC calls the driver expects
* cleanup
---------
Co-authored-by: Hans Van Akelyen <[email protected]>
---
.../integration-tests-oracle.yaml | 35 ++
.../resource/oracle/oracle-nchar-entrypoint.sh | 96 ++++++
.../ROOT/pages/database/databases/oracle.adoc | 21 ++
.../oracle/0009-national-character-write.hpl | 139 ++++++++
integration-tests/oracle/0009b-oversize-write.hpl | 153 +++++++++
integration-tests/oracle/dev-env-config.json | 15 +
integration-tests/oracle/disabled.txt | 11 +
.../oracle/main-0009-national-character-types.hwf | 354 +++++++++++++++++++++
.../oracle/main-0010-national-character-repro.hwf | 354 +++++++++++++++++++++
.../oracle/metadata/rdbms/oracle-nchar.json | 27 ++
.../hop/databases/oracle/OracleDatabaseMeta.java | 15 +-
.../oracle/OraclePreparedStatementBinding.java | 174 ++++++++++
.../oracle/OraclePreparedStatementBindingTest.java | 255 +++++++++++++++
13 files changed, 1648 insertions(+), 1 deletion(-)
diff --git a/docker/integration-tests/integration-tests-oracle.yaml
b/docker/integration-tests/integration-tests-oracle.yaml
index f439172466..41c5560172 100644
--- a/docker/integration-tests/integration-tests-oracle.yaml
+++ b/docker/integration-tests/integration-tests-oracle.yaml
@@ -35,8 +35,11 @@ services:
depends_on:
oracle:
condition: service_healthy
+ oracle-nchar:
+ condition: service_healthy
links:
- oracle
+ - oracle-nchar
environment:
- HOP_DRIVERS_DOWNLOAD=oracle
- ORACLE_HOST=oracle
@@ -49,6 +52,9 @@ services:
# The client wallet the Oracle container generated, holding cwallet.sso
plus a tnsnames.ora
# with a TCPS alias. Read-only: the tests must not be able to alter what
they connect with.
- ORACLE_WALLET_DIR=/opt/oracle-data/clientWallet/FREE
+ # The second database, the one whose character set cannot hold the
national characters.
+ - ORACLE_NCHAR_HOST=oracle-nchar
+ - ORACLE_NCHAR_SERVICE=FREEPDB1
volumes:
- oracle-data:/opt/oracle-data:ro
@@ -82,5 +88,34 @@ services:
retries: 60
start_period: 300s
+ # A second database whose character set is WE8MSWIN1252 rather than the
AL32UTF8 the image
+ # ships. Writing an NVARCHAR2 through setString converts the value to the
database character
+ # set on the way in, so on this one, and not on the Unicode one, the
characters are lost.
+ # See the entrypoint for how a non-Unicode database is obtained from an
image that ships a
+ # Unicode one.
+ oracle-nchar:
+ image: container-registry.oracle.com/database/free:latest
+ hostname: oracle-nchar
+ environment:
+ - ORACLE_PWD=HopIntegrationTest_1
+ - ORACLE_CHARACTERSET=WE8MSWIN1252
+ - ORACLE_NCHARACTERSET=AL16UTF16
+ - APP_USER=hop
+ - APP_USER_PASSWORD=hop_password
+ ports:
+ - "1521"
+ volumes:
+ - oracle-nchar-data:/opt/oracle/oradata
+ -
./resource/oracle/oracle-nchar-entrypoint.sh:/opt/oracle/hop-nchar-entrypoint.sh:ro
+ command: [ "/bin/bash", "-c", "/opt/oracle/hop-nchar-entrypoint.sh" ]
+ healthcheck:
+ test: [ "CMD-SHELL", "\"$$ORACLE_BASE/$$CHECK_DB_FILE\" >/dev/null ||
exit 1" ]
+ interval: 20s
+ timeout: 15s
+ retries: 90
+ # Building a database from scratch takes far longer than opening the one
the image ships.
+ start_period: 900s
+
volumes:
oracle-data:
+ oracle-nchar-data:
diff --git
a/docker/integration-tests/resource/oracle/oracle-nchar-entrypoint.sh
b/docker/integration-tests/resource/oracle/oracle-nchar-entrypoint.sh
new file mode 100755
index 0000000000..dbd8d9b4a8
--- /dev/null
+++ b/docker/integration-tests/resource/oracle/oracle-nchar-entrypoint.sh
@@ -0,0 +1,96 @@
+#!/bin/bash
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+# Brings up the Oracle database whose character set is not Unicode.
+#
+# The image ships a built database and opens it as it is, and a database that
is merely opened
+# keeps the character set it was built with. ORACLE_CHARACTERSET is only read
when a database is
+# created, and one is only created when oradata is empty. So the baked
database is removed on the
+# first start, which is what makes the image build a WE8MSWIN1252 one in its
place.
+#
+# Emptying the volume rather than mounting an empty one is deliberate:
mounting the named volume
+# copies the baked database in with its oracle ownership, and removing the
contents afterwards
+# keeps that ownership. An empty volume would arrive owned by root, which the
database cannot
+# write to.
+
+set -u
+
+MARKER="${ORACLE_BASE}/oradata/.hop-nchar-built"
+
+if [ ! -f "${MARKER}" ]; then
+ echo "hop-it: removing the database the image ships, so that one is created
with ORACLE_CHARACTERSET=${ORACLE_CHARACTERSET:-unset}"
+ shopt -s dotglob
+ rm -rf "${ORACLE_BASE}"/oradata/* 2>/dev/null || true
+ shopt -u dotglob
+ # Read back by the next start, and by a restart after the false failure
handled below. It sits
+ # beside the database rather than inside it, and the image looks for a
directory named after
+ # the SID, so it is not mistaken for one.
+ touch "${MARKER}"
+fi
+
+APP_USER="${APP_USER:-hop}"
+APP_USER_PASSWORD="${APP_USER_PASSWORD:-hop_password}"
+APP_PDB="${APP_PDB:-FREEPDB1}"
+
+"${ORACLE_BASE}/${RUN_FILE}" &
+ORACLE_PID=$!
+
+(
+ # checkDBStatus.sh is what the image's own healthcheck uses.
+ until "${ORACLE_BASE}/${CHECK_DB_FILE}" >/dev/null 2>&1; do
+ if ! kill -0 "${ORACLE_PID}" 2>/dev/null; then
+ echo "hop-it: oracle exited before it became available" >&2
+ exit 1
+ fi
+ sleep 5
+ done
+
+ # The image creates APP_USER as one of the last steps of building a
database, and building this
+ # one ends early because the character set is not the one it expects. So the
user the tests
+ # connect as is never created, and it is created here instead. Guarded by a
lookup rather than
+ # by the marker, because a restart has to find it already there and leave it
alone.
+ if ! sqlplus -s / as sysdba <<SQL | grep -q "^${APP_USER}$"
+set heading off feedback off pagesize 0
+alter session set container=${APP_PDB};
+select username from dba_users where username = upper('${APP_USER}');
+exit
+SQL
+ then
+ echo "hop-it: creating ${APP_USER} in ${APP_PDB}, which building the
database did not get to"
+ sqlplus -s / as sysdba <<SQL
+alter session set container=${APP_PDB};
+create user ${APP_USER} identified by ${APP_USER_PASSWORD} quota unlimited on
users;
+grant connect, resource, create view to ${APP_USER};
+exit
+SQL
+ fi
+) &
+
+wait "${ORACLE_PID}"
+
+# Creating a database with a character set the image does not expect ends with
it reporting
+# failure even though the database is built and opens. Exiting here would take
the whole compose
+# run down with it (the suite runs with --abort-on-container-exit), so the
start is simply
+# repeated: the second one finds the database on the volume and opens it, and
the block above
+# runs again to add the user.
+if [ "${HOP_NCHAR_RETRIED:-0}" = "1" ]; then
+ echo "hop-it: the database returned twice, giving up rather than looping" >&2
+ exit 1
+fi
+echo "hop-it: first start returned, opening the database that is now on the
volume"
+export HOP_NCHAR_RETRIED=1
+exec "${ORACLE_BASE}/hop-nchar-entrypoint.sh"
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/database/databases/oracle.adoc
b/docs/hop-user-manual/modules/ROOT/pages/database/databases/oracle.adoc
index 40a50ef87f..9d710d1a3b 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/database/databases/oracle.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/database/databases/oracle.adoc
@@ -111,3 +111,24 @@ Use this when the file is awkward to distribute -- a
container image or a Hop Se
Either way, an Autonomous Database wallet is an auto-login (`cwallet.sso`)
one, so the connection needs `oraclepki.jar` beside the driver.
See the note in <<_tls_tcps_connections,TLS (TCPS) connections>> above; `hop
driver install oracle` takes care of it.
+
+== National character and LOB columns
+
+Oracle keeps two sets of string types.
+`VARCHAR2`, `CHAR` and `CLOB` store text in the database character set;
`NVARCHAR2`, `NCHAR` and `NCLOB` store it in the national character set, which
is where text goes that the database character set cannot represent.
+
+The JDBC driver treats the two differently, and a value written to a national
column the way a `VARCHAR2` is written comes back wrong -- usually as question
marks or replacement characters, because the driver converted it to the
database character set on the way in.
+Hop writes each of these columns with the JDBC call the driver expects for it,
so a transform such as xref:pipeline/transforms/tableoutput.adoc[Table Output]
needs nothing configured for national text to survive the round trip.
+
+Hop learns the column type from the prepared statement itself: the Oracle JDBC
driver describes the target columns of an `INSERT` or `UPDATE` once, when the
statement is first written to, so this costs one round trip per prepared
statement rather than per row.
+A driver too old to describe its bind parameters, or a statement it cannot
parse, falls back to the plain `VARCHAR2` call Hop always used.
+
+TIP: A connection-level alternative is the driver property
`oracle.jdbc.defaultNChar=true`, which makes the driver bind every string as
national text. It is not needed with Hop, and it has a cost: Oracle then
converts `VARCHAR2` columns in `WHERE` clauses to compare them, which can stop
indexes on those columns being used.
+
+=== Values longer than the column
+
+Hop writes the value it has, whatever the column is: `CLOB` and `NCLOB` are
streamed whole however long they are, and a string bound to a `VARCHAR2(n)` or
`NVARCHAR2(n)` is passed to Oracle at its full length.
+
+Fitting it is Oracle's decision, not Hop's.
+A value too wide for its column fails the row with `ORA-12899: value too large
for column`, which is what you want to see: the alternative is a pipeline that
finishes green having quietly dropped the overflow.
+Give the column the width the data needs, or use a LOB type, and handle the
error rows the way you would any other rejected row.
diff --git a/integration-tests/oracle/0009-national-character-write.hpl
b/integration-tests/oracle/0009-national-character-write.hpl
new file mode 100644
index 0000000000..8c8b7e9e4d
--- /dev/null
+++ b/integration-tests/oracle/0009-national-character-write.hpl
@@ -0,0 +1,139 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0009-national-character-write</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Writes text into Oracle's national character column types -
NVARCHAR2, NCHAR and NCLOB - alongside the VARCHAR2 and CLOB equivalents. One
batch carries a short value, a value longer than a VARCHAR2 can hold, and a row
of nulls, which is the mix that used to fail with ORA-01461.</description>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/09/17 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/09/17 12:00:00.000</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>National text in</from>
+ <to>National text out</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>National text in</name>
+ <type>TableInput</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>${ORACLE_CONNECTION}</connection>
+ <execute_each_row>N</execute_each_row>
+ <limit>0</limit>
+ <sql>SELECT ID, ASCII_TEXT, NATIONAL_TEXT, LONG_NATIONAL
+FROM HOP_NCHAR_SOURCE
+ORDER BY ID</sql>
+ <variables_active>N</variables_active>
+ <attributes/>
+ <GUI>
+ <xloc>144</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>National text out</name>
+ <type>TableOutput</type>
+ <description>Every string column type Oracle has, written in one batch.
The commit size is larger than the row count on purpose: all three rows have to
go to the driver together for the short/long mix to be the one that used to
raise ORA-01461.</description>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <commit>1000</commit>
+ <connection>${ORACLE_CONNECTION}</connection>
+ <fields>
+ <field>
+ <column_name>ID</column_name>
+ <stream_name>ID</stream_name>
+ </field>
+ <field>
+ <column_name>VARCHAR_VALUE</column_name>
+ <stream_name>ASCII_TEXT</stream_name>
+ </field>
+ <field>
+ <column_name>NVARCHAR_VALUE</column_name>
+ <stream_name>NATIONAL_TEXT</stream_name>
+ </field>
+ <field>
+ <column_name>NCHAR_VALUE</column_name>
+ <stream_name>NATIONAL_TEXT</stream_name>
+ </field>
+ <field>
+ <column_name>NCLOB_VALUE</column_name>
+ <stream_name>LONG_NATIONAL</stream_name>
+ </field>
+ <field>
+ <column_name>CLOB_VALUE</column_name>
+ <stream_name>LONG_NATIONAL</stream_name>
+ </field>
+ <field>
+ <column_name>NCLOB_SHORT</column_name>
+ <stream_name>NATIONAL_TEXT</stream_name>
+ </field>
+ </fields>
+ <ignore_errors>N</ignore_errors>
+ <only_when_have_rows>N</only_when_have_rows>
+ <partitioning_daily>N</partitioning_daily>
+ <partitioning_enabled>N</partitioning_enabled>
+ <partitioning_field/>
+ <partitioning_monthly>Y</partitioning_monthly>
+ <return_field/>
+ <return_keys>N</return_keys>
+ <schema/>
+ <specify_fields>Y</specify_fields>
+ <table>HOP_NCHAR_TARGET</table>
+ <tablename_field/>
+ <tablename_in_field>N</tablename_in_field>
+ <tablename_in_table>Y</tablename_in_table>
+ <truncate>N</truncate>
+ <use_batch>Y</use_batch>
+ <attributes/>
+ <GUI>
+ <xloc>384</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/oracle/0009b-oversize-write.hpl
b/integration-tests/oracle/0009b-oversize-write.hpl
new file mode 100644
index 0000000000..74fc4ba28f
--- /dev/null
+++ b/integration-tests/oracle/0009b-oversize-write.hpl
@@ -0,0 +1,153 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0009b-oversize-write</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Writes the same rows into a VARCHAR2(50). The 7000 character
value does not fit, and the point of the test is that Oracle says so instead of
Hop quietly cutting the value down to 50.</description>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/09/17 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/09/17 12:00:00.000</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>Rows in</from>
+ <to>Narrow column out</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Narrow column out</from>
+ <to>Rejected</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Rows in</name>
+ <type>TableInput</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>${ORACLE_CONNECTION}</connection>
+ <execute_each_row>N</execute_each_row>
+ <limit>0</limit>
+ <sql>SELECT ID, LONG_NATIONAL
+FROM HOP_NCHAR_SOURCE
+ORDER BY ID</sql>
+ <variables_active>N</variables_active>
+ <attributes/>
+ <GUI>
+ <xloc>144</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Narrow column out</name>
+ <type>TableOutput</type>
+ <description>Row 1 carries 7000 characters into a VARCHAR2(50). Rejecting
it is the expected outcome; the rows that do fit must still land.</description>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <commit>1000</commit>
+ <connection>${ORACLE_CONNECTION}</connection>
+ <fields>
+ <field>
+ <column_name>ID</column_name>
+ <stream_name>ID</stream_name>
+ </field>
+ <field>
+ <column_name>NARROW_TEXT</column_name>
+ <stream_name>LONG_NATIONAL</stream_name>
+ </field>
+ </fields>
+ <ignore_errors>N</ignore_errors>
+ <only_when_have_rows>N</only_when_have_rows>
+ <partitioning_daily>N</partitioning_daily>
+ <partitioning_enabled>N</partitioning_enabled>
+ <partitioning_field/>
+ <partitioning_monthly>Y</partitioning_monthly>
+ <return_field/>
+ <return_keys>N</return_keys>
+ <schema/>
+ <specify_fields>Y</specify_fields>
+ <table>HOP_NCHAR_NARROW</table>
+ <tablename_field/>
+ <tablename_in_field>N</tablename_in_field>
+ <tablename_in_table>Y</tablename_in_table>
+ <truncate>N</truncate>
+ <use_batch>Y</use_batch>
+ <attributes/>
+ <GUI>
+ <xloc>384</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Rejected</name>
+ <type>Dummy</type>
+ <description>Holds the rows Oracle refused, so the pipeline finishes and
the check can look at what landed.</description>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>624</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ <error>
+ <source_transform>Narrow column out</source_transform>
+ <target_transform>Rejected</target_transform>
+ <is_enabled>Y</is_enabled>
+ <nr_valuename>ERR_COUNT</nr_valuename>
+ <descriptions_valuename>ERR_DESC</descriptions_valuename>
+ <fields_valuename>ERR_FIELDS</fields_valuename>
+ <codes_valuename>ERR_CODES</codes_valuename>
+ <max_errors/>
+ <max_pct_errors/>
+ <min_pct_rows/>
+ </error>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/oracle/dev-env-config.json
b/integration-tests/oracle/dev-env-config.json
index f83c597db9..0875fa6152 100644
--- a/integration-tests/oracle/dev-env-config.json
+++ b/integration-tests/oracle/dev-env-config.json
@@ -49,6 +49,21 @@
"name": "ORACLE_SYSTEM_PASSWORD",
"value": "HopIntegrationTest_1",
"description": "ORACLE_PWD of the test container, throwaway"
+ },
+ {
+ "name": "ORACLE_CONNECTION",
+ "value": "oracle-service-name",
+ "description": "Connection the national character pipelines write
through, so the same pipelines can run against the Unicode and the non-Unicode
database"
+ },
+ {
+ "name": "ORACLE_NCHAR_HOST",
+ "value": "oracle-nchar",
+ "description": "Host of the WE8MSWIN1252 container started by
integration-tests-oracle.yaml"
+ },
+ {
+ "name": "ORACLE_NCHAR_SERVICE",
+ "value": "FREEPDB1",
+ "description": "Service name of its pluggable database"
}
]
}
\ No newline at end of file
diff --git a/integration-tests/oracle/disabled.txt
b/integration-tests/oracle/disabled.txt
index 984d0487ee..2712e126f5 100644
--- a/integration-tests/oracle/disabled.txt
+++ b/integration-tests/oracle/disabled.txt
@@ -33,6 +33,17 @@ using "true", so the other disabled projects stay disabled:
./integration-tests/scripts/run-tests-docker.sh INCLUDE_DISABLED=oracle
KEEP_IMAGES=true
+The suite starts two databases. The second one, oracle-nchar, exists because
the national
+character tests need a database whose character set is not Unicode: writing an
NVARCHAR2 through
+setString converts the value to the database character set, which only loses
characters when that
+character set cannot hold them. 0009 runs against the Unicode database, where
the fault cannot be
+seen and the test is a regression guard; 0010 runs the same pipelines against
the non-Unicode one,
+where it reproduces the fault and fails without the national character binding.
+
+The image ships a Unicode database and only honours ORACLE_CHARACTERSET when
it builds one, so the
+second container removes the database it ships and lets it be rebuilt. That
costs several extra
+minutes on the first run; the volume is kept afterwards, so later runs only
open it.
+
The image is pulled from Oracle's container registry under the Oracle Free Use
Terms and
Conditions. It pulls anonymously - no docker login and no license token -
which is why the Free
edition is used and not Enterprise; a 403 or an authorization error means
something is pointing at
diff --git a/integration-tests/oracle/main-0009-national-character-types.hwf
b/integration-tests/oracle/main-0009-national-character-types.hwf
new file mode 100644
index 0000000000..8757f7fc58
--- /dev/null
+++ b/integration-tests/oracle/main-0009-national-character-types.hwf
@@ -0,0 +1,354 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<workflow>
+ <name>main-0009-national-character-types</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Text written into Oracle's national character types: NVARCHAR2,
NCHAR and NCLOB, next to the VARCHAR2 and CLOB that take the same values.
Writing a national column through setString loses whatever the database
character set cannot represent, and a batch mixing a short value with one
longer than a VARCHAR2 used to raise ORA-01461. Note that this only fails on a
database whose character set is not Unicode: against the AL32UTF8 of the
suite's own container setString converts [...]
+ <extended_description/>
+ <workflow_version/>
+ <created_user>-</created_user>
+ <created_date>2026/09/17 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/09/17 12:00:00.000</modified_date>
+ <parameters>
+ </parameters>
+ <actions>
+ <action>
+ <name>Start</name>
+ <description/>
+ <type>SPECIAL</type>
+ <attributes/>
+ <DayOfMonth>1</DayOfMonth>
+ <hour>12</hour>
+ <intervalMinutes>60</intervalMinutes>
+ <intervalSeconds>0</intervalSeconds>
+ <minutes>0</minutes>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <weekDay>1</weekDay>
+ <parallel>N</parallel>
+ <xloc>50</xloc>
+ <yloc>50</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Write through the Unicode connection</name>
+ <description>Set explicitly rather than left to the default, so that
this workflow is unaffected by 0010 having run before it in the same
JVM.</description>
+ <type>SET_VARIABLES</type>
+ <attributes/>
+ <replacevars>Y</replacevars>
+ <filename/>
+ <file_variable_type>JVM</file_variable_type>
+ <fields>
+ <field>
+ <variable_name>ORACLE_CONNECTION</variable_name>
+ <variable_value>oracle-service-name</variable_value>
+ <variable_type>ROOT_WORKFLOW</variable_type>
+ </field>
+ </fields>
+ <parallel>N</parallel>
+ <xloc>128</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Init tables</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <sql>DECLARE
+ table_missing EXCEPTION;
+ PRAGMA EXCEPTION_INIT(table_missing, -942);
+ -- Written as escapes rather than literal characters so that nothing here
depends on the
+ -- client's NLS settings: UNISTR builds the same seven characters whatever
the JDBC layer does.
+ japanese NVARCHAR2(20) := UNISTR('\65E5\672C\8A9E\30C6\30AD\30B9\30C8');
+ long_text NCLOB;
+ short_text NCLOB;
+BEGIN
+ BEGIN
+ EXECUTE IMMEDIATE 'DROP TABLE HOP_NCHAR_SOURCE';
+ EXCEPTION
+ WHEN table_missing THEN NULL;
+ END;
+ BEGIN
+ EXECUTE IMMEDIATE 'DROP TABLE HOP_NCHAR_TARGET';
+ EXCEPTION
+ WHEN table_missing THEN NULL;
+ END;
+ BEGIN
+ EXECUTE IMMEDIATE 'DROP TABLE HOP_NCHAR_NARROW';
+ EXCEPTION
+ WHEN table_missing THEN NULL;
+ END;
+
+ EXECUTE IMMEDIATE 'CREATE TABLE HOP_NCHAR_SOURCE (
+ ID NUMBER(10),
+ ASCII_TEXT VARCHAR2(50),
+ NATIONAL_TEXT NVARCHAR2(50),
+ LONG_NATIONAL NCLOB)';
+
+ EXECUTE IMMEDIATE 'CREATE TABLE HOP_NCHAR_NARROW (
+ ID NUMBER(10),
+ NARROW_TEXT VARCHAR2(50))';
+
+ EXECUTE IMMEDIATE 'CREATE TABLE HOP_NCHAR_TARGET (
+ ID NUMBER(10),
+ VARCHAR_VALUE VARCHAR2(50),
+ NVARCHAR_VALUE NVARCHAR2(50),
+ NCHAR_VALUE NCHAR(20),
+ NCLOB_VALUE NCLOB,
+ CLOB_VALUE CLOB,
+ NCLOB_SHORT NCLOB)';
+
+ -- 114688 characters (7 doubled 14 times). Past the 4000 a VARCHAR2 holds,
and past the ~32k a
+ -- JDBC setString will carry, so the value can only arrive if it is written
as a stream. The
+ -- batch then holds this and a short value together, which is the ORA-01461
mix.
+ long_text := TO_NCLOB(japanese);
+ FOR i IN 1..14 LOOP
+ long_text := long_text || long_text;
+ END LOOP;
+
+ short_text := TO_NCLOB(japanese);
+
+ -- The tables were made with EXECUTE IMMEDIATE, so they do not exist yet as
far as this block's
+ -- compiler is concerned. The inserts have to be dynamic too, with the text
bound rather than
+ -- written into the statement.
+ EXECUTE IMMEDIATE 'INSERT INTO HOP_NCHAR_SOURCE VALUES (1, ''ascii row
one'', :1, :2)'
+ USING japanese, long_text;
+ EXECUTE IMMEDIATE 'INSERT INTO HOP_NCHAR_SOURCE VALUES (2, NULL, NULL,
NULL)';
+ EXECUTE IMMEDIATE 'INSERT INTO HOP_NCHAR_SOURCE VALUES (3, ''ascii row
three'', :1, :2)'
+ USING japanese, short_text;
+ COMMIT;
+END;</sql>
+ <useVariableSubstitution>F</useVariableSubstitution>
+ <sqlfromfile>F</sqlfromfile>
+ <sqlfilename/>
+ <sendOneStatement>T</sendOneStatement>
+ <connection>oracle-service-name</connection>
+ <parallel>N</parallel>
+ <xloc>224</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0009-national-character-write.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+ <filename>${PROJECT_HOME}/0009-national-character-write.hpl</filename>
+ <logext/>
+ <logfile/>
+ <loglevel>Basic</loglevel>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <parallel>N</parallel>
+ <xloc>432</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0009b-oversize-write.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+ <filename>${PROJECT_HOME}/0009b-oversize-write.hpl</filename>
+ <logext/>
+ <logfile/>
+ <loglevel>Basic</loglevel>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <parallel>N</parallel>
+ <xloc>560</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>check what was stored</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <sql>DECLARE
+ found NUMBER;
+ japanese NVARCHAR2(20) := UNISTR('\65E5\672C\8A9E\30C6\30AD\30B9\30C8');
+ stored_length NUMBER;
+BEGIN
+ SELECT COUNT(*) INTO found FROM HOP_NCHAR_TARGET;
+ IF found <> 3 THEN
+ RAISE_APPLICATION_ERROR(-20090, 'expected 3 rows but found ' || found);
+ END IF;
+
+ -- The national columns have to come back as the characters that went in.
Reading them back
+ -- through setString would have left replacement characters or question
marks behind.
+ SELECT COUNT(*) INTO found
+ FROM HOP_NCHAR_TARGET
+ WHERE ID = 1
+ AND VARCHAR_VALUE = 'ascii row one'
+ AND NVARCHAR_VALUE = japanese
+ AND TRIM(NCHAR_VALUE) = japanese
+ AND TO_NCHAR(NCLOB_SHORT) = japanese;
+ IF found <> 1 THEN
+ RAISE_APPLICATION_ERROR(-20091,
+ 'the NVARCHAR2, NCHAR or short NCLOB value did not survive the write');
+ END IF;
+
+ -- The long value is the ORA-01461 case. It must arrive whole: a silent
truncation to 4000
+ -- would still leave a row behind, so the length is checked rather than the
row count.
+ SELECT LENGTH(NCLOB_VALUE) INTO stored_length FROM HOP_NCHAR_TARGET WHERE ID
= 1;
+ IF stored_length <> 114688 THEN
+ RAISE_APPLICATION_ERROR(-20092,
+ 'the NCLOB value should be 114688 characters but is ' || stored_length);
+ END IF;
+
+ SELECT LENGTH(CLOB_VALUE) INTO stored_length FROM HOP_NCHAR_TARGET WHERE ID
= 1;
+ IF stored_length <> 114688 THEN
+ RAISE_APPLICATION_ERROR(-20093,
+ 'the CLOB value should be 114688 characters but is ' || stored_length);
+ END IF;
+
+ SELECT COUNT(*) INTO found
+ FROM HOP_NCHAR_TARGET
+ WHERE ID = 1
+ AND DBMS_LOB.INSTR(NCLOB_VALUE, japanese) = 1
+ AND DBMS_LOB.INSTR(CLOB_VALUE, TO_CHAR(japanese)) = 1;
+ IF found <> 1 THEN
+ RAISE_APPLICATION_ERROR(-20094, 'the long LOB values do not start with the
text written');
+ END IF;
+
+ SELECT COUNT(*) INTO found
+ FROM HOP_NCHAR_TARGET
+ WHERE ID = 2
+ AND VARCHAR_VALUE IS NULL
+ AND NVARCHAR_VALUE IS NULL
+ AND NCHAR_VALUE IS NULL
+ AND NCLOB_VALUE IS NULL
+ AND CLOB_VALUE IS NULL
+ AND NCLOB_SHORT IS NULL;
+ IF found <> 1 THEN
+ RAISE_APPLICATION_ERROR(-20095, 'the row of nulls did not survive the
write');
+ END IF;
+
+ -- Row 3 shares the batch with row 1's long value. If the short rows come
back wrong the
+ -- binding leaked the previous row's form of use or length.
+ SELECT COUNT(*) INTO found
+ FROM HOP_NCHAR_TARGET
+ WHERE ID = 3
+ AND VARCHAR_VALUE = 'ascii row three'
+ AND NVARCHAR_VALUE = japanese
+ AND TO_NCHAR(NCLOB_VALUE) = japanese;
+ IF found <> 1 THEN
+ RAISE_APPLICATION_ERROR(-20096,
+ 'the short row sharing a batch with the long one did not survive the
write');
+ END IF;
+
+ -- A 7000 character value bound to a VARCHAR2(50). Oracle has to refuse it:
Hop passes the value
+ -- at its full length rather than cutting it to the column, so the row is
rejected and the
+ -- failure is visible. A row here with a 50 character value would mean the
overflow was dropped.
+ SELECT COUNT(*) INTO found FROM HOP_NCHAR_NARROW WHERE ID = 1;
+ IF found <> 0 THEN
+ SELECT LENGTH(NARROW_TEXT) INTO stored_length FROM HOP_NCHAR_NARROW WHERE
ID = 1;
+ RAISE_APPLICATION_ERROR(-20097,
+ 'the oversized value should have been refused but was stored, cut to '
+ || stored_length || ' characters');
+ END IF;
+
+ -- The rows that do fit still have to land, so that the rejection above is
the column being too
+ -- narrow and not the whole write failing.
+ SELECT COUNT(*) INTO found FROM HOP_NCHAR_NARROW WHERE ID IN (2, 3);
+ IF found <> 2 THEN
+ RAISE_APPLICATION_ERROR(-20098,
+ 'the rows that fit the narrow column should still be written, found ' ||
found);
+ END IF;
+END;</sql>
+ <useVariableSubstitution>F</useVariableSubstitution>
+ <sqlfromfile>F</sqlfromfile>
+ <sqlfilename/>
+ <sendOneStatement>T</sendOneStatement>
+ <connection>oracle-service-name</connection>
+ <parallel>N</parallel>
+ <xloc>688</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>Start</from>
+ <to>Write through the Unicode connection</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>Write through the Unicode connection</from>
+ <to>Init tables</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>Init tables</from>
+ <to>0009-national-character-write.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0009-national-character-write.hpl</from>
+ <to>0009b-oversize-write.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0009b-oversize-write.hpl</from>
+ <to>check what was stored</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ </hops>
+ <notepads>
+ </notepads>
+ <attributes/>
+</workflow>
diff --git a/integration-tests/oracle/main-0010-national-character-repro.hwf
b/integration-tests/oracle/main-0010-national-character-repro.hwf
new file mode 100644
index 0000000000..dd47563c22
--- /dev/null
+++ b/integration-tests/oracle/main-0010-national-character-repro.hwf
@@ -0,0 +1,354 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<workflow>
+ <name>main-0010-national-character-repro</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>The same writes as 0009, against a database whose character set
is WE8MSWIN1252 instead of AL32UTF8. This is the reproduction: writing an
NVARCHAR2, NCHAR or NCLOB through setString converts the value to the database
character set on the way in, and a character set that cannot hold Japanese
loses it. Without the national character binding this workflow fails on the
first check; with it every value survives. 0009 runs the same pipelines against
the Unicode database, where s [...]
+ <extended_description/>
+ <workflow_version/>
+ <created_user>-</created_user>
+ <created_date>2026/09/17 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/09/17 12:00:00.000</modified_date>
+ <parameters>
+ </parameters>
+ <actions>
+ <action>
+ <name>Start</name>
+ <description/>
+ <type>SPECIAL</type>
+ <attributes/>
+ <DayOfMonth>1</DayOfMonth>
+ <hour>12</hour>
+ <intervalMinutes>60</intervalMinutes>
+ <intervalSeconds>0</intervalSeconds>
+ <minutes>0</minutes>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <weekDay>1</weekDay>
+ <parallel>N</parallel>
+ <xloc>50</xloc>
+ <yloc>50</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Write through the non-Unicode connection</name>
+ <description>The pipelines take their connection from this variable, so
0009 and 0010 can share them.</description>
+ <type>SET_VARIABLES</type>
+ <attributes/>
+ <replacevars>Y</replacevars>
+ <filename/>
+ <file_variable_type>JVM</file_variable_type>
+ <fields>
+ <field>
+ <variable_name>ORACLE_CONNECTION</variable_name>
+ <variable_value>oracle-nchar</variable_value>
+ <variable_type>ROOT_WORKFLOW</variable_type>
+ </field>
+ </fields>
+ <parallel>N</parallel>
+ <xloc>128</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Init tables</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <sql>DECLARE
+ table_missing EXCEPTION;
+ PRAGMA EXCEPTION_INIT(table_missing, -942);
+ -- Written as escapes rather than literal characters so that nothing here
depends on the
+ -- client's NLS settings: UNISTR builds the same seven characters whatever
the JDBC layer does.
+ japanese NVARCHAR2(20) := UNISTR('\65E5\672C\8A9E\30C6\30AD\30B9\30C8');
+ long_text NCLOB;
+ short_text NCLOB;
+BEGIN
+ BEGIN
+ EXECUTE IMMEDIATE 'DROP TABLE HOP_NCHAR_SOURCE';
+ EXCEPTION
+ WHEN table_missing THEN NULL;
+ END;
+ BEGIN
+ EXECUTE IMMEDIATE 'DROP TABLE HOP_NCHAR_TARGET';
+ EXCEPTION
+ WHEN table_missing THEN NULL;
+ END;
+ BEGIN
+ EXECUTE IMMEDIATE 'DROP TABLE HOP_NCHAR_NARROW';
+ EXCEPTION
+ WHEN table_missing THEN NULL;
+ END;
+
+ EXECUTE IMMEDIATE 'CREATE TABLE HOP_NCHAR_SOURCE (
+ ID NUMBER(10),
+ ASCII_TEXT VARCHAR2(50),
+ NATIONAL_TEXT NVARCHAR2(50),
+ LONG_NATIONAL NCLOB)';
+
+ EXECUTE IMMEDIATE 'CREATE TABLE HOP_NCHAR_NARROW (
+ ID NUMBER(10),
+ NARROW_TEXT VARCHAR2(50))';
+
+ EXECUTE IMMEDIATE 'CREATE TABLE HOP_NCHAR_TARGET (
+ ID NUMBER(10),
+ VARCHAR_VALUE VARCHAR2(50),
+ NVARCHAR_VALUE NVARCHAR2(50),
+ NCHAR_VALUE NCHAR(20),
+ NCLOB_VALUE NCLOB,
+ CLOB_VALUE CLOB,
+ NCLOB_SHORT NCLOB)';
+
+ -- 114688 characters (7 doubled 14 times). Past the 4000 a VARCHAR2 holds,
and past the ~32k a
+ -- JDBC setString will carry, so the value can only arrive if it is written
as a stream. The
+ -- batch then holds this and a short value together, which is the ORA-01461
mix.
+ long_text := TO_NCLOB(japanese);
+ FOR i IN 1..14 LOOP
+ long_text := long_text || long_text;
+ END LOOP;
+
+ short_text := TO_NCLOB(japanese);
+
+ -- The tables were made with EXECUTE IMMEDIATE, so they do not exist yet as
far as this block's
+ -- compiler is concerned. The inserts have to be dynamic too, with the text
bound rather than
+ -- written into the statement.
+ EXECUTE IMMEDIATE 'INSERT INTO HOP_NCHAR_SOURCE VALUES (1, ''ascii row
one'', :1, :2)'
+ USING japanese, long_text;
+ EXECUTE IMMEDIATE 'INSERT INTO HOP_NCHAR_SOURCE VALUES (2, NULL, NULL,
NULL)';
+ EXECUTE IMMEDIATE 'INSERT INTO HOP_NCHAR_SOURCE VALUES (3, ''ascii row
three'', :1, :2)'
+ USING japanese, short_text;
+ COMMIT;
+END;</sql>
+ <useVariableSubstitution>F</useVariableSubstitution>
+ <sqlfromfile>F</sqlfromfile>
+ <sqlfilename/>
+ <sendOneStatement>T</sendOneStatement>
+ <connection>oracle-nchar</connection>
+ <parallel>N</parallel>
+ <xloc>224</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0009-national-character-write.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+ <filename>${PROJECT_HOME}/0009-national-character-write.hpl</filename>
+ <logext/>
+ <logfile/>
+ <loglevel>Basic</loglevel>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <parallel>N</parallel>
+ <xloc>432</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0009b-oversize-write.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+ <filename>${PROJECT_HOME}/0009b-oversize-write.hpl</filename>
+ <logext/>
+ <logfile/>
+ <loglevel>Basic</loglevel>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <parallel>N</parallel>
+ <xloc>560</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>check what was stored</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <sql>DECLARE
+ found NUMBER;
+ japanese NVARCHAR2(20) := UNISTR('\65E5\672C\8A9E\30C6\30AD\30B9\30C8');
+ stored_length NUMBER;
+BEGIN
+ SELECT COUNT(*) INTO found FROM HOP_NCHAR_TARGET;
+ IF found <> 3 THEN
+ RAISE_APPLICATION_ERROR(-20090, 'expected 3 rows but found ' || found);
+ END IF;
+
+ -- The national columns have to come back as the characters that went in.
Reading them back
+ -- through setString would have left replacement characters or question
marks behind.
+ SELECT COUNT(*) INTO found
+ FROM HOP_NCHAR_TARGET
+ WHERE ID = 1
+ AND VARCHAR_VALUE = 'ascii row one'
+ AND NVARCHAR_VALUE = japanese
+ AND TRIM(NCHAR_VALUE) = japanese
+ AND TO_NCHAR(NCLOB_SHORT) = japanese;
+ IF found <> 1 THEN
+ RAISE_APPLICATION_ERROR(-20091,
+ 'the NVARCHAR2, NCHAR or short NCLOB value did not survive the write');
+ END IF;
+
+ -- The long value is the ORA-01461 case. It must arrive whole: a silent
truncation to 4000
+ -- would still leave a row behind, so the length is checked rather than the
row count.
+ SELECT LENGTH(NCLOB_VALUE) INTO stored_length FROM HOP_NCHAR_TARGET WHERE ID
= 1;
+ IF stored_length <> 114688 THEN
+ RAISE_APPLICATION_ERROR(-20092,
+ 'the NCLOB value should be 114688 characters but is ' || stored_length);
+ END IF;
+
+ SELECT LENGTH(CLOB_VALUE) INTO stored_length FROM HOP_NCHAR_TARGET WHERE ID
= 1;
+ IF stored_length <> 114688 THEN
+ RAISE_APPLICATION_ERROR(-20093,
+ 'the CLOB value should be 114688 characters but is ' || stored_length);
+ END IF;
+
+ SELECT COUNT(*) INTO found
+ FROM HOP_NCHAR_TARGET
+ WHERE ID = 1
+ AND DBMS_LOB.INSTR(NCLOB_VALUE, japanese) = 1
+ AND DBMS_LOB.INSTR(CLOB_VALUE, TO_CHAR(japanese)) = 1;
+ IF found <> 1 THEN
+ RAISE_APPLICATION_ERROR(-20094, 'the long LOB values do not start with the
text written');
+ END IF;
+
+ SELECT COUNT(*) INTO found
+ FROM HOP_NCHAR_TARGET
+ WHERE ID = 2
+ AND VARCHAR_VALUE IS NULL
+ AND NVARCHAR_VALUE IS NULL
+ AND NCHAR_VALUE IS NULL
+ AND NCLOB_VALUE IS NULL
+ AND CLOB_VALUE IS NULL
+ AND NCLOB_SHORT IS NULL;
+ IF found <> 1 THEN
+ RAISE_APPLICATION_ERROR(-20095, 'the row of nulls did not survive the
write');
+ END IF;
+
+ -- Row 3 shares the batch with row 1's long value. If the short rows come
back wrong the
+ -- binding leaked the previous row's form of use or length.
+ SELECT COUNT(*) INTO found
+ FROM HOP_NCHAR_TARGET
+ WHERE ID = 3
+ AND VARCHAR_VALUE = 'ascii row three'
+ AND NVARCHAR_VALUE = japanese
+ AND TO_NCHAR(NCLOB_VALUE) = japanese;
+ IF found <> 1 THEN
+ RAISE_APPLICATION_ERROR(-20096,
+ 'the short row sharing a batch with the long one did not survive the
write');
+ END IF;
+
+ -- A 7000 character value bound to a VARCHAR2(50). Oracle has to refuse it:
Hop passes the value
+ -- at its full length rather than cutting it to the column, so the row is
rejected and the
+ -- failure is visible. A row here with a 50 character value would mean the
overflow was dropped.
+ SELECT COUNT(*) INTO found FROM HOP_NCHAR_NARROW WHERE ID = 1;
+ IF found <> 0 THEN
+ SELECT LENGTH(NARROW_TEXT) INTO stored_length FROM HOP_NCHAR_NARROW WHERE
ID = 1;
+ RAISE_APPLICATION_ERROR(-20097,
+ 'the oversized value should have been refused but was stored, cut to '
+ || stored_length || ' characters');
+ END IF;
+
+ -- The rows that do fit still have to land, so that the rejection above is
the column being too
+ -- narrow and not the whole write failing.
+ SELECT COUNT(*) INTO found FROM HOP_NCHAR_NARROW WHERE ID IN (2, 3);
+ IF found <> 2 THEN
+ RAISE_APPLICATION_ERROR(-20098,
+ 'the rows that fit the narrow column should still be written, found ' ||
found);
+ END IF;
+END;</sql>
+ <useVariableSubstitution>F</useVariableSubstitution>
+ <sqlfromfile>F</sqlfromfile>
+ <sqlfilename/>
+ <sendOneStatement>T</sendOneStatement>
+ <connection>oracle-nchar</connection>
+ <parallel>N</parallel>
+ <xloc>688</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>Start</from>
+ <to>Write through the non-Unicode connection</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>Write through the non-Unicode connection</from>
+ <to>Init tables</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>Init tables</from>
+ <to>0009-national-character-write.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0009-national-character-write.hpl</from>
+ <to>0009b-oversize-write.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0009b-oversize-write.hpl</from>
+ <to>check what was stored</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ </hops>
+ <notepads>
+ </notepads>
+ <attributes/>
+</workflow>
diff --git a/integration-tests/oracle/metadata/rdbms/oracle-nchar.json
b/integration-tests/oracle/metadata/rdbms/oracle-nchar.json
new file mode 100644
index 0000000000..94491153b2
--- /dev/null
+++ b/integration-tests/oracle/metadata/rdbms/oracle-nchar.json
@@ -0,0 +1,27 @@
+{
+ "rdbms": {
+ "ORACLE": {
+ "pluginId": "ORACLE",
+ "pluginName": "Oracle",
+ "accessType": 0,
+ "hostname": "${ORACLE_NCHAR_HOST}",
+ "port": "${ORACLE_PORT}",
+ "databaseName": "${ORACLE_NCHAR_SERVICE}",
+ "username": "${ORACLE_USER}",
+ "password": "${ORACLE_PASSWORD}",
+ "connectionType": "SERVICE_NAME",
+ "manualUrl": "",
+ "attributes": {
+ "SUPPORTS_TIMESTAMP_DATA_TYPE": "Y",
+ "QUOTE_ALL_FIELDS": "N",
+ "SUPPORTS_BOOLEAN_DATA_TYPE": "Y",
+ "FORCE_IDENTIFIERS_TO_LOWERCASE": "N",
+ "PRESERVE_RESERVED_WORD_CASE": "Y",
+ "SQL_CONNECT": "",
+ "FORCE_IDENTIFIERS_TO_UPPERCASE": "N",
+ "PREFERRED_SCHEMA_NAME": ""
+ }
+ }
+ },
+ "name": "oracle-nchar"
+}
\ No newline at end of file
diff --git
a/plugins/databases/oracle/src/main/java/org/apache/hop/databases/oracle/OracleDatabaseMeta.java
b/plugins/databases/oracle/src/main/java/org/apache/hop/databases/oracle/OracleDatabaseMeta.java
index 083545cdfc..0d884801f9 100644
---
a/plugins/databases/oracle/src/main/java/org/apache/hop/databases/oracle/OracleDatabaseMeta.java
+++
b/plugins/databases/oracle/src/main/java/org/apache/hop/databases/oracle/OracleDatabaseMeta.java
@@ -116,15 +116,28 @@ public class OracleDatabaseMeta extends BaseDatabaseMeta
.as(v -> v.getLength() > 0 ? "VECTOR(" + v.getLength() + ",
FLOAT32)" : "VECTOR(*, *)")
.build();
+ /**
+ * Oracle will not take an NVARCHAR2, NCHAR or NCLOB through {@code
setString} without converting
+ * the value to the database character set, and a batch into a CLOB that
mixes short and long
+ * values raises ORA-01461. Every string is bound through {@link
OraclePreparedStatementBinding},
+ * which asks the statement which column it is writing to and picks the JDBC
call for it.
+ */
+ private static final List<IDatabaseTypeRule> STRING_BINDING =
+ DatabaseTypes.rules()
+ .bind(IValueMeta.TYPE_STRING,
OraclePreparedStatementBinding.INSTANCE)
+ .build();
+
@Override
public List<IDatabaseTypeRule> getTypeRules() {
// A 38 digit number is an integer unless this connection asked for the
strict reading. That
// option used to sit on the interface every dialect implements; it is
Oracle's own.
- List<IDatabaseTypeRule> rules = new ArrayList<>(RAW_RULES.size() +
JSON_RULES.size() + 1);
+ List<IDatabaseTypeRule> rules =
+ new ArrayList<>(RAW_RULES.size() + JSON_RULES.size() +
STRING_BINDING.size() + 1);
rules.addAll(isStrictBigNumberInterpretation() ? NUMBER_38_AS_BIGNUMBER :
NUMBER_38_AS_INTEGER);
rules.addAll(RAW_RULES);
rules.addAll(JSON_RULES);
rules.addAll(VECTOR_RULES);
+ rules.addAll(STRING_BINDING);
return rules;
}
diff --git
a/plugins/databases/oracle/src/main/java/org/apache/hop/databases/oracle/OraclePreparedStatementBinding.java
b/plugins/databases/oracle/src/main/java/org/apache/hop/databases/oracle/OraclePreparedStatementBinding.java
new file mode 100644
index 0000000000..8dcdbe4d00
--- /dev/null
+++
b/plugins/databases/oracle/src/main/java/org/apache/hop/databases/oracle/OraclePreparedStatementBinding.java
@@ -0,0 +1,174 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.oracle;
+
+import java.io.StringReader;
+import java.sql.ParameterMetaData;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.util.Collections;
+import java.util.Locale;
+import java.util.Map;
+import java.util.WeakHashMap;
+import org.apache.hop.core.database.IDatabase;
+import org.apache.hop.core.database.types.IValueBinding;
+import org.apache.hop.core.exception.HopValueException;
+import org.apache.hop.core.row.IValueMeta;
+
+/**
+ * How Oracle writes strings: the national-character columns
(NVARCHAR2/NCHAR/NCLOB) that the driver
+ * will not accept through {@code setString} without converting them to the
database character set,
+ * and the LOB columns that need a stream bind so a batch mixing short and
long values does not
+ * raise ORA-01461.
+ *
+ * <p>Which column a value is bound to is not something the value's own
metadata knows -- a string
+ * is a string whether it is going into a VARCHAR2 or an NVARCHAR2 -- but the
statement does. The
+ * Oracle driver parses the SQL behind {@link
PreparedStatement#getParameterMetaData()} and
+ * describes the target columns, so the column type of every bind parameter is
asked from the
+ * statement itself, once, and kept for as long as the statement lives. A
driver that cannot say (an
+ * old one, or a statement it cannot parse) leaves the value written the way
Hop always wrote it,
+ * with {@code setString}.
+ *
+ * <p>Write only. {@link #read} throws, so reading a string off an Oracle
result set stays exactly
+ * what it was before this binding existed -- see {@link
+ * org.apache.hop.core.database.BaseDatabaseMeta#getValueFromResultSet}.
Nothing was wrong with
+ * reads.
+ */
+final class OraclePreparedStatementBinding implements IValueBinding {
+
+ static final OraclePreparedStatementBinding INSTANCE = new
OraclePreparedStatementBinding();
+
+ /** What the statement could not tell us: bound the way Hop always bound a
string. */
+ private static final int UNKNOWN_COLUMN_TYPE = Types.OTHER;
+
+ /**
+ * The column types of every statement this binding has written to, by
statement identity. The
+ * driver caches parameter metadata per SQL text as well, but asking it
costs a lock and a lookup
+ * per value, and this is the innermost loop of every Oracle insert. Entries
go when the statement
+ * is collected.
+ */
+ private static final Map<PreparedStatement, int[]> COLUMN_TYPES =
+ Collections.synchronizedMap(new WeakHashMap<>());
+
+ private OraclePreparedStatementBinding() {}
+
+ /**
+ * Declining to read costs an exception per value, and unlike the bindings
that decline a JSON or
+ * a date column this one is asked about every string Oracle reads -- the
innermost loop there is.
+ * So it is thrown without a stack trace nobody looks at, and without
allocating.
+ */
+ private static final class ReadNotSupported extends
UnsupportedOperationException {
+ private ReadNotSupported() {
+ super("This binding only writes values");
+ }
+
+ @Override
+ public synchronized Throwable fillInStackTrace() {
+ return this;
+ }
+ }
+
+ private static final ReadNotSupported READ_NOT_SUPPORTED = new
ReadNotSupported();
+
+ @Override
+ public Object read(IDatabase database, IValueMeta valueMeta, ResultSet
resultSet, int index) {
+ // Declared for writing only. The caller falls back to the value type's
own reading.
+ throw READ_NOT_SUPPORTED;
+ }
+
+ @Override
+ public void write(
+ IDatabase database,
+ IValueMeta valueMeta,
+ PreparedStatement preparedStatement,
+ int index,
+ Object value)
+ throws SQLException, HopValueException {
+ if (valueMeta.isNull(value)) {
+ // What ValueMetaBase does for a null string, kept identical.
+ preparedStatement.setNull(index, Types.VARCHAR);
+ return;
+ }
+ String string = valueMeta.getString(value);
+ switch (columnType(preparedStatement, index)) {
+ case Types.NCLOB ->
+ preparedStatement.setNCharacterStream(
+ index, new StringReader(string), (long) string.length());
+ case Types.CLOB ->
+ preparedStatement.setCharacterStream(
+ index, new StringReader(string), (long) string.length());
+ case Types.NCHAR, Types.NVARCHAR, Types.LONGNVARCHAR ->
+ preparedStatement.setNString(index, string);
+ default -> preparedStatement.setString(index, string);
+ }
+ }
+
+ /** The JDBC type of the column behind one bind parameter, or {@link
#UNKNOWN_COLUMN_TYPE}. */
+ static int columnType(PreparedStatement preparedStatement, int index) {
+ int[] types = COLUMN_TYPES.get(preparedStatement);
+ if (types == null) {
+ types = describeColumns(preparedStatement);
+ COLUMN_TYPES.put(preparedStatement, types);
+ }
+ return index >= 1 && index <= types.length ? types[index - 1] :
UNKNOWN_COLUMN_TYPE;
+ }
+
+ /**
+ * Asks the statement for the column type of each of its parameters.
Whatever the driver cannot
+ * answer -- a driver too old to describe binds, a statement its parser does
not understand, a
+ * parameter it has no type for -- is recorded as unknown, so the question
is asked once per
+ * statement whatever the outcome.
+ */
+ private static int[] describeColumns(PreparedStatement preparedStatement) {
+ try {
+ ParameterMetaData metaData = preparedStatement.getParameterMetaData();
+ int[] types = new int[metaData.getParameterCount()];
+ for (int i = 0; i < types.length; i++) {
+ types[i] = columnType(metaData, i + 1);
+ }
+ return types;
+ } catch (SQLException | RuntimeException e) {
+ return new int[0];
+ }
+ }
+
+ private static int columnType(ParameterMetaData metaData, int parameter) {
+ try {
+ int type = metaData.getParameterType(parameter);
+ // The driver reports the national types by their own JDBC codes, but a
type name is the
+ // safer of the two answers when both are there.
+ String typeName = metaData.getParameterTypeName(parameter);
+ if (typeName != null) {
+ switch (typeName.toUpperCase(Locale.ROOT)) {
+ case "NCLOB" -> type = Types.NCLOB;
+ case "CLOB" -> type = Types.CLOB;
+ case "NCHAR" -> type = Types.NCHAR;
+ case "NVARCHAR2" -> type = Types.NVARCHAR;
+ default -> {
+ // Trust the code.
+ }
+ }
+ }
+ return type;
+ } catch (SQLException | RuntimeException e) {
+ return UNKNOWN_COLUMN_TYPE;
+ }
+ }
+}
diff --git
a/plugins/databases/oracle/src/test/java/org/apache/hop/databases/oracle/OraclePreparedStatementBindingTest.java
b/plugins/databases/oracle/src/test/java/org/apache/hop/databases/oracle/OraclePreparedStatementBindingTest.java
new file mode 100644
index 0000000000..c5dca736ee
--- /dev/null
+++
b/plugins/databases/oracle/src/test/java/org/apache/hop/databases/oracle/OraclePreparedStatementBindingTest.java
@@ -0,0 +1,255 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.oracle;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+import static org.junit.jupiter.api.Assertions.assertThrows;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.anyInt;
+import static org.mockito.ArgumentMatchers.anyLong;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.never;
+import static org.mockito.Mockito.times;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
+
+import java.sql.ParameterMetaData;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Types;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.types.DatabaseTypeMapper;
+import org.apache.hop.core.database.types.IValueBinding;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.mockito.ArgumentCaptor;
+
+/**
+ * Which JDBC call Oracle uses to write a string, per column type.
+ *
+ * <p>The column type comes from the statement's own parameter metadata, which
is what the Oracle
+ * driver fills in by parsing the SQL and describing the target table. Every
case goes through
+ * {@link DatabaseTypeMapper#getBinding}, the same lookup {@code
Database.setValue} does, so these
+ * also cover the dialect actually declaring the binding.
+ */
+class OraclePreparedStatementBindingTest {
+
+ private static final String LOG_FIELD = "LOG_FIELD";
+
+ private OracleDatabaseMeta oracleDatabaseMeta;
+ private PreparedStatement preparedStatementMock;
+ private ParameterMetaData parameterMetaData;
+
+ @BeforeEach
+ void setUp() throws SQLException {
+ oracleDatabaseMeta = new OracleDatabaseMeta();
+ preparedStatementMock = mock(PreparedStatement.class);
+ parameterMetaData = mock(ParameterMetaData.class);
+
when(preparedStatementMock.getParameterMetaData()).thenReturn(parameterMetaData);
+ when(parameterMetaData.getParameterCount()).thenReturn(1);
+ }
+
+ /** What the driver describes parameter 1 as. */
+ private void column(int sqlType, String typeName) throws SQLException {
+ when(parameterMetaData.getParameterType(1)).thenReturn(sqlType);
+ when(parameterMetaData.getParameterTypeName(1)).thenReturn(typeName);
+ }
+
+ /** Binds through the declared rule, the way the insert path reaches it. */
+ private void write(IValueMeta valueMeta, Object value) throws Exception {
+ IValueBinding binding = DatabaseTypeMapper.getBinding(oracleDatabaseMeta,
valueMeta);
+ assertNotNull(binding, "Oracle should declare a binding for strings");
+ binding.write(oracleDatabaseMeta, valueMeta, preparedStatementMock, 1,
value);
+ }
+
+ @Test
+ void testVarchar2UsesSetString() throws Exception {
+ column(Types.VARCHAR, "VARCHAR2");
+ String data = StringUtils.repeat("*", 10);
+ write(new ValueMetaString(LOG_FIELD, 20, 0), data);
+
+ verify(preparedStatementMock, times(1)).setString(1, data);
+ verify(preparedStatementMock, never()).setNString(anyInt(), any());
+ verify(preparedStatementMock, never()).setCharacterStream(anyInt(), any(),
anyLong());
+ }
+
+ @Test
+ void testNvarchar2UsesSetNString() throws Exception {
+ column(Types.NVARCHAR, "NVARCHAR2");
+ String data = StringUtils.repeat("*", 10);
+ write(new ValueMetaString(LOG_FIELD, 20, 0), data);
+
+ verify(preparedStatementMock, times(1)).setNString(1, data);
+ verify(preparedStatementMock, never()).setString(anyInt(), any());
+ verify(preparedStatementMock, never()).setCharacterStream(anyInt(), any(),
anyLong());
+ }
+
+ @Test
+ void testNcharUsesSetNString() throws Exception {
+ column(Types.NCHAR, "NCHAR");
+ String data = "ab";
+ write(new ValueMetaString(LOG_FIELD, 2, 0), data);
+
+ verify(preparedStatementMock, times(1)).setNString(1, data);
+ }
+
+ /** A driver that reports the national type only by name is still
understood. */
+ @Test
+ void testNationalTypeNameOverridesAVarcharCode() throws Exception {
+ column(Types.VARCHAR, "NVARCHAR2");
+ String data = StringUtils.repeat("*", 10);
+ write(new ValueMetaString(LOG_FIELD, 20, 0), data);
+
+ verify(preparedStatementMock, times(1)).setNString(1, data);
+ }
+
+ @Test
+ void testClobUsesCharacterStream() throws Exception {
+ column(Types.CLOB, "CLOB");
+ String data = StringUtils.repeat("*", 10);
+ write(new ValueMetaString(LOG_FIELD, DatabaseMeta.CLOB_LENGTH, 0), data);
+
+ verify(preparedStatementMock, times(1)).setCharacterStream(anyInt(),
any(), anyLong());
+ verify(preparedStatementMock, never()).setString(anyInt(), any());
+ }
+
+ @Test
+ void testNclobUsesNCharacterStream() throws Exception {
+ column(Types.NCLOB, "NCLOB");
+ String data = StringUtils.repeat("*", 10);
+ write(new ValueMetaString(LOG_FIELD, DatabaseMeta.CLOB_LENGTH, 0), data);
+
+ verify(preparedStatementMock, times(1)).setNCharacterStream(anyInt(),
any(), anyLong());
+ verify(preparedStatementMock, never()).setString(anyInt(), any());
+ }
+
+ /** The same for a LOB, where the column size Oracle reports is 4000
whatever the value holds. */
+ @Test
+ void testLongClobIsWrittenWhole() throws Exception {
+ column(Types.NCLOB, "NCLOB");
+ String data = StringUtils.repeat("*", 7000);
+ write(new ValueMetaString(LOG_FIELD, 4000, 0), data);
+
+ ArgumentCaptor<Long> length = ArgumentCaptor.forClass(Long.class);
+ verify(preparedStatementMock).setNCharacterStream(anyInt(), any(),
length.capture());
+ assertEquals(7000L, length.getValue());
+ }
+
+ /**
+ * The value is written whole, however wide the column is: fitting it is
Oracle's business, and it
+ * says so with ORA-12899 rather than having Hop quietly drop the overflow.
+ */
+ @Test
+ void testValuesAreWrittenWholeRatherThanCutToTheColumnWidth() throws
Exception {
+ column(Types.NVARCHAR, "NVARCHAR2");
+ String data = StringUtils.repeat("*", 100);
+ write(new ValueMetaString(LOG_FIELD, 20, 0), data);
+
+ verify(preparedStatementMock, times(1)).setNString(1, data);
+ }
+
+ /** A null is still a null: the binding does what ValueMetaBase did, rather
than streaming it. */
+ @Test
+ void testNullBindsAsNullVarchar() throws Exception {
+ column(Types.NVARCHAR, "NVARCHAR2");
+ write(new ValueMetaString(LOG_FIELD, 20, 0), null);
+
+ verify(preparedStatementMock, times(1)).setNull(1, Types.VARCHAR);
+ verify(preparedStatementMock, never()).setNString(anyInt(), any());
+ }
+
+ /**
+ * A driver that cannot describe its parameters -- too old, or a statement
its parser does not
+ * take -- leaves the value written the way Hop always wrote it. A value out
of a CLOB carries
+ * CLOB_LENGTH wherever it is going; with no column type to say otherwise
there is no reason to
+ * believe the target is a LOB, and streaming into a VARCHAR2 is what raises
ORA-01461 on a mixed
+ * batch.
+ */
+ @Test
+ void testUnknownColumnTypeFallsBackToSetString() throws Exception {
+ when(preparedStatementMock.getParameterMetaData())
+ .thenThrow(new SQLException("Unsupported feature"));
+ String data = StringUtils.repeat("*", 10);
+ write(new ValueMetaString(LOG_FIELD, DatabaseMeta.CLOB_LENGTH, 0), data);
+
+ verify(preparedStatementMock, times(1)).setString(1, data);
+ verify(preparedStatementMock, never()).setCharacterStream(anyInt(), any(),
anyLong());
+ verify(preparedStatementMock, never()).setNCharacterStream(anyInt(),
any(), anyLong());
+ }
+
+ /** A parameter the driver has no type for is bound as before; the others
are unaffected. */
+ @Test
+ void testAParameterWithoutATypeFallsBackToSetString() throws Exception {
+ when(parameterMetaData.getParameterType(1)).thenThrow(new SQLException("no
type"));
+ String data = StringUtils.repeat("*", 10);
+ write(new ValueMetaString(LOG_FIELD, 20, 0), data);
+
+ verify(preparedStatementMock, times(1)).setString(1, data);
+ }
+
+ /** The statement is described once, however many rows go through it. */
+ @Test
+ void testColumnTypesAreAskedOncePerStatement() throws Exception {
+ column(Types.NVARCHAR, "NVARCHAR2");
+ IValueMeta valueMeta = new ValueMetaString(LOG_FIELD, 20, 0);
+ write(valueMeta, "one");
+ write(valueMeta, "two");
+ write(valueMeta, "three");
+
+ verify(preparedStatementMock, times(1)).getParameterMetaData();
+ verify(parameterMetaData, times(1)).getParameterType(1);
+ verify(preparedStatementMock, times(3)).setNString(anyInt(), any());
+ }
+
+ /** So is a failed description: an old driver is not asked again for every
row. */
+ @Test
+ void testAFailedDescriptionIsNotRetriedPerRow() throws Exception {
+ when(preparedStatementMock.getParameterMetaData())
+ .thenThrow(new SQLException("Unsupported feature"));
+ IValueMeta valueMeta = new ValueMetaString(LOG_FIELD, 20, 0);
+ write(valueMeta, "one");
+ write(valueMeta, "two");
+
+ verify(preparedStatementMock, times(1)).getParameterMetaData();
+ verify(preparedStatementMock, times(2)).setString(anyInt(), any());
+ }
+
+ /**
+ * Reading was never broken, so the binding declines it and the caller falls
back to the value
+ * type's own handling.
+ */
+ @Test
+ void testReadingIsLeftToTheValueType() {
+ IValueBinding binding =
+ DatabaseTypeMapper.getBinding(oracleDatabaseMeta, new
ValueMetaString(LOG_FIELD, 20, 0));
+ assertNotNull(binding);
+ assertThrows(
+ UnsupportedOperationException.class,
+ () ->
+ binding.read(
+ oracleDatabaseMeta,
+ new ValueMetaString(LOG_FIELD, 20, 0),
+ mock(ResultSet.class),
+ 1));
+ }
+}
