ODAC 10.0.2: Invalid stored procedures can not be executed in direct mode.

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
EvMig
Posts: 2
Joined: Tue 08 May 2018 09:59

ODAC 10.0.2: Invalid stored procedures can not be executed in direct mode.

Post by EvMig » Tue 08 May 2018 10:15

Hello.

When I try to execute an invalid procedure in client mode it is automatically recompiled and successfully executed.
But in direct mode there is an error: "ORA-20003: ORU-10036: object ... is invalid and cannot be described. ORA-06512: at "SYS.DBMS_DESCRIBE""

How to solve this problem?

Borland Delphi for Microsoft Windows Version 10.0.2558.35231 Update 2
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Test case.

Create procedure and make it invalid:

Code: Select all

SQL>create table t ( x int );
Table created.
SQL> create or replace procedure p( x int ) as
  2  begin
  3          for x in ( select * from t ) loop null; end loop;
  4  end;
  5  /
Procedure created.
SQL> alter table t add y number;
Table altered.

SQL> select object_name, status from user_objects where object_name='P';

OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
P
INVALID


SQL>
Write and compile program:

Code: Select all

program TestCLI;

{$APPTYPE CONSOLE}

uses
  SysUtils, Ora, OraError;

var
  Session: TOraSession;
  Proc: TOraStoredProc;
begin
  Session := TOraSession.Create(nil);
  Proc := TOraStoredProc.Create(nil);
  Session.Username := '***';
  Session.Password := '***';
  Session.Options.Direct := True;
  Session.Server := '***';
  Session.Connect;
  Writeln('Direct mode. Connected.');
  Proc.Session := Session;
  Proc.StoredProcName := 'P';
  try
    Proc.ExecProc;
    Writeln('Executed successfully');
  except
    on E:EOraError do Writeln(E.Message);
  end;
  Session.Disconnect;
  Session.Options.Direct := False;
  Session.Server := '***';
  Session.Connect;
  Writeln('Client mode. Connected.');
  Proc.StoredProcName := 'P';
  try
    Proc.ExecProc;
    Writeln('Executed successfully');
  except
    on E:EOraError do Writeln(E.Message);
  end;
  Proc.Free;
  Session.Free;
  Readln;
end.
Execute program:

Code: Select all

C:\Users\Evmig \Documents\Borland Studio Projects\TestODAC10>TestCLI.exe
Direct mode. Connected.
ORA-20003: ORU-10036: object P is invalid and cannot be described
ORA-06512: at "SYS.DBMS_DESCRIBE", line 147
ORA-06512: at line 1

Client mode. Connected.
Executed successfully


C:\Users\Evmig\Documents\Borland Studio Projects\TestODAC10>

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: ODAC 10.0.2: Invalid stored procedures can not be executed in direct mode.

Post by MaximG » Thu 10 May 2018 06:54

Thank you for providing the information. We know about the existence of this issue and are investigating the possible ways to solve it. We will let you know the results of this work immediately after it's finished.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: ODAC 10.0.2: Invalid stored procedures can not be executed in direct mode.

Post by MaximG » Wed 20 Jun 2018 09:58

We reproduced the issue and fixed this error. The fix will be included in the next ODAC build. As a workaround, we can send you a night ODAC build including the required changes. For this, using e-support form (https://www.devart.com/company/contactform.html) provide us with your license number and IDE version you are interested in

Post Reply