TRY ... CATCH doesn't work with EXEC SQL?

former_member195402 · ‎10-20-2008

When I created a native SQL statement, I knew before, that it would take a few tries to get it working. I decided to use exception classes to avoid short dumps, but it didn't work.

Following code causes a short dump because of the dot after :xp_stras in the native SQL SELECT statement:

try.

EXEC SQL PERFORMING list.

SELECT LIFNR, NAME1, STRAS

INTO :xsl

FROM LFA1

WHERE UPPER( NAME1 ) LIKE :xp_name1

AND UPPER( STRAS ) LIKE :xp_stras.

ENDEXEC.

catch CX_SY_NATIVE_SQL_ERROR.

write: /1 'CX_SY_NATIVE_SQL_ERROR'.

catch CX_SY_SQL_ERROR.

write: /1 'CX_SY_SQL_ERROR'.

catch CX_DYNAMIC_CHECK.

write: /1 'CX_DYNAMIC_CHECK'.

catch CX_ROOT.

write: /1 'CX_SY_ROOT'.

endtry.

In ST22 you can see

Name of runtime error: DBIF_DSQL2_SQL_ERROR

Exception:

The name of the exception is empty, in other short dumps you can find there the name of the exception class. Maybe that's the reason for CATCH does'nt work, but I don't understand it.

Online help for EXEC SQL says:

Catchable Exceptions

CXSY_NATIVE_SQL_ERROR_

...

Cause: SQL-Error at the execution of a Native SQL-command.

Runtime Error: DBIF_DSQL2_SQL_ERROR

We have SAP ECC 6.0 with SAP_BASIS rel. 700 lvl. 0013.

If You have an idea why CATCH doesn't work, please tell me!

Regards,

Klaus

Former Member · ‎10-20-2008

You can start by not putting extra spaces between NAME1 and the parentheses. Try:

WHERE UPPER(NAME1)  =  :NAME

(and STRAS as well).

Also check that the declaration of XSL corresponds to what you are selecting.

Rob

Edited by: Rob Burbank on Oct 20, 2008 2:11 PM

former_member195402 · ‎10-21-2008

Thank You for Your answer, Rob,

but my problem is not the native SQL statement. I know this error and have got a fine working code in a correct running program.

My problem is, that CATCH doesn't catch this error and a short dump occurs while developing a new native SQL statement. TRY ... CATCH ... are out of order, and I want to know the reason.

Edited by: Klaus Babl on Oct 21, 2008 6:23 AM

Former Member · ‎10-21-2008

Odd...

I ran your code (in ECC 6.0 and 4.7) and it caught the exception. See what happens if you try this:

REPORT ztest LINE-SIZE 80 MESSAGE-ID 00.

TABLES: lfa1.

DATA: BEGIN OF xsl,
        lifnr TYPE lfa1-lifnr,
        name1 TYPE lfa1-name1,
        stras TYPE lfa1-stras,
      END   OF xsl.

DATA: xp_name1 TYPE lfa1-name1,
      xp_stras TYPE lfa1-stras.

TRY.
    EXEC SQL PERFORMING list.
      SELECT LIFNR, NAME1, STRAS
      INTO :xsl
      FROM LFA1
      WHERE UPPER( NAME1 ) LIKE :xp_name1
      AND UPPER( STRAS ) LIKE :xp_stras.
    ENDEXEC.

  CATCH cx_sy_native_sql_error.
    WRITE: /1 'CX_SY_NATIVE_SQL_ERROR'.
  CATCH cx_sy_sql_error.
    WRITE: /1 'CX_SY_SQL_ERROR'.
  CATCH cx_dynamic_check.
    WRITE: /1 'CX_DYNAMIC_CHECK'.
  CATCH cx_root.
    WRITE: /1 'CX_SY_ROOT'.
ENDTRY.

*&---------------------------------------------------------------------*
*&      Form  LIST
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM list .

ENDFORM.                    " LIST

Rob

Edited by: Rob Burbank on Oct 21, 2008 9:16 AM

former_member195402 · ‎10-22-2008

Hi Rob,

I tried out Your original code as program ZTEST5 and got a short dump (sorry, some of the short dump symbols are formatting this post in a wrong way ...):

Short text

An SQL error occurred when executing Native SQL.

What happened?

The error 919 occurred in the current database connection "DEFAULT".

What can you do?

Note down which actions and inputs caused the error.

To process the problem further, contact you SAP system

administrator.

Using Transaction ST22 for ABAP Dump Analysis, you can look

at and manage termination messages, and you can also

keep them for a long time.

How to correct the error

Database error text........: "ORA-00919: invalid function"

Database error code........: 919

Triggering SQL statement...: "FETCH NEXT "

Internal call code.........: "[DBDS/NEW DSQL]"

Please check the entries in the system log (Transaction SM21).

If the error occures in a non-modified SAP program, you may be able to

find an interim solution in an SAP Note.

If you have access to SAP Notes, carry out a search with the following

keywords:

"DBIF_DSQL2_SQL_ERROR" " "

"ZTEST5" or "ZTEST5"

"START-OF-SELECTION"

If you cannot solve the problem yourself and want to send an error

notification to SAP, include the following information:

1. The description of the current problem (short dump)

To save the description, choose "System->List->Save->Local File

(Unconverted)".

2. Corresponding system log

Display the system log by calling transaction

Restrict the time interval to 10 minutes befor

after the short dump. Then choose "System->List->

(Unconverted)".

