SQL & PL/SQL

Hi All,

I am on Oracle 9i and have a stored procedure which I am trying to execute from within my C#.NET code. I am using System.Data.OracleClient.

-----
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection();
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("GETDATA", conn);
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_XMLReport", OracleType.Clob).Direction = ParameterDirection.Output;
cmd.Parameters.Add("p_FilterConditions", OracleType.Clob).Value = conditionList;
cmd.Parameters.Add("p_ReportFields", OracleType.Clob).Value = fields;
cmd.ExecuteNonQuery();
OracleLob clob1 = ((OracleLob)cmd.Parameters["p_XMLReport"].Value);
---------------
When this executes, it throws a "System.Data.OracleClient.OracleException: ORA-01403: no data found" exception.
However, when I execute the same stored procedure using SQL Plus worksheet, it comes back with the results.
I am not sure why it is not seeing the data. Can you pls point out what could be wrong here?

Below is my stored procedure.

-------------------------------------------------------------------------------
create or replace PROCEDURE "GETDATA"
(
p_XMLReport OUT CLOB,
p_FilterConditions IN CLOB, -- where clause conditions
p_ReportFields IN CLOB -- field list
)
AS
v_OutputXML XMLTYPE;
v_FilterConditions VARCHAR2(32767):= '';
v_ReportFields VARCHAR2(32767) := 'ID, PROJECTSTATUS, PROJECTTYPE, CREATOR, DEVELOPMENTSITE';


BEGIN
v_FilterConditions := dbms_lob.substr( p_FilterConditions, 32767, 1 );
v_ReportFields := dbms_lob.substr( p_ReportFields, 32767, 1 );

execute immediate 'SELECT XMLELEMENT("DataSource",
XMLAGG (XMLELEMENT("DataRow",
XMLCOLATTVAL(' || v_ReportFields || ' )))) FROM GETDATAVIEW' || v_FilterConditions
INTO v_OutputXML;
p_XMLReport := v_OutputXML.getClobVal();

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR( -20001, 'An error was encountered - ' || substr(SQLCODE,1,200) || ' -ERROR- ' || SQLERRM, TRUE );

END GETDATA;
---------------------------------------------------------

Thanks much,
AD