The third party accesses sap through PI. Precautions: read external Oracle data with SAP

Posted by kimbhoot on Thu, 18 Nov 2021 04:05:18 +0100

Requirement description

As an interface, the external system interacts with SAP through PI. Here are some useful knowledge points


1, Is the Target URL HPPT/HTTPS?

  • If the other party's address is HTTP, the other party's URL address can be directly used in Integration Builder. Confirm with the other party's system personnel in advance whether the firewall can receive messages from the PI
  • If the other party's address is HTTPS, as shown in the figure Communication Channel
    First, you need to import the certificate in this website to PI, Download import step portal
    And confirm the firewall

2, SAP accessing external Oracle data

1. Add configuration on the server first

The path and configuration are as follows (for example, confirm the service_name with Oracle personnel):

2. DBCO creates new connection information

T-CODE:DBCO sets user name and password
The format of connection information is (width): connection name, "HOST:1521 / database name"
preservation

3. Test connection procedure se38: ADB_ TEST_ CONNECTION

4. Processing of date

Oracle Bottom table saved date Data needs to be read or displayed convert	

How ORACLE's date type is displayed depends on NLS_DATE_FORMAT initialization parameter NLS_ DATE_ The format parameter can be set at the following levels

  • Database level - if you want everyone to see data in a certain format, run SQL > alter system set NLS in SQLPLUS_ date_ format=‘yyyy-mm-dd hh24:mi:ss’;

  • SESSION level - if you just want to see a format without affecting the results seen by others, SQL > alter SESSION set NLS_ date_ format=‘yyyy-mm-dd hh24:mi:ss’;
    Note that only the current login status is valid

5. Start query code example

5.1 query statement in sqlplus (example: reading a qualified data)

select db_key,to_char(lch_time, 'YYYYMMDDHH24MISS') from ztest_table where rownum=1 and lch_time between to_date('20211109015959','YYYYMMDDHH24MISS') and to_date( '20211116185959','YYYYMMDDHH24MISS');

5.2 native sql query statement of ABAP program

  • Step 1: open the connection (gv_con_name is the connection name set in DBCO, which can be brought in)
*&---------------------------------------------------------------------*
*&      Form  OPEN_CONNECT
*&---------------------------------------------------------------------*
*       Connect to database
*----------------------------------------------------------------------*
FORM open_connect .

  DATA:  lo_root TYPE REF TO cx_root.
* Get data
  TRY.

      EXEC SQL.
        CONNECT TO :gv_con_name
      ENDEXEC.
*      connection failed
      IF sy-subrc <> 0.
        MESSAGE 'The remote database cannot be connected' TYPE 'E' RAISING link_to_db_failed.
      ENDIF.

    CATCH cx_sql_exception INTO lo_root.
*      connection failed
      MESSAGE 'The remote database cannot be connected' TYPE 'E'.

  ENDTRY.

ENDFORM.                    " OPEN_CONNECT
  • The second step is to obtain the data and transfer it to the user-defined internal table
DATA:lv_day_from type string,
	 lv_day_to type string.
	 
*  lv_day_from;lv_day_to Assign a value to the timestamp according to the actual situation

 TRY.
	
	      EXEC SQL.
	        OPEN ZDBCO_TEST FOR
	        SELECT DB_KEY,
	               TO_CHAR(LCH_TIME,'YYYYMMDDHH24MISS')
	          FROM ZTEST_TABLE
	          WHERE LCH_TIME between TO_DATE(:lv_day_from,'YYYYMMDDHH24MISS') AND TO_DATE(:lv_day_to,'YYYYMMDDHH24MISS')
	
	      ENDEXEC.
	
	  ENDTRY.
	
	  DO .
	
	    TRY.
	        EXEC SQL.
	
	          FETCH NEXT ZDBCO_TEST INTO :LS_TEST
	
	        ENDEXEC.
	
	      CATCH cx_sql_exception INTO cl_sqlerr_ref.
	
	    ENDTRY.
	
	    IF sy-subrc <> 0.
	      EXIT.
	    ENDIF.
	
	    CLEAR gs_data.
	
	    MOVE-CORRESPONDING ls_test TO gs_data.
	    gs_data-table_name = 'ZTEST_TABLE'.
	    APPEND gs_data TO gt_data.
	
	  ENDDO.
	
	  EXEC SQL.
	    CLOSE ZDBCO_TEST
	  ENDEXEC.

  • Step 3: close the database connection after counting
*&---------------------------------------------------------------------*
*&      Form  DB_DISCONNECT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM db_disconnect .

  TYPES  to_exception TYPE REF TO cx_sy_native_sql_error.

  DATA: lo_exception TYPE to_exception,
        lv_con_name  LIKE gv_con_name.

* First get current connection
  TRY.

      EXEC SQL.
        GET CONNECTION :lv_con_name
      ENDEXEC.

    CATCH cx_sy_native_sql_error INTO lo_exception.
*     If current connection not found, direcly return
      RETURN.

  ENDTRY.
* If current connection name is NOT 'ZDBCO_TEST'(Like 'DEFAULT')
* Also directly return, it means no need to do disconnect action
  IF lv_con_name <> gv_con_name.

    RETURN.

  ENDIF.

  EXEC SQL.
    DISCONNECT :gv_con_name
  ENDEXEC.

ENDFORM.                    " DB_DISCONNECT

4.3. If the date field is not maintained in the bottom table, the saved data is 00000000 instead of null, which should be noted during query

select * from ztest_table where lch_date <> '00000000';

summary

The above are some problems encountered in making interfaces. I hope it will help you
Later, there will be a series of tutorials on the interaction of external systems and SAP through PI to make configuration interfaces. Please look forward to it when I sort it out
@Please indicate the source for reprint

Topics: Oracle https sap