Error calling a procedure inside a package of another owner

[Luciano]

I'm using TSQLStoredProc component and Oracle 10g. When I call a procedure inside a package of another owner I always get a error 'Identifier XXXX (package.proc) must be declared' because It doesn´t put the owner of the package in front of the procedure calling, like OWNER.PACKAGE.PROCEDURE (I saw this in the TSQLMonitor component).

[Plash]

Please check that you have set the owner name to the SchemaName property of TSQLStoredProc, the package name to the PackageName property, and a name of a stored procedure inside the package to the StoredProcName property.

Specify the exact version of DbxOda and Delphi.

[Luciano]

My Delphi is 2007. I´m using the file dbexpoda40.dll (4.20.0.10) All the properties are filled:

object SQLStoredProcExcluiZerado: TSQLStoredProc
SchemaName = 'INFOR'
MaxBlobSize = -1
Params =
PackageName = 'P_PRODJUIZ'
SQLConnection = DataModuleBD.SQLConnectionInfor
StoredProcName = 'EXCLUIBOLETINSZERADOS'
Left = 600
Top = 144
end

The error occur in the ExecProc command:

SQLStoredProcExcluiZerado.Params.ParamByName ('pMesRef').Value := '200806';
ExecProc;

My package is Ok, because if I Login using the owner of the package, in the TSQLConnection, this work!! Then the problem can be the owner of the package.

Another problem: Functions inside a package don´t work too, I think because the same reason. How can I get the return value? Is there a 'RETURN_VALUE' parameter?

[Plash]

Maybe the user doesn't have rights to execute stored procedures of another owner. Try to grant these rights by executing the following statement under the SYSDBA user:

Code: Select all

GRANT EXECUTE ANY PROCEDURE TO 

There is the RESULT parameter that you can use to get a return value.

[Luciano]

I tried this GRANT but still don´t work. You can try to test calling ANY procedure or function of another owner and you will see the error. I tried to use the 'RESULT' parameter and don´t work too. PS.: Thanks for the help, my English is not very good because I'm from Brazil.

[Plash]

We could not reproduce the problem. Please send to dbxoda*devart*com a complete small sample that demonstrates the problem, including the script for creating database objects.

TSQLStoredProc component does not create parameters for a stored procedure if it cannot find this procedure.

[Luciano]

I will send a sample. The TSQLStoredProc found the parameters at design, the problem occur only when I run the application, It says 'Identifier 'P_PRODJUIZ.EXCLUIBOLETINSZERADOS' must be declared'

[Luciano]

Here is a solution for anyone who is interesting:


There is a bug in dbExpress components. The components add to the stored procedure name a value of the Schema property if it is set, or a value of the PackageName property of it is set. But dbExpress cannot add both properties.

You can resolve the problem in two ways:

1. If you always use objects from INFOR schema in your application, you can execute the following SQL statement after opening a connection:

ALTER SESSION SET CURRENT_SCHEMA = INFOR

Then you should set the Schema and PackageName properties of the TSQLStoredProc component to the corresponding values, and the StoredProcName property to the name of procedure without schema and package names.

2. You can use the TSQLQuery component with correct SQL to execute the stored procedure. Assign the following to the SQL property of TSQLQuery:

begin
INFOR.PackTest.proctest(:var_in);
end;


For stored functions you can use only the first way because dbExpress components treat ':=' in the text of the SQL property as a parameter with name '='.

[jsaumell]

Thanks Luciano,
I spent several hours before I found your post with the workaround (it seems the bug is still active).