Calling Stored Procedures with parameters

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
regisvicente
Posts: 5
Joined: Fri 04 May 2018 17:13

Calling Stored Procedures with parameters

Post by regisvicente » Fri 04 May 2018 17:19

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.

MaximG
Devart Team
Posts: 1019
Joined: Mon 06 Jul 2015 11:34

Re: Calling Stored Procedures with parameters

Post by MaximG » Sat 05 May 2018 12:57

Please provide a DDL script for creating a stored procedure, the use of which causes the issue

regisvicente
Posts: 5
Joined: Fri 04 May 2018 17:13

Re: Calling Stored Procedures with parameters

Post by regisvicente » Mon 07 May 2018 14:30

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

MaximG
Devart Team
Posts: 1019
Joined: Mon 06 Jul 2015 11:34

Re: Calling Stored Procedures with parameters

Post by MaximG » Tue 08 May 2018 15:19

Thanks you for the information. We tested the operation of our driver using the following code and found no problems:

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)

regisvicente
Posts: 5
Joined: Fri 04 May 2018 17:13

Re: Calling Stored Procedures with parameters

Post by regisvicente » Wed 09 May 2018 13:01

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

MaximG
Devart Team
Posts: 1019
Joined: Mon 06 Jul 2015 11:34

Re: Calling Stored Procedures with parameters

Post by MaximG » Wed 16 May 2018 11:00

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:
SQLStoredProc.ParamByName('P_PROGRAM').AsString := 'TEST';
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?

regisvicente
Posts: 5
Joined: Fri 04 May 2018 17:13

Re: Calling Stored Procedures with parameters

Post by regisvicente » Wed 16 May 2018 12:59

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.
The errors occurs only with the second user, I guess is not problem of privilege because when I use D7 or XE with old version(5.1.3) works fine .

MaximG
Devart Team
Posts: 1019
Joined: Mon 06 Jul 2015 11:34

Re: Calling Stored Procedures with parameters

Post by MaximG » Thu 17 May 2018 13:13

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:

Code: Select all

GRANT EXECUTE ON PKG_GENERAL TO USERCHECK
After that, we specified the SchemaName property value as follows:

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;
and successfully executed this code on behalf of the user USERCHECK

Post Reply