0

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:

enter image description here

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

1 Answer 1

2

XML path is a string, not an identifier, so you need to enclose it in single quotes:

"salary_year"       NUMBER(4,0)     PATH  'SALYR'  -- not "SALYR" 
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks @Kombajn zbożowy

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.