Visual Basic for Application
An
ORA-01460: unimplemented or unreasonable conversion requested error message is for example thrown when trying to pass a
clob exceeding the value of
max_string_size to a
PL/SQL procedure with
VBA.
This is demonstrated with the following test case.
First we need a table …
create table tq84_ora_01460_tab (
clb clob
);
… and a procedure filling the table which will be called by the VBA code:
create or replace procedure tq84_ora_01460_prc(c clob)
authid definer
as
pragma autonomous_transaction pragma autonomous_transaction;
begin
insert into tq84_ora_01460_tab (clb) values (c);
commit;
end tq84_ora_01460_prc;
/
The following code throws the ORA-01460 unimplemented or unreasonable conversion requested error:
option explicit
sub ora_01460() ' {
dim stmt as new adodb.command
set stmt.activeConnection = connectOra
stmt.commandText = "begin tq84_ora_01460_prc(:1); end;"
dim param as adodb.parameter
set param = stmt.createParameter(, adLongVarChar, adParamInput, 64000)
' param.attributes = adParamLong
param.attributes = adFldLong
stmt.parameters.append param
stmt.parameters(0).appendChunk string(16000, "x")
stmt.parameters(0).appendChunk string(16000, "7")
stmt.parameters(0).appendChunk string(16000, "x")
stmt.parameters(0).appendChunk string(16000, "7")
dim dummy as long
stmt.execute dummy
end sub ' }
function connectOra() as adodb.connection
on error goto err_
dim user_id as string
dim password as string
dim dbName as string
dbName = "ora19"
user_id = "[rene]"
password = ""
set connectOra = new adodb.connection
connectOra.open _
"Provider=OraOLEDB.Oracle.1" & _
";Persist Security Info=False" & _
";User ID=" & user_id & _
";Password=" & password & _
";Data Source=" & dbName & _
";FetchSize=10000" & _
";Extended Properties="""""
exit function
err_:
if left(err.description, 9) = "ORA-01017" then
msgBox "Not enough privileges to connect to the database"
end
end if
end function