ORA-01830: date format picture ends before convert...

Kaviprakash · ‎11-15-2024

Hi,

Recently we are migrating our hive metastore workloads to unity catalog. As part of which, se are running into an following error with 15.4 DBR (UC) version where as it's working with 10.4 DBR (Hive). The requirement is to read the data from a table in oracle on premise and write to the external delta table.

Please note that there is a DATE column in the oracle table which is populated with Datetime values.

ORA-01830: date format picture ends before converting entire input string

spark query:

df= spark.read.format("jdbc")\

.option("driver", jdbc_driver)\

.option("url", jdbc_url)\

.option("dbtable", sql)\

.option("user", oracle_user)\

.option("password", oracle_pwd)\

.option("lowerBound", minValue)\

.option("upperBound", maxValue)\

.option("numPartitions", number_of_partitions)\

.option("partitionColumn", read_partition_column)\

.option("fetchSize", fetch_size)\

.option("oracle.net.encryption_client", "REQUIRED")\

.option("oracle.net.crypto_checksum_client", "REQUIRED")\

.option("oracle.jdbc.timezoneAsRegion", False)\

.option("oracle.jdbc.mapDateToTimestamp", True)\

.option("sessionInitStatement", "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'")\

.load()

VZLA · ‎12-11-2024

@Kaviprakash thanks for your question!

Is this also probably specific to a cluster type ? Shared vs Single user?

If Shared Mode, can you please try restarting your cluster with the following Spark Configuration:
spark.connect.perserveOptionCasing true