topic Re: Problem with native SQL on Oracle in Application Development and Automation Discussions

Problem with native SQL on Oracle

Former Member — Tue, 18 Oct 2011 10:31:18 GMT

Hi all,

i've got a problem in storing records to a SAP external Oracle DB.

Between the EXECSQL/ENDEXEC, i use the conversion funtion TO_DATE(x,y). ST05 shows, that the Statement is truncated

after the closing bracket, for example:

EXECSQL.

INSERT INTO oradb ( F1, F2, F3 ) VALUES ( TO_DATE('22.12.2012', 'DD.MM.YYYY'), 'A', 'B' )

ENDEXEC.

results in SQL Trace

INSERT INTO oradb (F1, F2, F3) VALUES(

TO_DATE ( '22.12.2012', 'DD.MM.YYYY'

)

It seems to me, that something can't resolve the brackets..or the Oracle DATE type can't be handled?

Has anyone a idea how to make it right? Do i have to use Escape Sequences for the brackets or in the end a stored procedure?

Source DB (SAP CRM 7.0) is Oracle 10.2, destination is oracle 11, if it matters. The connection between the DB Servers works definetly.

Thanks in advance for the answers!!

Cheers, Jens

Re: Problem with native SQL on Oracle

former_member212713 — Fri, 21 Oct 2011 12:49:45 GMT

This message was moderated.

Re: Problem with native SQL on Oracle

Former Member — Fri, 21 Oct 2011 13:03:16 GMT

Hi Melieh,

i've read the SAP documentation already

At the moment, i got only one Oracle DB, because the system landscape (utilities sector) is very complicated and several DB providers and decentralised and clustred systems are involved. It would take weeks to access another DB.

The DB conection is no problem. It is possible to INSERT with host variables formatted in ORA specific format, respectively to write DATE values after ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD'. Only the TO_DATE() and TO_NUMBER() native SQL functions have been cutted in ST05 and i got a "invalid year" ORA Exception.

Cheers, Jens

Edited by: Jens Iggena on Oct 21, 2011 3:07 PM

Re: Problem with native SQL on Oracle

former_member212713 — Fri, 21 Oct 2011 14:56:08 GMT

Hi Jens;

I think You may try different way.

Firstly ; You can stored procedure at other DB(Oracle ) for insert process.

Stored Procedure :

1. Input parameters : f1 value, f2 value, f3 value.

2. Output parameters: Insert status

Secondly ; Call Procedure by SAP using native sql.

I tried this method 6 years ago. It's perfectly works.

Best regards.

Re: Problem with native SQL on Oracle

Sandra_Rossi — Sat, 22 Oct 2011 17:46:43 GMT

Hi Jens,

IMHO, SAP mostly does not interpret the native SQL queries, but here, maybe you have found a SAP kernel error, did you check the SAP notes, or asked the SAP support?

Sandra

Re: Problem with native SQL on Oracle

former_member212713 — Tue, 25 Oct 2011 14:17:45 GMT

You can find exam, below;


  SELECT SINGLE CON_NAME INTO WCONNAME FROM DBCON.

  EXEC SQL.
    CONNECT TO :wconname as 'A1'
  ENDEXEC.
  IF SY-SUBRC <> 0.
    RAISE EXP_04.
*    CONNECTION FAILURE
  ENDIF.
  EXEC SQL.
    EXECUTE PROCEDURE G_Z_xxx(IN :WEQUNR , IN :WEQART , IN :WEQTYP , IN :WMATNR , IN :WSERNR , IN :WBAGNES ,
    IN :WUSERNAME , OUT :WRESULT)
  ENDEXEC.
  EXEC SQL.
    DISCONNECT  'A1'
  ENDEXEC.

G_Z_xxx procedure insert to table side of the Oracle.

Best regards.

Re: Problem with native SQL on Oracle

Former Member — Tue, 25 Oct 2011 18:48:56 GMT

Hi Melih,

i considered to write a stored procedure and i know how to create it

It is a option, but throughout the complicated approval process, this should be the last way. One stored proc would not be a great problem, but we have a very large interface and a lot of procedures to realize to keep everything well designed.

Cheers, Jens