Skip to Content

Calling Oracle function using SAP ABAP

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

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

    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.
    Add comment
    10|10000 characters needed characters exceeded

  • Aug 10, 2017 at 06:21 AM

    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.
    
    Add comment
    10|10000 characters needed characters exceeded

  • Aug 10, 2017 at 01:31 AM

    Hi,

    looks SAP note 44977 is helping here?

    Best regards,
    James

    Add comment
    10|10000 characters needed characters exceeded