xml data as parameter to a procedure [message #390521] |
Fri, 06 March 2009 23:30  |
rahulsql
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
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 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
[mod-edit: fixed url by removing net. in front of it]
[Updated on: Sat, 07 March 2009 00:46] by Moderator Report message to a moderator
|
|
|
Re: xml data as parameter to a procedure [message #390535 is a reply to message #390521] |
Sat, 07 March 2009 01:26  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Fri, 28 November 2008 14:56 | Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
|
|
|
|