Data extraction with PL/SQL

[Former Member]

Hi Expert

My customer wants to use PL/SQL language for SAP data extraction in Oracle database. He doesn’t want to use ABAP code for this.

In my opinion I think that it’s not correct to do this but I have no solid argument.

Could anyone explain to me why it’s not advisable to use the ORACLE database directly for this data extraction?

Best regards

[former_member194669]

Hi,

PL SQL(Native SQL) statements bypass the R/3 database interface. There is no table logging, and no synchronization with the database buffer on the application server. For this reason, you should, wherever possible, use Open SQL(ABAP SQL) to change database tables declared in the ABAP Dictionary. In particular, tables declared in the ABAP Dictionary that contain long columns with the types LCHR or LRAW should only be addressed using Open SQL, since the columns contain extra, database-specific length information for the column. Native SQL does not take this information into account, and may therefore produce incorrect results. Furthermore, Native SQL does not support automatic client handling. Instead, you must treat client fields like any other.

I think this will be useful for you

a®

[niranjan_yerraguntla]

HI Eric,

We have been using PL/SQL (and Oracle Stored procedure) since 5 years and we have not had any problems. Though SAP recommends to avoid using Native SQL, in some cases, we can use without any problems. We move even files from Oracle to SAP. We are slowly moving towards XI though as number of Stored procedure calls have greatly increased in the past few years.

Niranjan

[Former Member]

Niranjan,

I have a question on how we can use/call the PL/SQL procedures from ABAP programs? Could you please provide me with an example?

Thank You.

[niranjan_yerraguntla]

When I said we are using Stored procedure, it is for external Oracle database and not database of SAP. If your question pertains to underlying Oracle database within SAP, then as the other gentelman said, we should not use PL/SQL language.