Reputation: 1093
Why getting ORA-01858: a non-numeric character was found where a numeric was expected?
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.
My table TBL_EMP_BASIC_PROFILE is :
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)
My Package function is:
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 '<result><status affectedRow='||ret1||'>Success</status></result>';
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 '<result><status>Error'||v_errm||'</status></result>';
END save_emp_basic_profile;
My input pl sql is:
declare
query_result clob;
begin
query_result := *package_name*.SAVE_EMP_BASIC_PROFILE
('<employees>
<employee>
<ebasp_id>1234567890</ebasp_id>
<ebasp_name></ebasp_name>
<b_ebasp_name></b_ebasp_name>
<ebasp_gender></ebasp_gender>
<ebasp_category></ebasp_category>
<ebasp_region_type></ebasp_region_type>
<ebasp_region_name></ebasp_region_name>
<ebasp_sub_region></ebasp_sub_region>
<ebasp_location></ebasp_location>
<ebasp_designation></ebasp_designation>
<b_ebasp_designation></b_ebasp_designation>
<ebasp_date_of_joining>to_date(''2-2-2016'',''DD-MM-YYYY'')</ebasp_date_of_joining>
<ebasp_grade></ebasp_grade>
<ebasp_basic></ebasp_basic>
<ebasp_photo_upload></ebasp_photo_upload>
<ebasp_created_on>to_date(''3-2-2016'',''DD-MM-YYYY'')</ebasp_created_on>
<ebasp_created_by></ebasp_created_by>
<econt_cell_number></econt_cell_number>
<econt_phone_number></econt_phone_number>
<econt_email></econt_email>
<econt_village_or_street></econt_village_or_street>
<b_econt_village_or_street></b_econt_village_or_street>
<econt_thana></econt_thana>
<b_econt_thana></b_econt_thana>
<econt_post_office></econt_post_office>
<b_econt_post_office></b_econt_post_office>
<econt_postal_code></econt_postal_code>
<b_econt_postal_code></b_econt_postal_code>
<econt_district></econt_district>
</employee>
</employees>');
end;
Only id and 1 date is given as input (id is mandatory as a primary key). But I am getting this error: Error code -1858: ORA-01858: a non-numeric character was found where a numeric was expected . 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.
Upvotes: 0
Views: 4157
Answers (2)
Reputation: 23578
Don't put the to_date inside your xml; that way, you're pulling out a string "to_date(''2-2-2016'',''DD-MM-YYYY'')" which you're then expecting to convert into a date - ie. to_date('to_date(''''2-2-2016'''',''''DD-MM-YYYY'''')', '<default nls_date_format mask>') which I think you can see is a non-starter.
Instead, leave the date as the string, e.g.: <ebasp_date_of_joining>2-2-2016<ebasp_date_of_joining>, change the datatype of the column in the xmltable to varchar2 and then do the conversion to date as part of the insert, like so:
insert into tbl_emp_basic_profile -- where is the list of columns? You should explicitly list the columns you're inserting into, esp in production code!
select ebasp_id,
ebasp_name,
b_ebasp_name,
ebasp_gender,
ebasp_category,
ebasp_region_type,
ebasp_region_name,
ebasp_sub_region,
ebasp_location,
ebasp_designation,
b_ebasp_designation,
to_date(ebasp_date_of_joining, 'dd-mm-yyyy') ebasp_date_of_joining,
ebasp_grade,
ebasp_basic,
ebasp_photo_upload,
to_date(ebasp_created_on, 'dd-mm-yyyy') ebasp_created_on,
ebasp_created_by
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 varchar2(10) 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 varchar2(10) path 'ebasp_created_on',
ebasp_created_by varchar2(50) path 'ebasp_created_by') temp1;
Even better would be to have the date mask used for each date as an attribute of each date node in the XML, so that you don't have to already know the format of the date string in advance; that way, if whatever generates the xml wants to change how they format their dates, it should be invisible to the database. I get that it may not be possible to get the XML generator to add that in, though.
Upvotes: 4
Reputation: 27
in your code you use to_char instead of to_date('2-2-2016','DD-MM-YYYY'). maybe that's where the error is coming from?
Upvotes: 0
Related Questions
- Getting Error - ORA-01858: a non-numeric character was found where a numeric was expected
- ora-01858 a non-numeric character was found where a numeric was expected informatica
- getting java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected
- .NET ORA-01858: a non-numeric character was found where a numeric was expected
- ORA-01858: a non-numeric character was found where a numeric was expected