Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

passing the xml data as parameter to procedure

user10447332Mar 6 2009 — edited Mar 9 2009
i have table like this
CREATE TABLE employeeDetails
(
EmpName VARCHAR2(50),

EmpSal INTEGER,
DeptNo INTEGER,
JoinDate DATE

)

i am getting the i/p data in xml format to extract data i saw the code in net.http://riteshk.blogspot.com/2008/09/sending-xml-string-as-input-parameter.html





CREATE OR REPLACE PROCEDURE Insert_Employee_Data (ip_emp_details IN XMLTYPE)
IS
BEGIN
FOR i IN
(SELECT XMLTYPE.EXTRACT (VALUE (a),
'/Root/EmpName/text()').getstringval
() AS ipempname,
XMLTYPE.EXTRACT (VALUE (a), '/Root/EmpSal/text()').getstringval
() AS ipempsal,
XMLTYPE.EXTRACT (VALUE (a), '/Root/DeptNo/text()').getstringval
() AS ipdeptno,
XMLTYPE.EXTRACT (VALUE (a),
'/Root/JoinDate/text()'
).getstringval () AS ipjoindate
FROM TABLE (XMLSEQUENCE (ip_emp_details.EXTRACT ('/EmployeeData/Root')
)
) a)
LOOP
INSERT INTO EMPLOYEEDETAILS
(empname, empsal, deptno, joindate
)
VALUES (i.ipempname, i.ipempsal, i.ipdeptno, i.ipjoindate
);
END LOOP;
END Insert_Employee_Data;



i want to test the procedure in database itself.
how to call the procedure in database to test that procedure by passing sample data.how to pass paratmeters while cakking that procedure in database itself to test that the procedure is working or not

/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 6 2009
Added on Mar 6 2009
9 comments
1,682 views