CODEWITHSUNDEEP
xmloracle-databaseplsql
Wahid Masud
Wahid Masud

Reputation: 1093

Oracle method dispatch on NULL SELF argument is disallowed

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;

Upvotes: 0

Views: 2139

Answers (1)

Jon Heller
Jon Heller

Reputation: 36902

The XPath expression is missing "employee".

Change this line:

input_rate:=to_number(xmlData.extract('/employees/insurance/health/tsih_total/text()').getstringval())/12;

To this:

input_rate:=to_number(xmlData.extract('/employees/employee/insurance/health/tsih_total/text()').getstringval())/12;

Upvotes: 0

Related Questions