I am new in pl/sql. i have write a pl/sql function using sql developer with proper package name & body.
I function is showing this error:
image given below:
Here is the pl/sql code:
create or replace PACKAGE PAYROLL AS
FUNCTION save_payroll_transaction(transaction_data NVARCHAR2 ) RETURN nclob;
END PAYROLL;
Body:
create or replace PACKAGE BODY PAYROLL IS
FUNCTION save_payroll_transaction(transaction_data NVARCHAR2 ) RETURN nclob IS ret nclob;
xmlData XMLType;
BEGIN
xmlData:=XMLType(transaction_data);
INSERT INTO PAYROLLFILE SELECT x.* FROM XMLTABLE('/transaction'
PASSING xmlData
COLUMNS "salary_year" NUMBER(4,0) PATH "SALYR",
"salary_month" NUMBER(2,0) PATH "SALMT",
"employee_id" NUMBER PATH "EMPID",
"department_code" NUMBER PATH "DPTID",
"salary_head" VARCHAR2(2) PATH "SALHD",
"description" VARCHAR2(50) PATH "DESCR",
"amount" FLOAT(126) PATH "ALAMT",
"operator_id" NUMBER PATH "OPID",
"transaction_date" DATE PATH "TRADT") x;
ret:=to_char(sql%rowcount);
COMMIT;
RETURN '<result><status affectedRow='||ret||'>success</status></result>';
EXCEPTION
WHEN OTHERS THEN
RETURN '<result><status>Error</status></result>';
END save_payroll_transaction;
END PAYROLL;
please help.Thanks
