Skip to Content
0

Calling Oracle function using SAP ABAP

Aug 09, 2017 at 09:30 AM

232

avatar image

I have oracle function as shown below

CREATEORREPLACEFUNCTION simplefunc
(inputINCHAR)
RETURNCHARISBEGINRETURNinput;
END;
/

I went to call it from sap using below ABAP code and I am getting the below error.

Anyone who can help me on solving the problem.

Data: input type c value 'X',
output type c.

EXEC SQL.

EXECUTE PROCEDURE simplefunc(IN :input, OUT :output)

ENDEXEC.

ORA-06550: line 1, column 7:#PLS-00306: wrong number or types of arguments in call to ' SIMPLEFUNC'#ORA-06550: line 1, column 7:#PL/SQL: Statement ignored

Thank you

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
avatar image
Former Member Aug 14, 2017 at 05:40 AM
0

you can also create a stored procedure, which will then call your target function.

CREATE OR REPLACE PROCEDURE simpleproc(input IN CHAR, output OUT CHAR)
IS
BEGIN
input := 'X';
output :=  simplefunc (input);
END;
/

simple use this code changing the procedure name to 'simpleproc'

Data: input type c value 'X',
output type c.

EXEC SQL.

EXECUTE PROCEDURE simpleproc(IN :input, OUT :output)

ENDEXEC.
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Yekeniyeley dem.....

0
Horst Keller
Aug 10, 2017 at 06:21 AM
0

You create a database function with a returning parameter and not a database procedure with IN, OUT, or INOUT parameters.

As far as I know, you cannot call a database function in Native SQL with EXECUTE PROCEDURE, since generally, a database function is not executed by a CALL but by using it in an operand position.

Either change your function into a database procedure with an OUT parameter or try to use your function in an allowed SQL statement directly.

Here is an example that works for HANA (adjust it to Oracle). The example demonstrates the usage of a scalar function in the WHERE clause of a query. If you want to use a function in the FROM clause, it should be a table function and not a scalar function.

DATA in1 TYPE c LENGTH 5.
DATA in2 TYPE c LENGTH 5.
DATA result TYPE demo_expressions-id.
TRY.
    EXEC SQL.
      DROP FUNCTION demo_scalar_function;
    ENDEXEC.
    EXEC SQL.
      CREATE FUNCTION
        demo_scalar_function
          ( IN p1 NVARCHAR(5), IN p2 NVARCHAR(5) )
          RETURNS r NVARCHAR(10) AS
          BEGIN
            r = concat(p1,p2);
          END;
    ENDEXEC.
    EXEC SQL.
      select id
             from demo_expressions
             where char1 = demo_scalar_function(:in1,:in2)
             into :result
    ENDEXEC.
  CATCH cx_sy_native_sql_error INTO DATA(exc).
    ...
ENDTRY.
Show 4 Share
10 |10000 characters needed characters left characters exceeded

Does ADBC not support calling DB-Functions? Afaik DB-Procedure calls are supported

0
I'd say, same as for EXEC SQL.

You can use DB-functions only as permitted by SQL and as far as I know that means using in operand positions and not behind CALL. That's why we need a CDS table function for a AMDP function in order to be usable as data source in Open SQL.

1

Thank you sir for the reply but our current version is SAP ECC 6.0

can you please modify the above case to somewhat fit to our current SAP Version

0

The ABAP part is not relevant for understanding the SQL part.

2
James Zhang
Aug 10, 2017 at 01:31 AM
0

Hi,

looks SAP note 44977 is helping here?

Best regards,
James

Share
10 |10000 characters needed characters left characters exceeded