Home » Developer & Programmer » Precompilers, OCI & OCCI » Error Pro*C - while trying to insert date using host variables
Error Pro*C - while trying to insert date using host variables [message #209295] Thu, 14 December 2006 02:29 Go to next message
adarshmg
Messages: 2
Registered: December 2006
Junior Member

Hi,

I am trying to develop a Pro*C program and i have to insert a date into a table tmpprice_table.

I have declared the values as below..

exec sql begin declare section;
varchar h_price_date [13],
h_benchmark_matdat [13];
exec sql end declare section;


and this is the statement i use for insertion.

exec sql INSERT INTO tmpprice_table( price_date,
benchmark_matdat)
VALUES ( to_date(:h_price_date,'DD-MON-YY'),
to_date(:h_benchmark_matdat,'DD-MON-YY'),
);

the values in the host variables are
h_price_date '31-AUG-98'
h_benchmark_matdat '31-AUG-98'



The description of the table tmpprice_table is
SQL> desc tmpprice_table
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSIP VARCHAR2(9)
PRICE NUMBER(30,12)
PRICE_DATE DATE
YIELD NUMBER(30,12)
BENCHMARK_CUSIP VARCHAR2(9)
BENCHMARK_MATDAT DATE
BENCHMARK_COUPON NUMBER(30,12)
SPREAD NUMBER(30)



but when i try to run the program i m getting the error
'ORA-01847: day of month must be between 1 and last day of month'

Is there a problem with the way i have inserted the dates?

Any help will be appreciated.


Re: Error Pro*C - while trying to insert date using host variables [message #209308 is a reply to message #209295] Thu, 14 December 2006 03:45 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

just wonder
exec sql INSERT INTO tmpprice_table( price_date,
benchmark_matdat)
VALUES ( to_date(:h_price_date,'DD-MON-YY'),
to_date(:h_benchmark_matdat,'DD-MON-YY'),<--one comma too many ?
);

Also what do you get if you

select to_date('31-AUG-98','DD-MON-YY') from dual
Looks a bit strange to me



Are you 100% sure the values are '31-AUG-98' ?
If it would be some like '32-AUG-98' you would get ORA-01847

[Updated on: Thu, 14 December 2006 03:47]

Report message to a moderator

Re: Error Pro*C - while trying to insert date using host variables [message #209344 is a reply to message #209295] Thu, 14 December 2006 07:07 Go to previous messageGo to next message
adarshmg
Messages: 2
Registered: December 2006
Junior Member

thanks a lot for ur reply..

sorry.. the extra comma was jus a typing error..

to_date('31-AUG-98','DD-MON-YY')
doesnt make a difference but still is quite harmless
it should not cause the error.


I have put a printf statement just before this insert statement and i find my h_price_date having '31-AUG-98'..

the error still persists.






Re: Error Pro*C - while trying to insert date using host variables [message #209389 is a reply to message #209295] Thu, 14 December 2006 08:53 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hmm

Ok, im not a c programmer but

the declaraction can you explain that

varchar h_price_date [13],
h_benchmark_matdat [13];

How do assign the values , using strcpy or ?
I see a varchar but the h_benchmark_matdat what's that ?

I this case i would try char instead of varchar.

Have a look here
http://www.webservertalk.com/archive150-2006-7-1587871.html
Previous Topic: Error with OCI8 Driver
Next Topic: Help me! How to read LONG fileds when link two same tables?
Goto Forum:
  


Current Time: Sat Dec 03 10:11:52 CST 2016

Total time taken to generate the page: 0.15385 seconds