Problem with Torastoredproc execution

clint · Post by **clint** » Mon 23 Jun 2008 18:23

Hi There,

I wonder if someone could please help me with a problem that my colleagues and I are having running a stored procedure using the Torastoredproc component in direct mode ( I think it is dierct mode as we have no Oracle client software installed and the Torastoredproc is using port 1521).

We are using Delphi 2005 with ODAC version 6.25.1.13 (but we also get the same problem with ODAC 6.50).

We have a stored procedure sitting on a Oracle 9.2.0.8 standard edition database. This stored procedure takes 23 parameters, with the last 2 being return parameters.

I can call the stored procedure from an SQL script and it works fine, as well as from a C++ program using Microsoft ADO, so the stored procedure actually runs fine.

The problem that we are getting is that the error "parameter not found" is being returned when we try to execute the stored procedure through the Delphi program using ODAC (direct mode).
The parameter name that is being shown as not being found is the first parameter in the list of 23. From the debugging that I have carried out, the stored procedure is not getting called at all, which would tie in with the error message.

The funny thing is that we can get it working OK through the Delphi program on another Oracle server which is also running Oracle standard edition 9.2.
The only thing that I can think of that is different between the working and non-working Oracle databases is that the non-working scenario is a member of a domain, whereas the working version is just a workgroup.

This is the code for seting up the call to the stored procedure :-

OraStoredProc1.ParamByName('P_OPERATOR').AsString :=RiMaORACLESetNew.MeasOperator;

OraStoredProc1.ParamByName('P_DATE').AsString :=RiMaORACLESetNew.MeasDate;

OraStoredProc1.ParamByName('P_TIME').AsString :=RiMaORACLESetNew.MeasTime;

OraStoredProc1.ParamByName('P_TOOL_SERIAL').AsString :=RiMaORACLESetNew.MeasIdent;

OraStoredProc1.ParamByName('P_TOOL_SIZE').AsFloat :=RiMaORACLESetNew.MeasDiaIn;

OraStoredProc1.ParamByName('P_TOOL_OD').AsFloat :=RiMaORACLESetNew.MeasDiaOut;

OraStoredProc1.ParamByName('P_OVAL_ID').AsFloat :=RiMaORACLESetNew.MeasOvaIn;

OraStoredProc1.ParamByName('P_OVAL_OD').AsFloat :=RiMaORACLESetNew.MeasOvaOut;

OraStoredProc1.ParamByName('P_CONCENTRIC').AsFloat :=RiMaORACLESetNew.MeasConcent;

OraStoredProc1.ParamByName('P_MAX_DIA').AsFloat :=RiMaORACLESetNew.MeasPosMaxDia;

OraStoredProc1.ParamByName('P_TEMP').AsFloat :=RiMaORACLESetNew.MeasTemp;

OraStoredProc1.ParamByName('P_CAL_DATE').AsString :=RiMaORACLESetNew.CalibDate;

OraStoredProc1.ParamByName('P_CAL_TIME').AsString :=RiMaORACLESetNew.CalibTime;

OraStoredProc1.ParamByName('P_CAL_SERIAL').AsString :=RiMaORACLESetNew.CalibIdent;

OraStoredProc1.ParamByName('P_CAL_ID').AsFloat :=RiMaORACLESetNew.CalibDiaIn;

OraStoredProc1.ParamByName('P_CAL_OD').AsFloat :=RiMaORACLESetNew.CalibDiaOut;

OraStoredProc1.ParamByName('P_CAL_OVAL_ID').AsFloat :=RiMaORACLESetNew.CalibOvaIn;

OraStoredProc1.ParamByName('P_CAL_OVAL_OD').AsFloat :=RiMaORACLESetNew.CalibOvaOut;

OraStoredProc1.ParamByName('P_CAL_CONCENTRIC').AsFloat :=RiMaORACLESetNew.CalibConcent;

OraStoredProc1.ParamByName('P_CAL_MAX_DIA').AsFloat :=RiMaORACLESetNew.CalibPosMaxDia;

OraStoredProc1.ParamByName('P_CAL_TEMP').AsFloat :=RiMaORACLESetNew.CalibTemp;

OraStoredProc1.ExecProc;

s1 := OraStoredProc1.ParamByName('P_RETURN_RESULT').AsString;

BodyMakerNo := OraStoredProc1.ParamByName('P_RETURN_MACHINE').AsInteger;

I am thinking that it might be a security issue on the domain installations of Oracle? Any help / pointers would be much appreciated.

Thank you

Clint

Challenger · Post by **Challenger** » Tue 24 Jun 2008 10:53

Do you assign the name in runtime or you have created SQL for calling a procedure in design time?

clint · Post by **clint** » Tue 24 Jun 2008 11:49

