xml - How to return CLOB datatype as output Parameter in Oracle stored procedure? -

i want return output param of clob datatype(xml data) oracle stored procedure.

my procedure looks this

create or replace procedure myprocedure (   myparam1 in number   ,myparam2 in number   ,myparam3 out clob  )      xml_bits clob; begin   select  xmldatacolumn xml_bits   mytable id = myparam1 , session_id = myparam2;    impcpm_xml := xml_bits; end myprocedure; 

i compiled , when try execute, giving me following error.

exact fetch returns more requested number of rows 

how return big xmldata out param?

thanks in advance.

the error has nothing returning clob value. query:

select  xmldatacolumn xml_bits  mytable id = myparam1 , session_id = myparam2; 

returns more 1 row , using select ... ... requires query returns single row.

you add filter rownum = 1:

create or replace procedure myprocedure (   myparam1 in number   ,myparam2 in number   ,myparam3 out clob  )  xml_bits clob; begin   select xmldatacolumn     xml_bits      mytable    id = myparam1   ,    session_id = myparam2   ,    rownum = 1; end myprocedure; / 

or handle exception:

create or replace procedure myprocedure (   myparam1 in number   ,myparam2 in number   ,myparam3 out clob  )  xml_bits clob; begin   select xmldatacolumn     xml_bits      mytable    id = myparam1   ,    session_id = myparam2; exception   when too_many_rows     xml_bits := null; end myprocedure; /