* DBCO: Transaktion zur Pflege der Datenbankverbindung zur Oracle-DB
* DBACOCKPIT
* DBCON: Tabelle der Datenbankverbindungen
* http://www.saptechnical.com/Tutorials/Basis/Oracle/RetrieveData.htm
* http://sqlines.com/oracle-to-sql-server/to_char_datetime
* https://archive.sap.com/discussions/thread/33542
* https://archive.sap.com/discussions/thread/107384
* https://blogs.sap.com/2012/12/28/native-sql-its-use-with-database-connection-in-sap/
* https://help.sap.com/http.svc/rc/abapdocu_750_index_htm/7.50/de-DE/abapexec_connection.htm
* Beispieldatentyp für verschiedene Oracle-Datentypen
TYPES: BEGIN OF ty_s_tableline,
col_number TYPE i, " NUMBER
col_number10_2 TYPE p LENGTH 10 DECIMALS 2, " NUMBER (10,2)
col_number5 TYPE p LENGTH 5 DECIMALS 0, " NUMBER (5)
col_char TYPE c LENGTH 16, " VARCHAR2 (16 Char)
col_date_conv TYPE c LENGTH 19, " DATE ('YYYY-MM-DD HH24:MI:SS')
col_date_conv2 TYPE c LENGTH 15, " DATE ('YYYYMMDD HH24MISS')
END OF ty_s_tableline.
TYPES: ty_it_table TYPE STANDARD TABLE OF ty_s_tableline WITH DEFAULT KEY.
* DB Connection zur Oracle DB
PARAMETERS: p_dbs TYPE dbcon-con_name DEFAULT 'MYDBCON'.
PARAMETERS: p_par1 TYPE matnr DEFAULT '12345'.
START-OF-SELECTION.
* Verbindungsinfos zur DB Connection lesen
DATA: lv_dbtype TYPE dbcon_dbms.
SELECT SINGLE dbms
INTO @lv_dbtype
FROM dbcon
WHERE con_name = @p_dbs.
IF sy-subrc = 0.
* Repräsentiert die Connection eine Oracle-DB?
IF lv_dbtype = 'ORA'. " Domäne DBCON_DBMS
TRY.
* Prüfen, ob die DB-Connection schon offen ist
EXEC SQL.
SET CONNECTION :p_dbs
ENDEXEC.
IF sy-subrc <> 0.
* Connect to DB
EXEC SQL.
CONNECT TO :p_dbs
ENDEXEC.
ENDIF.
IF sy-subrc = 0.
* Session-Parameter setzen
EXEC SQL.
alter session set NLS_DATE_FORMAT = 'YYYYMMDD'
ENDEXEC.
EXEC SQL.
alter session set NLS_NUMERIC_CHARACTERS = ',.'
ENDEXEC.
* Open Cursor and SELECT
* SQL-Statements im ORACLE Syntax!
* Abbildung verschiedener Oracle-Datentypen als ABAP-Datentypen
* speziell das Datum (Orycle Typ DATE) muss Oracleseitig als Character-Feld (z.B. Länge 19 oder 15) interpretiert werden (CAST), damit es korrekt an SAP übertragen werden kann
* zusätzliche Komplexität durch INNER JOIN von zwei Tabellen mytab und mytab2
* Übergabe von Parametern an die EXEC SQL-Bereiche erfolgt immer durch ":Variablenname"
EXEC SQL.
OPEN dbcur FOR
SELECT mytab.col_number,
mytab.col_number10_2,
mytab2.col_number5,
mytab.col_char,
CAST (TO_CHAR (mytab.col_date, 'YYYY-MM-DD HH24:MI:SS') AS VARCHAR2 (19)) AS col_date_conv
CAST (TO_CHAR (mytab.col_date, 'YYYYMMDD HH24MISS') AS VARCHAR2 (15)) AS col_date_conv2
FROM myschemaname.mytablename mytab
INNER JOIN myschemaname.mytablename2 mytab2 ON mytab.key1 = mytab2.key1 AND mytab.key2 = mytab2.key2
WHERE mytab2.colxyz = :p_par1
ENDEXEC.
IF sy-subrc = 0.
DATA: lv_line TYPE ty_s_tableline.
DATA: it_table TYPE ty_it_table.
* Loop Cursor Data
DO.
* DB-Cursor auf nächsten Datensatz setzen, solange, wie Daten vorhanden sind
EXEC SQL.
FETCH NEXT dbcur INTO :lv_line
ENDEXEC.
IF sy-subrc = 0.
* Daten an itab anfügen
APPEND lv_line TO it_table.
ELSE.
* keine Daten mehr vorhanden (EOF)
EXIT.
ENDIF.
ENDDO.
* Close Cursor
EXEC SQL.
CLOSE dbcur
ENDEXEC.
ENDIF.
* Reset auf "default connection"
EXEC SQL.
SET CONNECTION DEFAULT
ENDEXEC.
* Disconnect
EXEC SQL.
DISCONNECT :p_dbs
ENDEXEC.
* Daten ausgeben
DATA: o_salv TYPE REF TO cl_salv_table.
cl_salv_table=>factory( IMPORTING
r_salv_table = o_salv
CHANGING
t_table = it_table ).
o_salv->get_functions( )->set_all( abap_true ).
o_salv->get_columns( )->set_optimize( abap_true ).
o_salv->get_display_settings( )->set_list_header( 'Oracle Data' ).
o_salv->get_display_settings( )->set_striped_pattern( abap_true ).
o_salv->get_selections( )->set_selection_mode( if_salv_c_selection_mode=>row_column ).
LOOP AT o_salv->get_columns( )->get( ) ASSIGNING FIELD-SYMBOL(<c>).
<c>-r_column->set_short_text( |{ <c>-r_column->get_columnname( ) }| ).
<c>-r_column->set_medium_text( |{ <c>-r_column->get_columnname( ) }| ).
<c>-r_column->set_long_text( |{ <c>-r_column->get_columnname( ) }| ).
ENDLOOP.
o_salv->display( ).
ELSE.
WRITE: / 'DB-Verbindung konnte nicht hergestellt werden.'.
ENDIF.
CATCH cx_root INTO DATA(e_txt).
WRITE: / e_txt->get_text( ).
ENDTRY.
ELSE.
WRITE: / |Falscher Datenbanktyp: { lv_dbtype }|.
ENDIF.
ELSE.
WRITE: / |DB-Alias { p_dbs } nicht im System vorhanden.|.
ENDIF.
Schlagwort: NUMBER
[ABAP] string-Formatierungen mit String-Templates
* ab V7.31
* http://zevolving.com/2013/07/abap-string-templates-new-feature-in-abap-731/
* Hinweis: String-Templates funktionieren nur Programmen bei denen die Unicodeprüfung aktiv ist!
* Z.B. kommt es in Modulpool-Programmen zu Fehlern beim kompilieren des Codes und der Compiler moniert die '|'-Symbole
DATA: text TYPE string VALUE 'Text'.
DATA: atext TYPE string VALUE '012345'.
DATA: num TYPE p DECIMALS 3 VALUE '-123.45'.
* Case
WRITE: / |RAW: { text CASE = RAW }|. " Text
WRITE: / |LOWER: { text CASE = LOWER }|. " text
WRITE: / |UPPER: { text CASE = UPPER }|. " TEXT
* Align
WRITE: / |LEFT: ->{ text WIDTH = 20 ALIGN = LEFT }<--|. " >Text <
WRITE: / |RIGHT: ->{ text WIDTH = 20 ALIGN = RIGHT }<--|. " > Text<
WRITE: / |CENTER: ->{ text WIDTH = 20 ALIGN = CENTER }<--|. " > Text <
* Padding, Auffüllen mit definiertem Zeichen
WRITE: / |PAD: { text ALIGN = LEFT WIDTH = 20 PAD = '_' }|. " Text________________
WRITE: / |PAD: { text ALIGN = RIGHT WIDTH = 20 PAD = '_' }|. " ________________Text
WRITE: / |PAD: { text ALIGN = CENTER WIDTH = 20 PAD = '_' }|. " ________Text________
* führende Nullen hinzufügen, ersetzt FUBA 'CONVERSION_EXIT_ALPHA_INPUT'
WRITE: / |ALPHA: { atext WIDTH = 20 ALPHA = IN }|. " 00000000000000012345
* führende Nullen entfernen, ersetzt FUBA 'CONVERSION_EXIT_ALPHA_OUTPUT'
WRITE: / |ALPHA: { atext ALPHA = OUT }|. " 12345
* Zahlen 1
WRITE: / |RAW: { num NUMBER = RAW }|. " -123.45
WRITE: / |USER: { num NUMBER = USER }|. " -123,45
WRITE: / |ENVIRONMENT: { num NUMBER = ENVIRONMENT }|. " -123,45
* Zahlen 2
WRITE: / |SCIENTIFIC: { num STYLE = SCIENTIFIC }|. " -1.2345E+02
WRITE: / |SCIENTIFIC_WITH_LEADING_ZERO: { num STYLE = SCIENTIFIC_WITH_LEADING_ZERO }|. " -0.12345E+03
WRITE: / |SCALE_PRESERVING_SCIENTIFIC: { num STYLE = SCALE_PRESERVING_SCIENTIFIC }|. " -1.2345E+0002
WRITE: / |ENGINEERING: { num STYLE = ENGINEERING }|. " -123.45E+00
* Datumsformat
WRITE: / |RAW: { sy-datum DATE = RAW }|. " 20160705
WRITE: / |ISO: { sy-datum DATE = ISO }|. " 2016-07-05
WRITE: / |USER: { sy-datum DATE = USER }|. " 05.07.2016
WRITE: / |ENVIRONMENT: { sy-datum DATE = ENVIRONMENT }|. " 05.07.2016
* Uhrzeitformat
WRITE: / |RAW: { sy-uzeit TIME = RAW }|. " 102336
WRITE: / |ISO: { sy-uzeit TIME = ISO }|. " 10:23:36
WRITE: / |USER: { sy-uzeit TIME = USER }|. " 10:23:36
WRITE: / |ENVIRONMENT: { sy-uzeit TIME = ENVIRONMENT }|. " 10:23:36
* Timestampformat
DATA: lv_tsl TYPE timestampl.
* Zeitstempel lang
GET TIME STAMP FIELD lv_tsl.
WRITE: / |SPACE: { lv_tsl TIMESTAMP = SPACE }|. " 2016-07-05 08:23:36.0908600
WRITE: / |ISO: { lv_tsl TIMESTAMP = ISO }|. " 2016-07-05T08:23:36,0908600
WRITE: / |USER: { lv_tsl TIMESTAMP = USER }|. " 05.07.2016 08:23:36,0908600
WRITE: / |ENVIRONMENT: { lv_tsl TIMESTAMP = ENVIRONMENT }|. " 05.07.2016 08:23:36,0908600
* ABAP Serialization XML (axXML)
WRITE: / |XML: { lv_tsl XSD = YES }|. " 20160705082336.09086