: while executing a procedure

[JawwadLone]

hi
i m trying to insert a record .But keep on getting the same error again n again Not valid month.i tried the to_date () function but it isnt working as well. I have pasted the code below:
and expecting a solution ASAP.

Regards,
Jawwad

exec pack_Employee.aDDEmployee( 'sdsdf' ,'sdsdf', 1 , 'sdsdf' ,'s' , 's' , 's' , 'sdsdf' ,'sdsdf' , 's' , '02/04/2000','sdsdf' ,'sdsdf' , 'sdsdf' ,'sdsdf' , 'sdsdf' , 'sdsdf' , 'f' , 'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' , 1,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' );


------The Procedure in Package----------

  procedure AddEmployee(
  pr_name in varchar2, 
     father in varchar2, 
     Office_id in number, 
     pr_Alias in varchar2, 
     pr_category in varchar2, 
     Status in varchar2, 
     pr_class in varchar2, 
     religion in varchar2, 
       pr_cast in varchar2,
     gender in varchar2, 
      dob in date,
         cnic in varchar2, 
     street in varchar2, 
     mouza in varchar2, 
     district in varchar2, 
     province in varchar2, 
     country in varchar2, 
     marital_status in varchar2, 
     education in varchar2, 
     occupation in varchar2, 
     identification_mark1 in varchar2, 
     scar in varchar2, 
     height in varchar2, 
     weight in varchar2, 
     health in varchar2, 
     barrack_id in number,
     Identification_mark2 in varchar2 ,
     street2 in varchar2, 
     mouza2 in varchar2, 
     district2 in varchar2, 
     province2 in varchar2, 
     country2 in varchar2)
is  
Begin
 INSERT INTO pr_main VALUES( 1 ,    pr_name ,      father ,      Office_id ,      pr_Alias,      pr_category ,      Status ,      pr_class ,      religion ,      pr_cast,     gender ,      
 to_date(dob,'DD/MM/yyyy'),      cnic ,      street ,      mouza ,      district ,      province ,      country ,      marital_status ,      education ,      occupation ,      identification_mark1 ,      scar ,      height ,      weight ,      health ,      barrack_id ,     Identification_mark2 ,     street2 ,      mouza2 ,      district2 ,      province2 ,      country2 );
 end AddEmployee;

 

[SantaMufasa]

Jawwad,

Your problem is that by passing '02/04/2000' as an argument to your procedure, you are passing 10 characters, you are not passing a date. Instead, try passing:

...,to_date('02/04/2000','mm/dd/yyyy'),...

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[JawwadLone]

hi
thanx for the reply .
But i m getting the different error now.I have pasted the table structure and error produced during the execution of procedure below.

regards,
Jawwad

--------------Table Structure--------------

CREATE TABLE "PMIS"."" ("PR_ID" NUMBER(10) NOT NULL, "PR_NAME" 
    VARCHAR2(30 byte), "FATHER" VARCHAR2(30 byte), "office_ID" 
    NUMBER(10), "ALIAS" VARCHAR2(20 byte), "CATEGORY" VARCHAR2(2 
    byte), "STATUS" VARCHAR2(2 byte), "CLASS" VARCHAR2(1 byte), 
    "RELIGON" VARCHAR2(20 byte), "CAST" VARCHAR2(20 byte), 
    "GENDER" VARCHAR2(1 byte), "DOB" DATE, "CNIC" VARCHAR2(15 
    byte), "STREET" VARCHAR2(25 byte), "MOUZA" VARCHAR2(20 byte),
    "DISTRICT" VARCHAR2(20 byte), "PROVINCE" VARCHAR2(20 byte), 
    "COUNTRY" VARCHAR2(20 byte), "MARITAL_STATUS" VARCHAR2(1 
    byte), "EDUCATION" VARCHAR2(20 byte), "OCCUPATION" 
    VARCHAR2(25 byte), "IDENTIFICATION_MARK1" VARCHAR2(50 byte), 
    "SCAR" VARCHAR2(20 byte), "HEIGHT" VARCHAR2(10 byte), 
    "WEIGHT" VARCHAR2(10 byte), "HEALTH" VARCHAR2(30 byte), 
    "BARRAK_ID" NUMBER(3), "IDENTIFICATION_MARK2" VARCHAR2(50 
    byte), "STREET2" VARCHAR2(25 byte), "MOUZA2" VARCHAR2(20 
    byte), "DISTRICT2" VARCHAR2(20 byte), "PROVINCE2" VARCHAR2(20
    byte), "COUNTRY2" VARCHAR2(20 byte),

--------------Error--------------

BEGIN pack_employee.aDDemployee( 'sdsdf' ,'sdsdf' ,     1 ,      'sdsdf' ,'s' ,     's' ,      's' ,      'sdsdf' ,'sdsdf' ,     's' , to_date('2/4/2000','DD/MM/yyyy'),'sdsdf' ,'sdsdf' ,     'sdsdf' ,'sdsdf' ,    'sdsdf' ,   'sdsdf' ,    'f' ,     'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,    1,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ,'sdsdf' ); END;

*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "PMIS.PACK_employee", line 37
ORA-06512: at line 1

 

[sem]

Your dob parameter is already declared as of DATE data type thus you need not make any conersions in your insert statements. So you may either declare it as varchar2 and pass a string rather than date to procedure or just remove TO_DATE in procedure body.


Regards, Dima

http://seminihin.narod.ru