0

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?

CharlesB
  • 86,532
  • 28
  • 194
  • 218
Khizer Jalal
  • 51
  • 3

2 Answers2

2

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'))
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
bastos.sergio
  • 6,684
  • 4
  • 26
  • 36
  • Hi. THanks for the quick response. I tried adding the column names but the problem remains. Any more suggestions? Khizer J – Khizer Jalal Nov 20 '13 at 12:31
0

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

Khizer Jalal
  • 51
  • 3