Setting .OraClob = null

[Simp]

I have...

myClob := TOraLob.Create(OraSession1.OCISvcCtx);
myClob.CreateTemporary(ltClob);
myClob.AsString := '1111';
myClob.WriteLob;

with OraStoredProc1 do
begin
Close;
ParamByName('ClientList').AsOraClob := myClob;
Open;
end;

This works fine. I want to be able to pass null to the stored procedure for the param ClientList. How can I set the value going into the stored procedure to null? {The parameter defined in the Oracle SProc is a clob}


Thanks

[Alex]

You can use following code

Code: Select all

with OraStoredProc1 do 
begin 
Close; 
ParamByName('ClientList').Value := '';
Open; 
end; 

[Guest]

Alex,

Thanks for the quick response. I am now getting an error:
"ORA-22275: invalid LOB locatoer specified".


When I pass a value (.AsOraClob), it works fine. It is only when I pass the (.Value := '');

Here is the sProc:
CREATE OR REPLACE PROCEDURE Test (ClientList in clob,
cur_out out ResultsCursor.rs)
AS
BEGIN
open cur_out for
select c.CLNT_NBR
from CLNT c
where ((ClientList is null) or
(instr(ClientList, to_char (c.CLNT_NBR)) > 0));
END Test;


Thanks Again

[Alex]

We couldn't reproduce your problem, since I don't have your table creation script I change StoredProc table to Emp standard table. Try my example and if your problem persists send us small demo project with scripts to create server objects, send a message to our ODAC support e-mail address.

StoredProc script :

Code: Select all

--TYPE TCursor IS REF CURSOR;
 CREATE OR REPLACE PROCEDURE  Test_Null_CLOB (
  ClientList in CLOB,
  cur OUT Test_Pack.TCursor
 )
 AS
 BEGIN
   OPEN cur FOR
    SELECT c.EmpNo
    FROM Emp c
    WHERE ( (ClientList is null) OR
      (INSTR(ClientList, TO_CHAR(c.EmpNo)) > 0));
 END Test_Null_CLOB;

Delphi code:

Code: Select all

 //OraStoredProc.StoredProcName := 'Test_Null_CLOB';
 procedure TForm1.Button1Click(Sender: TObject);
 begin
  //Null param test
  OraStoredProc1.Close;
  OraStoredProc1.ParamByName('ClientList').Value := '';
  OraStoredProc1.Open;
 end;

 procedure TForm1.Button2Click(Sender: TObject);
 var
  myClob : TOraLob;
 begin
  //Not null param test
  OraStoredProc1.Close;
  myClob := TOraLob.Create(OraSession1.OCISvcCtx);
  myClob.CreateTemporary(ltClob);
  myClob.AsString := '10';
  myClob.WriteLob;
  OraStoredProc1.ParamByName('ClientList').AsOraClob := myClob;
  OraStoredProc1.Open;
 end;