topic Re: Data extraction with PL/SQL in Application Development and Automation Discussions

Data extraction with PL/SQL

Former Member — Thu, 30 Aug 2007 13:12:29 GMT

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

Re: Data extraction with PL/SQL

former_member194669 — Thu, 30 Aug 2007 13:20:35 GMT

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®

Re: Data extraction with PL/SQL

niranjan_yerraguntla — Thu, 30 Aug 2007 13:43:03 GMT

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

Re: Data extraction with PL/SQL

niranjan_yerraguntla — Thu, 30 Aug 2007 13:45:16 GMT

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.

Re: Data extraction with PL/SQL

Former Member — Sun, 31 May 2009 21:25:01 GMT

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.