ORA-01460 when passing PLSQL Table parameter in Oracle10g

[Oleksandr]

Hi,

I would like to share with you my problem

I'm passing PLSQL Table parameter to Oracle stored procedure using ODAC (6.50.0.35), but sometimes i recieve ORA-01460.

...i've spent some time on debugging to find out exactly situation when this exception is generated always.

To generate this exception you have to create oracle package :

Code: Select all

CREATE OR REPLACE PACKAGE testpack AS
  TYPE t_STRING255_A IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
  PROCEDURE testplst(pParam1 IN t_STRING255_A);
END;

CREATE OR REPLACE PACKAGE BODY testpack AS
  PROCEDURE testplst(pParam1 IN t_STRING255_A) AS                        
  BEGIN  
    null;
  END;
END;

and call procedure testplst from application using ODAC :

Code: Select all

with OraSQL1 do
begin
  text :=
    'begin '+
      'testpack.testplst(:pParam1); '+
    'end;';
  ParamByName('pParam1').Table := true;
  ParamByName('pParam1').DataType := ftString;
  ParamByName('pParam1').ParamType := ptInput;
  ParamByName('pParam1').Size := 255;
  ParamByName('pParam1').Length := 3;
  ParamByName('pParam1').ItemAsString[1] := 'XXXX';
  ParamByName('pParam1').ItemAsString[2] := '';
  ParamByName('pParam1').ItemAsString[3] := 'XX';
  execute;
end;

It is important to keep Odac TOraSession flags :
Options->Direct = false
Options->UseOCI7=false

My Oracle Client version is 10.2.0.1.0, my Oracle Server version is the same 10.2.0.1.0.

It seems that passing empty string to

Code: Select all

ParamByName('pParam1').ItemAsString[2] := '';

causes that problem.

Is it the problem of ODAC, or Oracle Client (Server) ?

I've noticed that :
1. enabling Options->Direct = true in TOraSession object
2. or enabling Options->UseOCI7=true in TOraSession object
3. or changing pParam1 parameter type in stored procedure from IN, to IN OUT solving that problem.

regards
Oleksandr

[Plash]

This is a bug of the Oracle client.