OraFAQ Forum: JDeveloper, Java & XML » PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER

Home » Developer & Programmer » JDeveloper, Java & XML » PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER (Oracle 10 g ,windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #344996]

Tue, 02 September 2008 02:18

manickam
Messages: 9
Registered: June 2005
Location: chennai

Junior Member

I have written a procedure which has an input parameter A CLOB.

Inside the procedure A is converted to B which is of XMLTYPE.

While executing this procedure an error occurs while assigning A:=B.

But the same doesnot happens if am declaring another variable C of clob datatype and assigning C:= B.

Report message to a moderator

Re: PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #345033 is a reply to message #344996]

Tue, 02 September 2008 03:13

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post a test case that we can reproduce.
Before 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).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #345049 is a reply to message #344996]

Tue, 02 September 2008 03:49

manickam
Messages: 9
Registered: June 2005
Location: chennai

Junior Member

Procedure ExportMilestones works fine while converting clob into xmltype as the value is assigned inside the procedure.

create or replace procedure ExportMilestones(XML_IN     clob := null,
                                             out_output out sys_refcursor) is
  X       xmltype;
  XML_IN1 clob;
begin

  XML_IN1 := '<ArrayOfMilestoneBO><MilestoneBO MilestoneID="26981" 
ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" />
<MilestoneBO MilestoneID="26982"
ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" /></ArrayOfMilestoneBO>';

  --Assigning values inside the procedure
  X := XMLTYPE(XML_IN1);
  open out_output for
  
    SELECT EXTRACTVALUE(VALUE(t), '/MilestoneBO/@MilestoneID') dim_id,
           EXTRACTVALUE(VALUE(t), '/MilestoneBO/@ResponsibilityMilestone') dim_id1
    
      FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/ArrayOfMilestoneBO/MilestoneBO'))) t,
           TABLE(XMLSEQUENCE(EXTRACT(VALUE(t),
                                     '/ArrayOfMilestoneBO/MilestoneBO')))(+) tx;

end ExportMilestones;

Procedure ExportMilestones1 doesnot works , conversion fails while converting clob into xmltype as the value is assigned through the input parameter.

create or replace procedure ExportMilestones1(XML_IN     clob :=null,
                                              out_output out sys_refcursor) is
  X       xmltype;
  XML_IN1 clob;
begin
/*
  XML_IN1 := '<ArrayOfMilestoneBO><MilestoneBO MilestoneID="26981" PlannedDate="0001-01-01T00:00:00"
CompleteDate="0001-01-01T00:00:00" ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" />
<MilestoneBO MilestoneID="26982" PlannedDate="0001-01-01T00:00:00" CompleteDate="0001-01-01T00:00:00" 
ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" /></ArrayOfMilestoneBO>';
*/
  --ASSINGNING VALUES TO X FROM IN PARAMETER XML_IN
  X := XMLTYPE(XML_IN);
  open out_output for
  
    SELECT EXTRACTVALUE(VALUE(t), '/MilestoneBO/@MilestoneID') dim_id,
           EXTRACTVALUE(VALUE(t), '/MilestoneBO/@ResponsibilityMilestone') dim_id1
    
      FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/ArrayOfMilestoneBO/MilestoneBO'))) t,
           TABLE(XMLSEQUENCE(EXTRACT(VALUE(t),
                                     '/ArrayOfMilestoneBO/MilestoneBO')))(+) tx;

end ExportMilestones1;

[Edit MC: Add code tags, please do it yourself next time]

[Updated on: Tue, 02 September 2008 04:09] by Moderator

Report message to a moderator

Re: PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #345064 is a reply to message #345049]

Tue, 02 September 2008 04:12

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Procedure ExportMilestones1 doesnot works , conversion fails while converting clob into xmltype as the value is assigned through the input parameter

Works for me:

SQL> create or replace procedure ExportMilestones1(XML_IN     clob :=null,
  2                                                out_output out sys_refcursor) is
  3    X       xmltype;
  4    XML_IN1 clob;
  5  begin
  6  /*
  7    XML_IN1 := '<ArrayOfMilestoneBO><MilestoneBO MilestoneID="26981" PlannedDate="0001-01-01T00:00:00"
  8  CompleteDate="0001-01-01T00:00:00" ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" />
  9  <MilestoneBO MilestoneID="26982" PlannedDate="0001-01-01T00:00:00" CompleteDate="0001-01-01T00:00:00" 
 10  ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" /></ArrayOfMilestoneBO>';
 11  */
 12    --ASSINGNING VALUES TO X FROM IN PARAMETER XML_IN
 13    X := XMLTYPE(XML_IN);
 14    open out_output for
 15    
 16      SELECT EXTRACTVALUE(VALUE(t), '/MilestoneBO/@MilestoneID') dim_id,
 17             EXTRACTVALUE(VALUE(t), '/MilestoneBO/@ResponsibilityMilestone') dim_id1
 18      
 19        FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/ArrayOfMilestoneBO/MilestoneBO'))) t,
 20             TABLE(XMLSEQUENCE(EXTRACT(VALUE(t),
 21                                       '/ArrayOfMilestoneBO/MilestoneBO')))(+) tx;
 22  
 23  end ExportMilestones1;
 24  /

Procedure created.

SQL> declare
  2    XML_IN1 clob := '<ArrayOfMilestoneBO><MilestoneBO MilestoneID="26981" 
  3  ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" />
  4  <MilestoneBO MilestoneID="26982"
  5  ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" /></ArrayOfMilestoneBO>';
  6    out sys_refcursor;
  7  begin
  8    ExportMilestones1(xml_in1, out);
  9  end;
 10  /

PL/SQL procedure successfully completed.

Regards
Michel

Report message to a moderator

Re: PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #345076 is a reply to message #345064]

Tue, 02 September 2008 04:35

manickam
Messages: 9
Registered: June 2005
Location: chennai

Junior Member

Thanks Michel.

It works fine from sql plus, but it throws error

"/* Source of TYPE BODY SYS.XMLTYPE is not available */"

while executing the same from pl sql interface (a tool like

toad).

Even the below procedure fails to parse the xml.

create or replace procedure ExportMilestones1(XML_IN clob :='<ArrayOfMilestoneBO><MilestoneBO MilestoneID="26981" PlannedDate="0001-01-01T00:00:00"
CompleteDate="0001-01-01T00:00:00" ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" />
<MilestoneBO MilestoneID="26982" PlannedDate="0001-01-01T00:00:00" CompleteDate="0001-01-01T00:00:00"
ResponsibilityMilestone="1219" IsExported="false" XMLNodeCount="0" /></ArrayOfMilestoneBO>',
out_output out sys_refcursor) is
X xmltype;
XML_IN1 clob;
begin

--ASSINGNING VALUES TO X FROM IN PARAMETER XML_IN
X := XMLTYPE(XML_IN);
open out_output for

SELECT EXTRACTVALUE(VALUE(t), '/MilestoneBO/@MilestoneID') dim_id,
EXTRACTVALUE(VALUE(t), '/MilestoneBO/@ResponsibilityMilestone') dim_id1

FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/ArrayOfMilestoneBO/MilestoneBO'))) t,
TABLE(XMLSEQUENCE(EXTRACT(VALUE(t),
'/ArrayOfMilestoneBO/MilestoneBO')))(+) tx;

end ExportMilestones1;

Report message to a moderator