topic Re: Error handling in Native SQL for ORACLE in Application Development and Automation Discussions

Error handling in Native SQL for ORACLE

Former Member — Fri, 26 Sep 2008 12:33:12 GMT

Hi,

I have the next code:

DATA instr TYPE char4.

EXEC SQL.

SELECT INSTRUMENTO INTO instr FROM DATOSMAESTROS

END EXEC.

And I want to control if the select has no results. Is it possible with the statement EXCEPTION inside EXEC SQL? Something like that:

DATA instr TYPE char4.

EXEC SQL.

SELECT INSTRUMENTO INTO instr FROM DATOSMAESTROS WHERE id_inst = '01'

EXCEPTION

WHEN NO_DATA_FOUND THEN

:err = SQLCODE

INSERT INTO error( field, code_err) VALUES ( 'instrumento',:err)

END EXEC.

Is this a correct code? If not, how can I do it?

Thank you all,

Cris.

Re: Error handling in Native SQL for ORACLE

Former Member — Fri, 26 Sep 2008 12:36:20 GMT

Hello,

Take a look on this: http://help.sap.com/saphelp_nw04s/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/frameset.htm

Try this:


DATA instr TYPE char4.

EXEC SQL.

SELECT INSTRUMENTO INTO :instr FROM DATOSMAESTROS

ENDEXEC.

Look that I put : inside the native SQL before the intr.

To catch if no data was recovered try this:


DATA instr TYPE char4.

EXEC SQL PERFORMING no_data.

SELECT INSTRUMENTO INTO :instr FROM DATOSMAESTROS

ENDEXEC.

FORM no_data.
  IF instr IS INITIAL.
    MESSAGE ...
  ENDIF.
ENDFORM.

Regards.

Re: Error handling in Native SQL for ORACLE

Former Member — Fri, 26 Sep 2008 12:45:49 GMT

Hello David,

I tought in the solution you propose, but this is only an example, I must handle other errors like insert a null value into a not null column, inconsistent datatypes, etc... and I find the Oracle errors for every case. Is there any way to handle Oracle Exceptions? It would be the ideal solution.

Thanks!!!

Cris.

Re: Error handling in Native SQL for ORACLE

Former Member — Tue, 30 Sep 2008 07:49:59 GMT

Hi Cristina,

you may check cx_sy_native_sql_error and it's messages for the error number

Example :

try .

* your statements


CATCH cx_sy_native_sql_error INTO my_err.
errtext = my_err->get_text( ).

MESSAGE errtext TYPE 'I'. 

endtry.

bye