Hi - sorry to miss information out, but I am not the actual developer.

The stored procedure name is setup at design time and thus fixed - is that what you are asking?

Thanks
Clint

cardiodid · Post by **cardiodid** » Tue 24 Jun 2008 12:00

I recently had the same problem and I solved it by removing the following bold line in ORA.pas:

procedure TOraStoredProc.SetStoredProcName(Value: string);
begin
if Value FStoredProcName then begin
if not (csReading in ComponentState) then
SQL.Text := '';

FStoredProcName := Trim(Value);

// SQL can be loaded after StoredProcName on reading
if not (csReading in ComponentState) then
if (csDesigning in ComponentState) and (FStoredProcName '') and
(UsedConnection nil) and UsedConnection.Connected
then
PrepareSQL;
end;
end;

clint · Post by **clint** » Tue 24 Jun 2008 14:25

Hi cardiodid - thank you for your post. I am not sure if your problem is exactly the same as the one that we are experiencing but I will look into it.

Maybe we are not doing the correct thing, but I think that we set the stored procedure name at design time and do not do anything at runtime to prepare an SQL statement - we just set the parameters in the Torastoredproc component and call the execute method on the component.

I am sure that there is something that I am missing here, as it works ok on one Oracle installation but not another!!

Thanks again.

Challenger · Post by **Challenger** » Thu 26 Jun 2008 14:22

If you set the StoredProcName in design time the SQL and Params properties of TOraStoredProc are filled automaticaly and are not recreated in run time. So I can not understrand the reasons why you get this exception. Please check that OraStoredProc1 stores all parameters in the *.dfm file.

clint · Post by **clint** » Thu 26 Jun 2008 18:45

OK, thank you for that - I will contact the developers and point them to this thread, so that they can provide more detail and check out your suggestions.

Thanks Again.

Clint

OakersMichael · Post by **OakersMichael** » Fri 27 Jun 2008 07:47

Hi there, Hi Clint it's me from OEG

We use different parameter for data base access during design time and
run time because we need same program for different data base locations
and we will load current parameters from a INI-file.

So in *.dfm file there are fixed paramter like this:
//=====================
object OraSession1: TOraSession
Options.Direct = True
Username = 'abc'
Password = 'abc'
Server = '192.168.0.10:1521:xyz'
LoginPrompt = False
HomeName = 'OraHome92'
Left = 40
Top = 24
end
//======================

and the current paramter (ORACLE_...-parameters) will be loaded at run time in following function:

//==================================
function TForm_ODAC_IF.OpenSession: boolean;
begin
result:=true;
OraSession1.ConnectString:=ORACLE_ConnectString;
OraSession1.Password:=ORACLE_Password;
OraSession1.Server:=ORACLE_Server;
OraSession1.UserName:=ORACLE_UserName;
OraStoredProc1.StoredProcName:=ORACLE_StoredProc;
try
OraSession1.connected:=true;
except
MessageDlg('connect function not ok',mtConfirmation, [mbOK], 0);
end;
end;
//==============================
Is this correct and complete or are only the desing values valid?

Challenger · Post by **Challenger** » Fri 27 Jun 2008 12:59

You should call the PrepareSQL method in order to create parameters if you set the StoredProcName property in run time.

OakersMichael · Post by **OakersMichael** » Fri 27 Jun 2008 14:15

Thank you for support, but on which place I have to call it?
On this way inside the first source code from Clint ?

//========================
...
OraStoredProc1.PrepareSQL;
OraStoredProc1.ExecProc;
...
//========================

OakersMichael · Post by **OakersMichael** » Mon 30 Jun 2008 13:02

After some test we have localized the problem a little bit more.
It seems the program is ok.
If we open DELPHI for desin time, put in all parameter for the data base
into components as properties and read the data base field names when
we execute params from tOraStoredProc1 and get the current params from database the compiled program will have correct access.
But if we change data base location with same program in run time by
using other location parameters (read from ini file, see above) it
seems the field names from data base are lost.
In both cases we use a database with same structure (fields) only on different location and different location parameters.
Is there property to fix this problem and make the program able to
use data bases on differnt locations without a new compiler run?
Will prepareSQL do this, if it is on the rigth place?

Thanks Again.

Plash · Post by **Plash** » Wed 02 Jul 2008 08:42

After you change StoredProcName property parameters in TOraStoredProc component are lost. You can recreate parameters by calling PrepareSQL method of TOraStoredProc. You should call this method before you assign values to parameters.

OakersMichael · Post by **OakersMichael** » Wed 02 Jul 2008 15:31

Thanks you for support!
It seems, the problem is solved and
we can change data base location in run time by loading
database access parameter from ini file when we use
PrepareSQL as first command before parameter loading and
transfer by StoredProcedure.

Thanks again

OakersMichael