4

I need to recreate some SAP stored procedures in Oracle. I've been trying to find tutorials, similar questions, examples, etc about this but apparently no one had to do this before

What Oracle SQL query can be similar to this SAP query ?

SELECT * FROM A
 INTO CORRESPONDING FIELDS OF TABLE B
 FOR ALL ENTRIES IN C
 WHERE a = C-a
 AND x = y.

 LOOP AT B INTO D.
   D-b = E-b.

 INSERT c FROM D.
 IF SY-SUBRC <> 0.
   WRITE: / 'error on insert', D-b, D-a.
 ENDIF.

Any help will be appreciated, Thanks.

Boghyon Hoffmann
  • 17,103
  • 12
  • 72
  • 170
frankie015
  • 358
  • 2
  • 10

2 Answers2

4

I recommend you to use transaction 'ST05' to trace your program. This tool will show details of the queries on the database including the exact SQL executed.

EDIT: As a demonstration of the queries generated by SAP for Oracle let's execute this code and trace it with transaction 'ST05'. Remember to run 'ST05' before executing the program.

tables: mara.
data: it_mara type standard table of mara,
      it_eina type standard table of eina.

select-options so_matnr for mara-matnr.

start-of-selection.

select matnr from mara into corresponding fields of table it_mara 
up to 100 rows where matnr in so_matnr.

check sy-subrc eq 0.

select * from eina into table it_eina for all entries in it_mara
  where matnr eq it_mara-matnr.

After execution check the output in transaction 'ST05':

enter image description here

If you want more details select an SQL statement in the screen and then click the button 'Explain'. You will see the following:

enter image description here

For better reference on transaction 'ST05' check this link.

Hope it helps.

Nelson Miranda
  • 5,484
  • 5
  • 33
  • 54
1

The FOR ALL ENTRIES statement usually produces many queries which results are then grouped by UNION or UNION ALL.

Here is a really nice analysis for Microsoft SQL Server.

Because of the fact that UNION and UNION ALL are part of SQL standard I think it is implemented exactly the same for any other SQL database.

[EDIT]

As Mr Miranda stated it looks differently when it comes to Oracle database. I googled a bit and found this article where it is said that IN-LISTs are used which seems also to be plausible.

Jagger
  • 10,350
  • 9
  • 51
  • 93
  • The SAP Notes 652634 mentioned in the article seems to apply only to MS SQL Server and not to Oracle, meaning that, the UNION feature is only available to SQL Server. We use Oracle and we do not experiment that behaviour in our FAE queries. – Nelson Miranda Sep 21 '15 at 14:19
  • Are you absolutely sure that `UNION` and `UNION ALL` are not part of SQL standard? I think that they surely are but [check](http://www.w3schools.com/sql/sql_union.asp) it by yourself. – Jagger Sep 21 '15 at 14:27
  • I'm not talking about the UNION feature of the standard SQL. I'm talking about the fact that queries generated by SAP in Oracle do not make use of UNION when we use FAE. In fact, if you check the SAP OSS #652634, it specifies the component 'BC-DB-MSS Microsoft SQL Server', but check it yourself http://sap-consalt.ru/vanilla/discussion/66533/note-652634-for-all-entries-performance-with-microsoft-sql-server/p1. As I stated, we use Oracle and we don't experiment that behaviour. None of our FAE produces UNION queries. – Nelson Miranda Sep 21 '15 at 14:44
  • Could you then be so kind and tell what kind of Oracle queries are produced, because I do not see any other possibility than those three described in the article about SQL Server. For FAE there have to be as many queries generated as there are entries in the internal table used in FAE. – Jagger Sep 22 '15 at 06:18
  • @NelsonMiranda I updated the answer based on your comments. – Jagger Sep 22 '15 at 06:26