I want to return an output param of CLOB data type(XML data) from an oracle stored procedure.
My procedure looks like this
create or replace PROCEDURE myProcedure
(
myParam1 IN NUMBER
,myParam2 IN NUMBER
,myParam3 OUT CLOB
) AS xml_bits CLOB;
BEGIN
select xmldataColumn into xml_bits
from myTable WHERE id = myParam1 AND SESSION_ID = myParam2;
IMPCPM_XML := xml_bits;
END myProcedure;
I compiled this and when I try to execute, it is giving me the following error.
exact fetch returns more than requested number of rows
How do I return a big XML data as an out param?
Thanks in advance.
There is nothing wrong with your procedure - it is the data (and the parameters ) that are causing the problem
SQL> create table mytable
2 (id int, session_id int, xmldataColumn clob );
Table created.
SQL>
SQL> insert into mytable values (1,1,'<xml>blahblah</xml>');
1 row created.
SQL>
SQL> create or replace PROCEDURE myProcedure
2 (
3 myParam1 IN NUMBER
4 ,myParam2 IN NUMBER
5 ,myParam3 OUT CLOB
6 ) AS
7 xml_bits CLOB;
8 BEGIN
9 select xmldataColumn into xml_bits
10 from myTable WHERE id = myParam1 AND SESSION_ID = myParam2;
11
12 myParam3 := xml_bits;
13
14 END;
15 /
Procedure created.
SQL>
SQL> variable c clob
SQL> exec myProcedure(1,1,:c);
PL/SQL procedure successfully completed.
SQL> print c
C
--------------------------------------------------------------------------------
<xml>blahblah</xml>
SQL>
SQL> insert into mytable values (1,1,'<xml>blahblah</xml>');
1 row created.
SQL> exec myProcedure(1,1,:c);
BEGIN myProcedure(1,1,:c); END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "MCDONAC.MYPROCEDURE", line 9
ORA-06512: at line 1
Any select-into within PLSQL must get a *single* from the database, otherwise you'll the error as seen.