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

Kaviprakash — Fri, 15 Nov 2024 21:06:04 GMT

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()

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

VZLA — Wed, 11 Dec 2024 18:46:28 GMT

@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

topic ORA-01830: date format picture ends before converting entire input string in Data Engineering

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

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