Calling Oracle function using SAP ABAP

[yosef_gebremichael]

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

[Former Member · Accepted solution]

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.

[yosef_gebremichael · Accepted solution]

Yekeniyeley dem.....

[horst_keller]

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.

[SuhaSaha]

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

[horst_keller]

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.

[yosef_gebremichael]

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

[horst_keller]

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

[JamesZ]

Hi,

looks SAP note 44977 is helping here?

Best regards,
James