Devart Forums

Posted: **Mon 11 Apr 2011 12:08**

Hi,

In our project we must to deal with Oracle and SQL Server models. For a concrete query we need to create a Function or Stored Procedure in order to get a list of values according to several specified parameters.

In our Oralce database we return an Output parameter to get a select result, but this parameter is it not required in SQL Server, so the number of parameters differ in both models.

How could we solve this problem? Is it possible to get the same number of parameters in both databases?

Any advice in this regard will be welcome.

Thanks in advance,

Dominik.

Posted: **Mon 11 Apr 2011 14:28**

Try to use devart:ResultSetParameter instead of the default output parameter as it is described here in our blog.

Posted: **Mon 11 Apr 2011 15:06**

Still have the same problem:

{"ORA-06550: line 2, column 3:\nPLS-00306: wrong number or types of arguments in call to 'OBTENER_ARBOLPERFIL'\nORA-06550: line 2, column 3:\nPL/SQL: Statement ignored"}

SQL SERVER:

Code: Select all

CREATE  procedure [dbo].[OBTENER_ARBOLPERFIL]
(
  @IdPerfil INT, 
  @Productos varchar(20)
)
AS 
begin

SELECT AP_CI, AP_CS_CI, AP_MSC, AP_RNG, AP_PROD
FROM Arbolperfil(@IdPerfil, @Productos)
WHERE AP_MSC IS NOT NULL

END

Code: Select all

ORACLE:

Code: Select all

CREATE OR REPLACE FUNCTION "OBTENER_ARBOLPERFIL" (

  IdPerfil IN NUMBER,
  Productos         IN VARCHAR2
) RETURN SYS_REFCURSOR IS
  ASeguridad_Out SYS_REFCURSOR;
BEGIN
  OPEN ASeguridad_Out FOR
  SELECT AP_CI, AP_CS_CI, AP_MSC, AP_RNG, AP_PROD
  FROM TABLE(Arbolperfil(IdPerfil, Productos))
  WHERE AP_MSC IS NOT NULL;
END OBTENER_ARBOLPERFIL;

Code: Select all

Posted: **Tue 12 Apr 2011 08:13**

Sorry, it works!

It was my fault. The previous function definition in Oracle was a test. The real stored procedure is defined as follows:

Code: Select all

CREATE OR REPLACE PROCEDURE "OBTENER_ARBOLPERFIL" (
  IdPerfil IN NUMBER,
  Productos         IN VARCHAR2,
  ASeguridad_Out OUT SYS_REFCURSOR) IS
BEGIN
  OPEN ASeguridad_Out FOR
  SELECT AP_CI, AP_CS_CI, AP_MSC, AP_RNG, AP_PROD
  FROM TABLE(Arbolperfil(IdPerfil, Productos))
  WHERE AP_MSC IS NOT NULL;
END OBTENER_ARBOLPERFIL;

And now, it works ok.

Thank you very much,

Dominik.

Posted: **Tue 12 Apr 2011 10:51**

The article I have referenced in the previous post describes the solution for stored functions as well.
Glad to hear the issue is resolved.

Devart Forums

Wrong number or types of arguments in call to PROCEDURE

Wrong number or types of arguments in call to PROCEDURE