Page 1 of 1
Delphi 2005. Executing stored procedure raise OracleException with message 'ORA-01405 fetched column value is NULL'
Posted: Thu 09 Jun 2005 06:37
by ender
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.
Posted: Thu 09 Jun 2005 08:31
by Paul
Try to execute this procedure using PL/SQL block in SQL Plus or using Microsoft data provider. What value do you receive in ErrorStr?
Posted: Thu 09 Jun 2005 10:21
by ender
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.
Posted: Thu 09 Jun 2005 13:59
by Paul
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;
Posted: Tue 14 Jun 2005 05:40
by ender
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.
Posted: Wed 15 Jun 2005 16:29
by Oleg
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.
Posted: Thu 16 Jun 2005 04:07
by ender
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.
Posted: Thu 16 Jun 2005 07:54
by Oleg
Did you manage to invoke your stored procedure using PL/SQL block in OraDirect .NET?
Posted: Thu 16 Jun 2005 08:11
by ender
Yes. However it is not way i like. This require construction of additional PL/SQL code for each procedure of that kind.
Posted: Thu 16 Jun 2005 08:41
by Oleg
Please provide a sample of your PL/SQL that works with OraDirect .NET.