Re: Passing Dates into stored procedures

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 Tue Feb 22 2000 - 15:25:47 CST