CODEWITHSUNDEEP
asp.netoracle-databaseora-01830
Khizer Jalal
Khizer Jalal

Reputation: 71

Oracle Query Works on my local pc but give exception of ORA: 01830 on other machine

I have a simple sql insert statement that is a part of a asp.net web application which is working fine on my Local computer, However after I deployed the functionality to the stage environment there are problems with the SQL Queries. The database being used is Oracle.

The problem appears when saving an entry with dateType format in to a table. Below is the query:

"insert into cpm2cpm_project_control values ('Test', 'test', 'test', 'test', 'STAGE', 'test123', 'test123', to_date('2013-11-19','yyyy-mm-dd'))"

Now this query works fine on my Local pc, which is pointing to the same database as the Stage environment, but it’s not being executed at the stage environment. The exception that I get on stage is as below:

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

So It’s hard to find the actual problem when the same query works on one environment and not on other. Can anyone please help on it?

Upvotes: 0

Views: 307

Answers (2)

Khizer Jalal
Khizer Jalal

Reputation: 71

Thanks for the suggestion. I have Sorted the problem. Its a strange behavior of Oracle Client. Oracle Client behaves differently on x86 bit machine and x64 machine. Also i think that the versions of Oracle client providers also change the behavior of query compilation.

Setting up same providers fixed the problem.

Thanks again.

Best Wishes

Khizer Jalal

Upvotes: 0

bastos.sergio
bastos.sergio

Reputation: 6764

You should always define the column names when using an INSERT INTO statement.

This is because if the columns are not aligned exactly (i.e. a table in staging is somehow different from your local table) you run the risk of inserting invalid values into the columns...

Change your query into something like this (edited for better visualization):

INSERT INTO cpm2cpm_project_control (column1,column2,column3,...)
VALUES ('Test', 'test', 'test', 'test', 'STAGE', 
        'test123', 'test123', to_date('2013-11-19','yyyy-mm-dd'))

Upvotes: 2

Related Questions