Error calling a procedure inside a package of another owner
Error calling a procedure inside a package of another owner
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).
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?
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?
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:
There is the RESULT parameter that you can use to get a return value.
Code: Select all
GRANT EXECUTE ANY PROCEDURE TO
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 '='.
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 '='.