Why getting ORA-01858: a non-numeric character was found where a numeric was expected?
<p>I am working with xml table. I am having problem with my date type columns. Can somebody look into my code and tell me what is wrong with my code cause I can't figure it out.  </p>

<p>My table <code>TBL_EMP_BASIC_PROFILE</code> is :  </p>

<pre><code>Name                  Null     Type          
--------------------- -------- ------------- 
EBASP_ID              NOT NULL VARCHAR2(10)  
EBASP_NAME                     VARCHAR2(50)  
B_EBASP_NAME                   VARCHAR2(150) 
EBASP_GENDER                   VARCHAR2(8)   
EBASP_CATEGORY                 VARCHAR2(10)  
EBASP_REGION_TYPE              VARCHAR2(20)  
EBASP_REGION_NAME              VARCHAR2(50)  
EBASP_SUB_REGION               VARCHAR2(50)  
EBASP_LOCATION                 VARCHAR2(100) 
EBASP_DESIGNATION              VARCHAR2(50)  
B_EBASP_DESIGNATION            VARCHAR2(150) 
EBASP_DATE_OF_JOINING          DATE          
EBASP_GRADE                    NUMBER(2)     
EBASP_BASIC                    NUMBER(7)     
EBASP_PHOTO_UPLOAD             VARCHAR2(500) 
EBASP_CREATED_ON               DATE          
EBASP_CREATED_BY               VARCHAR2(10)  
</code></pre>

<p>My Package function is:  </p>

<pre><code>FUNCTION save_emp_basic_profile(employeeData VARCHAR2) RETURN CLOB IS

ret CLOB;
xmlData XMLType;
v_code NUMBER;
v_errm VARCHAR2(500);

BEGIN

xmlData:=XMLType(employeeData);
INSERT INTO TBL_EMP_BASIC_PROFILE SELECT temp1.* FROM XMLTABLE('/employees/employee'
                                  PASSING xmlData
                                  COLUMNS ebasp_id VARCHAR2(10) PATH 'ebasp_id',
                                          ebasp_name VARCHAR2(50) PATH 'ebasp_name',
                                          b_ebasp_name VARCHAR2(150) PATH 'b_ebasp_name',
                                          ebasp_gender VARCHAR2(8) PATH 'ebasp_gender',
                                          ebasp_category VARCHAR2(10) PATH 'ebasp_category',
                                          ebasp_region_type VARCHAR2(50) PATH 'ebasp_region_type',
                                          ebasp_region_name VARCHAR2(50) PATH 'ebasp_region_name',
                                          ebasp_sub_region VARCHAR2(50) PATH 'ebasp_sub_region',
                                          ebasp_location VARCHAR2(100) PATH 'ebasp_location',
                                          ebasp_designation VARCHAR2(50) PATH 'ebasp_designation',
                                          b_ebasp_designation VARCHAR2(150) PATH 'b_ebasp_designation',
                                          ebasp_date_of_joining DATE PATH 'ebasp_date_of_joining',
                                          ebasp_grade NUMBER(2) PATH 'ebasp_grade',
                                          ebasp_basic NUMBER(7) PATH 'ebasp_basic',
                                          ebasp_photo_upload VARCHAR2(500) PATH 'ebasp_photo_upload',
                                          ebasp_created_on DATE PATH 'ebasp_created_on',
                                          ebasp_created_by VARCHAR2(50) PATH 'ebasp_created_by')temp1;
ret:=to_char(sql%rowcount);  
COMMIT;

RETURN '&lt;result&gt;&lt;status affectedRow='||ret1||'&gt;Success&lt;/status&gt;&lt;/result&gt;';
DBMS_OUTPUT.PUT_LINE(ret);
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1 , 500);
DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
RETURN '&lt;result&gt;&lt;status&gt;Error'||v_errm||'&lt;/status&gt;&lt;/result&gt;';
END save_emp_basic_profile;
</code></pre>

<p>My input pl sql is:  </p>

<pre><code>declare
  query_result clob;
begin
  query_result := *package_name*.SAVE_EMP_BASIC_PROFILE
  ('&lt;employees&gt;
    &lt;employee&gt;
        &lt;ebasp_id&gt;1234567890&lt;/ebasp_id&gt;
        &lt;ebasp_name&gt;&lt;/ebasp_name&gt;
        &lt;b_ebasp_name&gt;&lt;/b_ebasp_name&gt;
        &lt;ebasp_gender&gt;&lt;/ebasp_gender&gt;
        &lt;ebasp_category&gt;&lt;/ebasp_category&gt;
        &lt;ebasp_region_type&gt;&lt;/ebasp_region_type&gt;
        &lt;ebasp_region_name&gt;&lt;/ebasp_region_name&gt;
        &lt;ebasp_sub_region&gt;&lt;/ebasp_sub_region&gt;
        &lt;ebasp_location&gt;&lt;/ebasp_location&gt;
        &lt;ebasp_designation&gt;&lt;/ebasp_designation&gt;
        &lt;b_ebasp_designation&gt;&lt;/b_ebasp_designation&gt;
        &lt;ebasp_date_of_joining&gt;to_date(''2-2-2016'',''DD-MM-YYYY'')&lt;/ebasp_date_of_joining&gt;
        &lt;ebasp_grade&gt;&lt;/ebasp_grade&gt;
        &lt;ebasp_basic&gt;&lt;/ebasp_basic&gt;
        &lt;ebasp_photo_upload&gt;&lt;/ebasp_photo_upload&gt;
        &lt;ebasp_created_on&gt;to_date(''3-2-2016'',''DD-MM-YYYY'')&lt;/ebasp_created_on&gt;
        &lt;ebasp_created_by&gt;&lt;/ebasp_created_by&gt;
        &lt;econt_cell_number&gt;&lt;/econt_cell_number&gt;
        &lt;econt_phone_number&gt;&lt;/econt_phone_number&gt;
        &lt;econt_email&gt;&lt;/econt_email&gt;
        &lt;econt_village_or_street&gt;&lt;/econt_village_or_street&gt;
        &lt;b_econt_village_or_street&gt;&lt;/b_econt_village_or_street&gt;
        &lt;econt_thana&gt;&lt;/econt_thana&gt;
        &lt;b_econt_thana&gt;&lt;/b_econt_thana&gt;
        &lt;econt_post_office&gt;&lt;/econt_post_office&gt;
        &lt;b_econt_post_office&gt;&lt;/b_econt_post_office&gt;
        &lt;econt_postal_code&gt;&lt;/econt_postal_code&gt;
        &lt;b_econt_postal_code&gt;&lt;/b_econt_postal_code&gt;
        &lt;econt_district&gt;&lt;/econt_district&gt;
    &lt;/employee&gt;
&lt;/employees&gt;');
end;  
</code></pre>

<p>Only id and 1 date is given as input (id is mandatory as a primary key). But I am getting this error: <code>Error code -1858: ORA-01858: a non-numeric character was found where a numeric was expected</code> . Can somebody show me what is the right way to put date in a date type column. Because I have tried to_date() function but no good.</p>