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

ender · Post by **ender** » Thu 09 Jun 2005 06:37

I'm trying to execute following Oracle stored procedure:

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.

Paul · Post by **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?

ender · Post by **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.

Paul · Post by **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;

ender · Post by **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.

Oleg · Post by **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.

ender · Post by **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.

Oleg · Post by **Oleg** » Thu 16 Jun 2005 07:54

Did you manage to invoke your stored procedure using PL/SQL block in OraDirect .NET?

ender · Post by **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.

Oleg · Post by **Oleg** » Thu 16 Jun 2005 08:41

Please provide a sample of your PL/SQL that works with OraDirect .NET.