Calling Stored Procedures with parameters
-
- Posts: 5
- Joined: Fri 04 May 2018 17:13
Calling Stored Procedures with parameters
Hello
I'm using the dbExpress driver for Oracle (6.7.10) and I just encountered a problem using Delphi XE 2 or Seattle
When calling a Stored Procedure with parameters using the TSQLStoredProc component, we received the error message
"Actual number of parameters (1) exceeds the current number of stored procedure parameters (0). Either uncheck the ParamCheck component property or review the parameter list content."
I only got the error when using a user who doesn't have access to oracle objects, but when I using Delphi X2 with version(5.1.3) works fine.
I'm using the dbExpress driver for Oracle (6.7.10) and I just encountered a problem using Delphi XE 2 or Seattle
When calling a Stored Procedure with parameters using the TSQLStoredProc component, we received the error message
"Actual number of parameters (1) exceeds the current number of stored procedure parameters (0). Either uncheck the ParamCheck component property or review the parameter list content."
I only got the error when using a user who doesn't have access to oracle objects, but when I using Delphi X2 with version(5.1.3) works fine.
Re: Calling Stored Procedures with parameters
Please provide a DDL script for creating a stored procedure, the use of which causes the issue
-
- Posts: 5
- Joined: Fri 04 May 2018 17:13
Re: Calling Stored Procedures with parameters
CREATE OR REPLACE PACKAGE PKG_GENERAL AS
TYPE REFCURSOR IS REF CURSOR;
PROCEDURE GET_PROGRAMS
(
P_PROGRAM IN VARCHAR2,
P_RESULT OUT REFCURSOR
);
END PKG_GENERAL
TYPE REFCURSOR IS REF CURSOR;
PROCEDURE GET_PROGRAMS
(
P_PROGRAM IN VARCHAR2,
P_RESULT OUT REFCURSOR
);
END PKG_GENERAL
Re: Calling Stored Procedures with parameters
Thanks you for the information. We tested the operation of our driver using the following code and found no problems:
For further issue investigation, please send the full source code of your test project, execution of which causes the issue.
It is convenient to do this using the e-support form (https://devart.com the "Support"\"Request Support" menu)
Code: Select all
SQLConnection.DriverName := 'DevartOracle';
SQLConnection.Params.Values['User_Name'] := 'scott';
SQLConnection.Params.Values['Password'] := 'tiger';
SQLConnection.Params.Values['DataBase'] := '<your oracle alias>';
SQLConnection.Connected := True;
SQLStoredProc.SQLConnection := SQLConnection;
SQLStoredProc.PackageName := 'PKG_GENERAL';
SQLStoredProc.StoredProcName := 'GET_PROGRAMS';
SQLStoredProc.Active := True;
For further issue investigation, please send the full source code of your test project, execution of which causes the issue.
It is convenient to do this using the e-support form (https://devart.com the "Support"\"Request Support" menu)
-
- Posts: 5
- Joined: Fri 04 May 2018 17:13
Re: Calling Stored Procedures with parameters
You have to pass one or more parameters to got de error.
SQLStoredProc.SQLConnection := SQLConnection;
SQLStoredProc.PackageName := 'PKG_GENERAL';
SQLStoredProc.StoredProcName := 'GET_PROGRAMS';
SQLStoredProc.ParamByName('P_PROGRAM').AsString := 'TEST';
SQLStoredProc.Active := True;
When the errors occurs:
1-My database user does not have access to oracle object and I have to pass one or more parameters to TSQLStoredProc.
When the erros not occurs:
1- My database user is admin and have access to all objects
OR
2- I disable the paramCheck of TSQLStoredProc
SQLStoredProc.SQLConnection := SQLConnection;
SQLStoredProc.PackageName := 'PKG_GENERAL';
SQLStoredProc.StoredProcName := 'GET_PROGRAMS';
SQLStoredProc.ParamByName('P_PROGRAM').AsString := 'TEST';
SQLStoredProc.Active := True;
When the errors occurs:
1-My database user does not have access to oracle object and I have to pass one or more parameters to TSQLStoredProc.
When the erros not occurs:
1- My database user is admin and have access to all objects
OR
2- I disable the paramCheck of TSQLStoredProc
Re: Calling Stored Procedures with parameters
In our test environment, the Oracle user, under which the test connection is established, has the rights to execute the PKG_GENERAL package and we do not receive the error you describe when adding a line to our code:
Please explain, whether we understand correctly that the issue occurs only when using a database user that does not have rights to all Oracle schema objects that contains the PKG_GENERAL package?SQLStoredProc.ParamByName('P_PROGRAM').AsString := 'TEST';
-
- Posts: 5
- Joined: Fri 04 May 2018 17:13
Re: Calling Stored Procedures with parameters
I have 2 users:
- 1 - The first user have GRANT to create, modified and execute Package/procedures/Table this is my DBA User.
- 2 - The second user only have GRANT to EXECUTE the PACKAGE.
Re: Calling Stored Procedures with parameters
In our test environment, the PKG_GENERAL package is created by the user SCOTT. We created the user USERCHECK, and gave him only the following right:
After that, we specified the SchemaName property value as follows:
and successfully executed this code on behalf of the user USERCHECK
Code: Select all
GRANT EXECUTE ON PKG_GENERAL TO USERCHECK
Code: Select all
SQLStoredProc.SQLConnection := SQLConnection;
SQLStoredProc.SchemaName := 'SCOTT';
SQLStoredProc.PackageName := 'PKG_GENERAL';
SQLStoredProc.StoredProcName := 'GET_PROGRAMS';
SQLStoredProc.ParamByName('P_PROGRAM').AsString := 'TEST';
SQLStoredProc.Active := True;