Home » SQL & PL/SQL » SQL & PL/SQL » xml data as parameter to a procedure (oracle 10g)
xml data as parameter to a procedure [message #390521] Fri, 06 March 2009 23:30 Go to next message
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 Go to previous message
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


Previous Topic: ORA-12801
Next Topic: how we can keep our DB available 7/7 days and 24/24 houres
Goto Forum:
  


Current Time: Mon Feb 10 11:34:05 CST 2025