3. If the problem occurs in a problem of your own

program: The source code of the program

In the editor, choose "Utilities->More

Utilities->Upload/Download->Download".

4. Details about the conditions under which the e

actions and input led to the error.

System environment

SAP-Release 700

Application server... "lux01617"

Network address...... "195.217.80.104"

Operating system..... "Linux"

Release.............. "2.6.5-7.283-smp"

Hardware type........ "x86_64"

Character length.... 16 Bits

Pointer length....... 64 Bits

Work process number.. 5

Shortdump setting.... "full"

Database server... "lux09208"

Database type..... "ORACLE"

Database name..... "D10"

Database user ID.. "SAPDAT"

Char.set.... "C"

SAP kernel....... 700

created (date)... "Dec 2 2007 20:18:08"

create on........ "Linux GNU SLES-9 x86_64 cc3.3.3"

Database version. "OCI_102 (10.2.0.2.0) "

Patch level. 138

Patch text.. " "

Database............. "ORACLE 9.2.0.., ORACLE 10.1.0.., ORACLE 1

SAP database version. 700

Operating system..... "Linux 2.6"

Memory consumption

Roll.... 16192

EM...... 16759392

Heap.... 0

Page.... 98304

MM Used. 694160

MM Free. 3493088

User and Transaction

Client.............. 500

User................ "Z0000D2P"

Language key........ "E"

Transaction......... "SE38 "

Transactions ID..... "48FE92EB921F51C3E1000000C3D95068"

Program............. "ZTEST5"

Screen.............. "SAPMSSY0 1000"

Screen line......... 6

Information on where terminated

Termination occurred in the ABAP program "ZTEST5" - in "START-OF-SELECTION".

The main program was "ZTEST5 ".

In the source code you have the termination point in line 16

of the (Include) program "ZTEST5".

Source Code Extract

Line SourceCde

1 REPORT ztest LINE-SIZE 80 MESSAGE-ID 00.

2

3 TABLES: lfa1.

4

5 DATA: BEGIN OF xsl,

6 lifnr TYPE lfa1-lifnr,

7 name1 TYPE lfa1-name1,

8 stras TYPE lfa1-stras,

9 END OF xsl.

10

11 DATA: xp_name1 TYPE lfa1-name1,

12 xp_stras TYPE lfa1-stras.

13

14 TRY.

15 EXEC SQL PERFORMING list.

>>>>> SELECT LIFNR, NAME1, STRAS

17 INTO :xsl

18 FROM LFA1

19 WHERE UPPER( NAME1 ) LIKE :xp_name1

20 AND UPPER( STRAS ) LIKE :xp_stras.

21 ENDEXEC.

22

23 CATCH cx_sy_native_sql_error.

24 WRITE: /1 'CX_SY_NATIVE_SQL_ERROR'.

25 CATCH cx_sy_sql_error.

26 WRITE: /1 'CX_SY_SQL_ERROR'.

27 CATCH cx_dynamic_check.

28 WRITE: /1 'CX_DYNAMIC_CHECK'.

29 CATCH cx_root.

30 WRITE: /1 'CX_SY_ROOT'.

31 ENDTRY.

32

33 &----

34 *& Form LIST

35 &----

Edited by: Klaus Babl on Oct 22, 2008 6:42 AM

Former Member · ‎10-22-2008

Well, it worked in our system. It may be an Oracle issue rather than ABAP. We are on Oracle 09.01.0003.

Rob

former_member195402 · ‎10-23-2008

Thanks for your help. Rob.

I'm watching coming SAP notes and hope to find a solution there.

Because of starting my vacation I can't watch this thread any longer!

Edited by: Klaus Babl on Oct 23, 2008 6:38 AM

Former Member · ‎06-09-2009

Hello Klaus,

Did you find out what is wrong with this no-exception short dump issue? I'm having the same problem. My ABAP program is doing an SELECT FOR UPDATE NOWAIT, and when the Oracle based table is locked by another process, a short dump is generated with no exception, DBIF_DSQL2_SQL_ERROR

error and "ORA-00054: resource busy and acquire with NOWAIT specified" Oracle error. Just as you, the exception cant be caught with try/catch/endcatch because it seems to be missing. I'm using Oracle 10.2.0.4.0.

Best regards and thanks on advance.

Former Member · ‎07-19-2011

problem is 'performing' is not allowed for TRY-ENDCATCH.

former_member212713 · ‎07-19-2011

Hi Klaus;

I look your code but as if I didnt see "Connect", "Set Connection" as commands.

I know; If we will use Native SQL and connect to different DB, We should use "Set Connection" and "Connect" commands.

Maybe below link help you.

Best regards.

http://help.sap.com/abapdocu_70/en/ABAPEXEC_CONNECTION.htm

Former Member · ‎10-20-2008

Hi,

Catch should work with Native SQL....

Change your SQL , to remove period at the end of statement.

EXEC SQL PERFORMING list.

SELECT LIFNR, NAME1, STRAS

INTO :xsl

FROM LFA1

WHERE UPPER( NAME1 ) LIKE :xp_name1

AND UPPER( STRAS ) LIKE :xp_stras

ENDEXEC.

Refer to this thred:

former_member195402 · ‎10-21-2008

Thanks, I followed Your link, but the thread didn't help me to find out, why CATCH doesn't work.

I also tried out CATCH ... INTO ..., but there was no difference.

Maybe it's database dependent, why CATCH works or works not. We are using ORACLE 10.2.0.2.0.