Error calling a procedure inside a package of another owner

Error calling a procedure inside a package of another owner

Postby Luciano » Mon 14 Jul 2008 21:13

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).
Luciano
 
Posts: 5
Joined: Mon 14 Jul 2008 21:00

Postby Plash » Tue 15 Jul 2008 07:01

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.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby Luciano » Tue 15 Jul 2008 16:33

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 = <
item
DataType = ftString
Name = 'pMesRef'
ParamType = ptInput
Size = 6
end>
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?
Luciano
 
Posts: 5
Joined: Mon 14 Jul 2008 21:00

Postby Plash » Wed 16 Jul 2008 08:43

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.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby Luciano » Wed 16 Jul 2008 19:13

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.
Luciano
 
Posts: 5
Joined: Mon 14 Jul 2008 21:00

Postby Plash » Fri 18 Jul 2008 14:05

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.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby Luciano » Mon 21 Jul 2008 16:24

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
 
Posts: 5
Joined: Mon 14 Jul 2008 21:00

Postby Luciano » Thu 14 Aug 2008 21:29

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 '='.
Luciano
 
Posts: 5
Joined: Mon 14 Jul 2008 21:00

Postby jsaumell » Mon 16 Aug 2010 14:38

Thanks Luciano,
I spent several hours before I found your post with the workaround (it seems the bug is still active).
jsaumell
 
Posts: 9
Joined: Thu 05 Jun 2008 16:02


Return to dbExpress driver for Oracle