I have a function under a package. I am passing some xml data to that function. But I'm getting this error. Could anybody explain it? I am explaining what I want to do in my code. I want to input all fields except the tsih_rate field. It will be calculated and filled in an automated way.
FUNCTION save_settings_insurance_health(employeeData NVARCHAR2) RETURN CLOB IS ret CLOB; xmlData XMLType; v_code NUMBER; v_errm VARCHAR2(500); input_rate number(7,2); BEGIN xmlData:=XMLType(employeeData); INSERT INTO TBL_SETTINGS_INSURANCE_HEALTH SELECT temp1.* FROM XMLTABLE('/employees/employee/insurance/health' PASSING xmlData COLUMNS tsih_class NVARCHAR2(10) PATH 'tsih_class', tsih_rate NUMBER(7,2) PATH 'tsih_rate' default null, tsih_total NUMBER(7,2) PATH 'tsih_total', tsih_eff_date_from DATE PATH 'tsih_eff_date_from', tsih_eff_date_to DATE PATH 'tsih_eff_date_to', tsih_created_on DATE PATH 'tsih_created_on', tsih_created_by NVARCHAR2(10) PATH 'tsih_created_by')temp1; ret:=to_char(sql%rowcount); input_rate:=to_number(xmlData.extract('/employees/insurance/health/tsih_total/text()').getstringval())/12; INSERT INTO TBL_SETTINGS_INSURANCE_HEALTH (tsih_rate) VALUES (input_rate); COMMIT; RETURN '<result><status affectedRow='||ret||'>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_settings_insurance_health; My input pl/sql is as following:
declare result clob; begin -- Call the function result := PMIS.SAVE_SETTINGS_INSURANCE_HEALTH('<employees> <employee> <insurance> <health> <tsih_class>teacher</tsih_class> <tsih_total>1200</tsih_total> <tsih_eff_date_from>2016-1-16</tsih_eff_date_from> <tsih_eff_date_to></tsih_eff_date_to> <tsih_created_on>2016-1-16</tsih_created_on> <tsih_created_by>Wahid</tsih_created_by> </health> </insurance> </employee> </employees>'); DBMS_OUTPUT.PUT_LINE(result); end;
No comments:
Post a Comment