Re: Passing Dates into stored procedures

Louis

It's worth pointing out that the procedure would have worked if the NLS_DATE_FORMAT for the database was 'MM/DD/YY' (or even 'DD/MM/YY' for us Brits!). However, if somebody had subsequently changed this parameter, the procedure would have stopped working, possibly leading to panic and much finger-pointing. So it's always prudent to pass the format mask explicitly as cindy demonstrates. It may mean a little more coding but I always find that it's well worth bullet-proofing one's code this way.

Further, in order to avoid millennial confusion, I would add that it's probably a good idea to specify the century also: TO_DATE('09/03/1966', 'MM/DD/YYYY'). You did mean 1966 didn't you?

Best regards

Nick

"C. Ferguson" <c_ferguson_at_rationalconcepts.com> wrote in message news:38B2FF00.F7BE9202_at_rationalconcepts.com...
> Hi,
> you passed in a string '09/03/66' to a date parameter.
> try to_date('09/03/66', 'MM/DD/YY') instead.
>
> hope this helps,
> cindy
>
> Louis Frolio wrote:
>
> > Greetings all, i just wrote a procedure in Oracle8. This procedure
> > takes as one of the passed values a birth date. However, when I
> > pass the required values to the procedure I get the following error
> > message:
> >
> > SQLWKS> execute spAddUser (4, 'FirstName', 'Last Name', 'V', 'Address1',
> > 'Address2', 'City', 'ST', 'ZIP', 840, '09/03/66' , 'M', 0, 'IT', 'E
> > Commerce');
> > ORA-01843: not a valid month
> > ORA-06512: at line 2
> >
> > Her are the contents of my procedure:
> >
> > CREATE OR REPLACE PROCEDURE spAddUser
> > (
> > IinUserId IN NUMBER,
> > IvcFirstName IN VARCHAR2,
> > IvcLastName IN VARCHAR2,
> > IchMiddleInitial IN CHAR,
> > IvcAddress1 IN VARCHAR2,
> > IvcAddress2 IN VARCHAR2,
> > IvcCity IN VARCHAR2,
> > IvcState IN VARCHAR2,
> > IvcZipCode IN VARCHAR2,
> > IsiCountryCode IN NUMBER,
> > IsdBirthDate IN DATE,
> > IchGender IN CHAR,
> > ItiMarried IN NUMBER,
> > IvcOccupation IN VARCHAR2,
> > IvcIndustry IN VARCHAR2
> > )
> >
> > IS
> >
> > BEGIN
> > INSERT INTO tbUser (inUserId, vcFirstName, vcLastName,
> > chMiddleInitial, vcAddress1,
> > vcAddress2, vcCity, vcState, vcZipCode,
> > siCountryCode,
> > sdBirthDate, chGender, tiMarried,
vcOccupation,
> > vcIndustry,
> > sdDate, tiActive)
> >
> > VALUES (IinUserId, IvcFirstName, IvcLastName, IchMiddleInitial,
> > IvcAddress1,
> > IvcAddress2, IvcCity, IvcState, IvcZipCode,
> > IsiCountryCode,
> > IsdBirthDate, IchGender, ItiMarried, IvcOccupation,
> > IvcIndustry,
> > SysDate, 1);
> >
> > END spAddUser;
> >
> > I would appreciate any help with regards to this matter.
> >
> > Louis
>
Received on Sun Feb 27 2000 - 06:13:41 CST