OraFAQ Forum: Precompilers, OCI & OCCI » Error Pro*C - while trying to insert date using host variables

Home » Developer & Programmer » Precompilers, OCI & OCCI » Error Pro*C - while trying to insert date using host variables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error Pro*C - while trying to insert date using host variables [message #209295]

Thu, 14 December 2006 02:29

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.

Report message to a moderator

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

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

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.

Report message to a moderator

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

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

Report message to a moderator

Previous Topic:	Error with OCI8 Driver
Next Topic:	Help me! How to read LONG fileds when link two same tables?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 09:26:58 CDT 2024