topic Re: Executing stored function using Native SQL in Application Development and Automation Discussions

Executing stored function using Native SQL

Former Member — Wed, 23 Nov 2005 15:36:00 GMT

In SAP documentation, I can see how to execute an Oracle stored procedure in an external database using:

EXEC SQL.

EXECUTE PROCEDURE procname

ENDEXEC.

I want to execute a stored FUNCTION within a package. I have tried executing it using the syntax for PROCEDURE above and get a "ORA-06550 wrong type or number of arguments" error. Since the function has one argument in and one argument out, I think I am counting the number of arguments correctly. Has anyone executed from within ABAP an Oracle function stored in an external database?

Thanks

Janice Ishee

Re: Executing stored function using Native SQL

Former Member — Wed, 23 Nov 2005 15:49:47 GMT

Hi Janice,

Here is an example that can help you:

EXEC SQL PERFORMING get_ora.

SELECT ora_field1, ora_field2, ora_field3,

INTO :gs_data-field1,

:gs_data-field12,

:gs_data-field13,

FROM oraserv.sv_abcde@xyz

WHERE ora_field1 = :G_FIELD1

ENDEXEC.

Note the use of colon ( and the comma after each field.

If this helps, please remember to award points and close the post.

Cheers,

Bhanu

Re: Executing stored function using Native SQL

Former Member — Wed, 23 Nov 2005 16:26:35 GMT

Janice - from help.sap.com:

Stored Procedures

The command EXECUTE PROCEDURE proc allows you to call a procedure stored in the database. When you call it, you can pass a list of host variables as parameters. When yuo call a procedure, you must specify for each parameter whether it is an input parameter ( IN), output parameter (OUT) or changing parameter (INOUT).

Example

Calling a Procedure:

DATA Y TYPE I VALUE 300. 

DATA Z TYPE I.

EXEC SQL.
INSERT INTO AVERI_CLNT (CLIENT, ARG1, ARG2, ARG3)
VALUES ('000', 9, 2, 47)
ENDEXEC.

EXEC SQL.
CREATE OR REPLACE PROCEDURE PROC1 (X IN NUMBER) IS
BEGIN
UPDATE AVERI_CLNT SET ARG3 = ARG3 + X;
END;
ENDEXEC.
EXEC SQL.
CREATE OR REPLACE PROCEDURE PROC2 (X IN NUMBER, Y OUT NUMBER) IS
BEGIN
SELECT ARG3 INTO Y
FROM AVERI_CLNT
WHERE CLIENT = '000' AND ARG1 = 9 AND ARG2 = 2;
UPDATE AVERI_CLNT SET ARG3 = ARG3 - X;
END;
ENDEXEC.
EXEC SQL.
EXECUTE PROCEDURE PROC1 ( IN :Y )
ENDEXEC.
EXEC SQL.
EXECUTE PROCEDURE PROC2 ( IN :Y, OUT :Z )
ENDEXEC.
IF SY-SUBRC <> 0 OR Z <> 347.
WRITE: / 'Wrong result for EXECUTE PROCEDURE:', Z.
ENDIF.
EXEC SQL.
DROP PROCEDURE PROC1
ENDEXEC.
EXEC SQL.
DROP PROCEDURE PROC2
ENDEXEC.

Rob

Re: Executing stored function using Native SQL

Former Member — Wed, 23 Nov 2005 17:17:01 GMT

Thanks. I have seen and tried this syntax. I am thinking my problem is that, within the external Oracle database, I am trying to execute a function rather than a procedure. As you describe, the procedure syntax is

EXECUTE PROCEDURE procname ( IN in_param, OUT out_param ).

Using this syntax I am getting a wrong number or type of parameters error. I am thinking it might be the syntax of calling a function would be more like:

out_param = funcname ( in_param ).

I am not defining my function within ABAP but it exists in the Oracle database. Have you had any success executing a function?

Re: Executing stored function using Native SQL

chaouki_akir — Fri, 12 Sep 2008 07:21:05 GMT

Hello,

do you find a solution ?

Cordialeme,nt,

chaouki.

Re: Executing stored function using Native SQL

Former Member — Fri, 27 Feb 2009 15:09:49 GMT

Hi experts,

have you find a solution ?

Thanks

Claudio