Delphi 2005. Executing stored procedure raise OracleException with message 'ORA-01405 fetched column value is NULL'

Delphi 2005. Executing stored procedure raise OracleException with message 'ORA-01405 fetched column value is NULL'

Postby ender » Thu 09 Jun 2005 06:37

I'm trying to execute following Oracle stored procedure:

Code: Select all
CREATE OR REPLACE function RepGost ( Operation in pls_integer,
                         RowData in varchar2,
                         ErrorStr in out varchar2,
                         PATH_TUNE_ID in pls_integer)
    return pls_integer
  is
    EXTERNAL
    LIBRARY repnsi
    NAME "RepGost"
    LANGUAGE C
    PARAMETERS(
      Operation LONG,
      RowData by reference string,
      ErrorStr by reference string,
      PATH_TUNE_ID LONG,
      return LONG);


I'm use OracleCommand, automatically receive parameters through CreateParameters, then fill "in" and "in/out" parameters, and execute procedure with ExecuteNonQuery which raises an OracleException.
ender
 
Posts: 14
Joined: Mon 06 Jun 2005 11:32

Postby Paul » Thu 09 Jun 2005 08:31

Try to execute this procedure using PL/SQL block in SQL Plus or using Microsoft data provider. What value do you receive in ErrorStr?
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby ender » Thu 09 Jun 2005 10:21

Code: Select all
DECLARE
  RetVal pls_integer;
  OPERATION BINARY_INTEGER;
  ROWDATA VARCHAR2(200);
  ERRORSTR VARCHAR2(200);
  PATH_TUNE_ID BINARY_INTEGER;

BEGIN
  OPERATION := -1;
  ROWDATA := 'sadfasdf';
  ERRORSTR := 'asdfsadf';
  PATH_TUNE_ID := 0;

  RetVal := PNTZ.REPGOST ( OPERATION, ROWDATA, ERRORSTR, PATH_TUNE_ID );

  DBMS_OUTPUT.Put_Line('ERRORSTR = ' || ERRORSTR);
  DBMS_OUTPUT.Put_Line('RetVal = ' || TO_CHAR(RetVal));

  COMMIT;
END;


ErrorStr contain some data (actually report about actions was made) and is not NULL.
ender
 
Posts: 14
Joined: Mon 06 Jun 2005 11:32

Postby Paul » Thu 09 Jun 2005 13:59

We cannot reproduce your problem with OraDirect .NET 3.0, Oracle 9.2.0.4. Can you reproduce this problem without external library. If yes, send us please small Delphi demo project to demonstrate the problem to OraDirect .NET support address and include script to create server objects.

Code: Select all
procedure TWinForm.Button1_Click(sender: System.Object; e: System.EventArgs);
begin
  OracleConnection1.Open;
  OracleCommand1.CommandText := 'RepGost';
  OracleCommand1.CommandType := CommandType.StoredProcedure;
  OracleCommand1.CreateParameters;
  OracleCommand1.Parameters['OPERATION'   ].Value := TObject(-1);
  OracleCommand1.Parameters['ROWDATA'     ].Value := TObject('sadfasdf');
  OracleCommand1.Parameters['ERRORSTR'    ].Value := TObject('asdfsadf');
  OracleCommand1.Parameters['PATH_TUNE_ID'].Value := TObject(0);
  OracleCommand1.ExecuteNonQuery;
end;
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby ender » Tue 14 Jun 2005 05:40

This error cannot be reproduced without external library. I'm created stored function with same prototype and error does not happen.

Moreover, this error can be workarounded if in/out parameters given some large values (not null). For example string of size 2000 chars.
ender
 
Posts: 14
Joined: Mon 06 Jun 2005 11:32

Postby Oleg » Wed 15 Jun 2005 16:29

Suppose the problem also can be in using pls_integer type, try to call this procedure through PL/SQL block with OraDirect .NET, where pls_integer parameters must be converted to another type, for example Number.
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Postby ender » Thu 16 Jun 2005 04:07

I think this weird behavior linked with in/out variables, not with PLS_Integer specifically. The document which describes this is on the metalink.oracle.com - Note:99731.1.
ender
 
Posts: 14
Joined: Mon 06 Jun 2005 11:32

Postby Oleg » Thu 16 Jun 2005 07:54

Did you manage to invoke your stored procedure using PL/SQL block in OraDirect .NET?
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Postby ender » Thu 16 Jun 2005 08:11

Yes. However it is not way i like. This require construction of additional PL/SQL code for each procedure of that kind.
ender
 
Posts: 14
Joined: Mon 06 Jun 2005 11:32

Postby Oleg » Thu 16 Jun 2005 08:41

Please provide a sample of your PL/SQL that works with OraDirect .NET.
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56


Return to dotConnect for Oracle