Clob as Parameter to Stored Procedure

[indigo]

Hello:

I have an Oracle package which implements a number of functions for a webservice. The SendReturns function of the package is used by the webservice to deliver XML via p_ReturnsData which is stored in a CLOB in the database.

A TOraclePackage(EServicesPackage) is used to access the oracle function. The maximum size transmitted is 4000 bytes which corresponds to Varchar2 and not clob limitations.

What do I need to do to send the full XML and not just the first 4000 bytes?

Thanks, Sidney

Here is the pertinent coding in the Delphi program:
---------------------------------------------------
var
ReturnsData: String;
p_ReportDate: TDateTime;
Result: String;
begin
ReturnsData := edReturnData.Text;
p_ReportDate := StrToDate(edReportingDate.Text);
Result := EservicesPackage.CallStringFunction('SendReturnsData',[p_ReportDate,ReturnsData]);
end;

Here are the corresponding package definitions in Oracle 10:
------------------------------------------------------------
create or replace package Eservices as
function SendReturnsData (p_ReportDate IN date,p_ReturnsData IN clob) return varchar2;
end Eservices;

create or replace package body Eservices as
function SendReturnsData (p_ReportDate IN date,p_ReturnsData IN clob) return varchar2 is
v_ProcessedDateTime EservicesReports.ProcessedDateTime%type;
v_ReturnsDateTime EservicesReports.ReturnsDateTime%type;
v_ReturnsData EservicesReports.ReturnsData%type;
begin
select ProcessedDateTime,ReturnsDateTime,ReturnsData
into v_ProcessedDateTime,v_ReturnsDateTime,v_ReturnsData
from EservicesReports
where ReportDate = p_ReportDate;
if not v_ProcessedDateTime is NULL then
return 'Rejected';
elsif v_ReturnsDateTime is NULL then
update EServicesReports
set ReturnsData = p_ReturnsData,
ReturnsDateTime = SYSDATE
where ReportDate = p_ReportDate;
commit;
return 'Accepted '||Length(p_ReturnsData)||' bytes';
else
update EServicesReports
set ReturnsData = p_ReturnsData,
ReturnsDateTime = SYSDATE
where ReportDate = p_ReportDate;
commit;
return 'Updated '||Length(p_ReturnsData)||' bytes';
end if;
exception
when NO_DATA_FOUND then
insert into EservicesReports(ReportDate,ReturnsData,ReturnsDateTime)
values (p_ReportDate,p_ReturnsData,SYSDATE);
commit;
return 'Accepted '||Length(p_ReturnsData)||' bytes';
end SendReturnsData;
end Eservices;

 

[Marco Kalter]

You cannot use a TOraclePackage component for this. You have to create a PL/SQL function that returns the CLOB data, and use a TOracleQuery with a PL/SQL Block to call this function, or use the Package Wizard to generate a class for the PL/SQL package.

 

[indigo]

Marco:

I don't understand what you mean by I cannot use a TOraclePackage to do this. I am already doing it and it works fine except oracle only receives the first 4000 bytes. It is as if it is ignoring the clob definition and treating it as varchar2.

I am not interested in returning the CLOB data from the database. I am only interested in storing the XML which I have received from the client application and am using the Package to store in my database. Therefore, I don't understand your comment to create a PL/SQL function to return data.

Since it appears I didn't clearly state my problem, I will await your reply before attempting to understand the rest of your response.

Thanks, Sidney

 

[Marco Kalter]

If you use the TOraclePackage component, then all string values are limited to 4000 bytes (the otString data type). If you want to pass more data, you will need to use a TOracleQuery and pass the data as otPLSQLString (32KB limit) or otCLOB (4GB limit).