question about clobs as bind variables

[jdorlon]

Hi,

I am using Delphi 7, Oracle 11g client and server, ODAC 6.25.

I must be doing something wrong here, but I am not sure what it is. This works fine if I declare the SQL parameter as a ftString, but of course that doesn't accept more than 4000 bytes of data, so I am trying to switch to ftOraClob.

I am trying to execute this pl/sql block in a tsmartquery:

DECLARE
ret_val VARCHAR2(4000);
sqltext CLOB;
BEGIN
sqltext := :SQL;
ret_val := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext,
bind_list => NULL,
user_name => :u,
scope => :s,
time_limit => :t,
task_name => :tn,
description => :desc);
Dbms_Sqltune.EXECUTE_TUNING_TASK(:tn);
END;

I am setting the SQL parameter like this:

qry.ParamByName('SQL').ParamType:= ptInput;
qry.ParamByName('SQL').DataType := ftOraClob;
qry.ParamByName('SQL').AsString := mSQL.Lines.Text; // <4000 bytes!

and I am setting the other variables similarly except that they are just ftString and ftInteger.

When I execute it, I am getting the following error:

ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 20
ORA-06512: at "SYS.DBMS_SQLTUNE", line 487
ORA-06512: at line 7

Am I doing something wrong?

Thanks for your help.

-John

[jdorlon]

More Info:

When I set the TemporaryLobUpdate option to true, I get an ORA-24805 LOB Type Mismatch

[Plash]

You should set TemporaryLobUpdate option to True for this SQL.

We cannot reproduce 'LOB Type Mismatch' error. Please send to odac*devart*com a complete small sample that demonstrates the problem, including the script for creating database objects.

[jdorlon]

Ok, I sent a demo project. thank you for your help.