topic Re: Calling oracle procedure from ABAP program in Application Development and Automation Discussions

Calling oracle procedure from ABAP program

Former Member — Fri, 10 Aug 2007 12:01:53 GMT

There is a requirement to call the oracle stored procedure with some input parameter from ABAP program.

Suppose we have order number which is entered by the user in ABAP program.

This order number needs to be passed to oracle stored procedure as an input parameter in the same ABAP program.

We can call the oracle stored procedure without parameter using the following syntax

exec sql

execute procedure <procedure name>

end exec.

But do not know how to execute the oracle stored procedure with some input parameter?

Re: Calling oracle procedure from ABAP program

Former Member — Fri, 10 Aug 2007 12:17:14 GMT

You can use EXEC SQL. command to write Oracle Queries from ABAP.

Following is the sample code.

DATA: exc_ref TYPE REF TO cx_sy_native_sql_error,

error_text TYPE string.

TRY.

EXEC SQL.

INSERT INTO scarr

(MANDT, CARRID, CARRNAME, CURRCODE, URL)

VALUES ('000', 'FF', 'Funny Flyers', 'EUR',

'http://www.ff.com');

INSERT INTO scarr

(MANDT, CARRID, CARRNAME, CURRCODE, URL)

VALUES ('000', 'EF', 'Easy Flyers', 'EUR',

'http://www.ef.com');

ENDEXEC.

CATCH cx_sy_native_sql_error INTO exc_ref.

error_text = exc_ref->get_text( ).

MESSAGE error_text TYPE 'I'.

ENDTRY.

Reward Points if usefull.

Thanks and regards,

Veerendranath Maddula.

Re: Calling oracle procedure from ABAP program

Former Member — Fri, 10 Aug 2007 12:19:28 GMT

Apologies , following is the correct code.

This will definetly solve your problem.

DATA scarr_carrid TYPE scarr-carrid.

SELECT-OPTIONS s_carrid FOR scarr_carrid NO INTERVALS.

DATA s_carrid_wa LIKE LINE OF s_carrid.

DATA name TYPE c LENGTH 20.

TRY.

EXEC SQL.

CREATE FUNCTION selfunc( input CHAR(3) )

RETURNING char(20);

DEFINE output char(20);

SELECT carrname

INTO output

FROM scarr

WHERE mandt = '000' AND

carrid = input;

RETURN output;

END FUNCTION;

ENDEXEC.

LOOP AT s_carrid INTO s_carrid_wa

WHERE sign = 'I' AND option = 'EQ'.

TRY.

EXEC SQL.

EXECUTE PROCEDURE selfunc( IN :s_carrid_wa-low,

OUT :name )

ENDEXEC.

WRITE: / s_carrid_wa-low, name.

CATCH cx_sy_native_sql_error.

MESSAGE `Error in procedure execution` TYPE 'I'.

ENDTRY.

ENDLOOP.

EXEC SQL.

DROP FUNCTION selfunc;

ENDEXEC.

CATCH cx_sy_native_sql_error.

MESSAGE `Error in procedure handling` TYPE 'I'.

ENDTRY.

Reward points if usefull.

Thanks and regards,

Veerendranath.