Home » Developer & Programmer » JDeveloper, Java & XML » PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER (Oracle 10 g ,windows)
PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #344996] Tue, 02 September 2008 02:18 Go to next message
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.

Re: PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #345033 is a reply to message #344996] Tue, 02 September 2008 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
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
Re: PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #345049 is a reply to message #344996] Tue, 02 September 2008 03:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
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
Re: PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #345076 is a reply to message #345064] Tue, 02 September 2008 04:35 Go to previous messageGo to next message
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;
Re: PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #345091 is a reply to message #344996] Tue, 02 September 2008 05:13 Go to previous messageGo to next message
manickam
Messages: 9
Registered: June 2005
Location: chennai
Junior Member

Any solution for the above one?
Re: PROBLEM WHILE USING XMLTYPE AS INPUT PARAMETER [message #345107 is a reply to message #345076] Tue, 02 September 2008 05:52 Go to previous message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your post!

Regards
Michel
Previous Topic: Extract Attribute Values?
Next Topic: Template not selected in XML
Goto Forum:
  


Current Time: Thu Oct 30 07:43:59 CDT 2014

Total time taken to generate the page: 0.12672 seconds