Clob as out parameter to Storedproc component

Hi,

I want to use clob as out parameter in storedproc component. I tried this but i am unable to accomplish it. please guide me how can i achieve this.

thanks
sudheer.

What exactly goes wrong?

Note that you need to create a TLOBLocator instance through the TLOBLocator.CreateTemporary constructor, so that you obtain a Temporary CLOB that can be used for stored procedure calls.

See also the CreateTemporary topic in the User's Guide.

the following delphi procedure what we are using.
procedure getclob();
var
templob : TLOBLocator;
tempSession : TOracleSession;
tempPackage : TOraclePackage;
begin
tempSession := TOracleSession.Create(Self);
tempSession.LogonDatabase := 'temp';
tempSession.LogonUsername := 'temp';
tempSession.LogonPassword := 'temp';
tempSession.Connected := True;
tempPackage := TOraclePackage.Create(Self);
tempPackage.PackageName := 'temptest';
tempPackage.CallProcedure('getclob',[]); // there are no IN or INOUT parameters. Only one OUT parameter p_returnclob of type CLOB
templob := TLOBLocator.Create(tempSession,otCLOB);
templob := tempPackage.GetParameter('p_retrunclob');
showmessage(templob.ToString);
end;

the following PL/SQL procedure what we are using under the package temptest.

procedure getclob( p_returnclob OUT CLOB) is
begin
p_returnclob := 'test';
end;

You cannot use the TOraclePackage to call procedures with complex paramaters such as CLOB's. You will need to use a TOracleQuery instance with a PL/SQL Block with the procedure call. For example:

Code

var LOB: TLOBLocator;
begin
  with LOBQuery do
  begin
    SetVariable('v_id', ...);
    // Create a new temporary CLOB
    LOB := TLOBLocator.CreateTemporary(Session, otCLOB, True);
    // Assign it to the returning variable
    SetComplexVariable('v_clob', LOB);
    // Call the procedure
    Execute;
    // Do something with the data
    ShowMessage(LOB.AsString);  
    // Free the CLOB
    LOB.Free;
  end;
end;