Setting a CLOB variable

[CraigJ]

Given the following SQL in a TOracleQuery:

begin
sv.UpdateOrderZERTORDERNO, :ITEMNO, :OFFERID, :ZERT);
end;

All the variables are defined as otInteger except for :ZERT which is defined as otCLOB. I'm trying to use a TLOBLocator and SetComplexVariable to set the value of :ZERT. When I try to write a PChar buffer to the TLOBLocator I get an Invalid Handle exception. After going over the DOA documentation and the Oracle docs it seems I can't do this. You need to have a LOB object defined on the server that the TLOBLocator is associated with. Any ideas on how to make this work?

Craig Jensen
craig.jensen@powerquest.com

 

[Marco Kalter]

You do indeed need a LOB Locator that is initialized on the server, but this can easily be achieved. The following stored procedure would update a dept record, clears the Remarks column (which is a CLOB) and returns an initialized LOB Locator:

procedure update_dept(p_deptno  in dept.deptno%type,
                      p_dname   in dept.dname%type,
                      p_loc     in dept.loc%type,
                      p_remarks in out dept.remarks%type) is
begin
  update dept
     set dname = p_dname,
         loc = p_loc,
         remarks = empty_clob()
   where deptno = p_deptno
   returning remarks into p_remarks;
end update_dept;

You can call this procedure through a TOracleQuery with the following SQL:

begin
  update_dept(:p_deptno, :p_dname, :p_loc, :p_remarks);
end;

Finally, you can call the stored procedure and write to the CLOB column like this:

procedure TMainForm.ButtonClick(Sender: TObject);
var Remarks: TLOBLocator;
    s: string;
begin
  with MyQuery do
  begin
    SetVariable('p_deptno', 10);
    SetVariable('p_dname', 'Accounting');
    SetVariable('p_loc', 'New York');
    // Create a TLOBLocator instance and assign it to the remarks variable
    Remarks := TLOBLocator.Create(MySession, otCLOB);
    SetComplexVariable('p_remarks', Remarks);
    Execute;
    // After calling the procedure, the TLOBLocator is initialized
    s := 'The accounting department';
    Remarks.Write(s[1], Length(s));
    Remarks.Free;
    MySession.Commit;
  end;
end;

------------------
Marco Kalter
Allround Automations

 

[CraigJ]

Thanks for info. It works great.