Oracle – Dicionário de Dados

Script para gerar um simples dicionário de dados

SELECT A.TABLE_NAME, C.COMMENTS ,A.COLUMN_NAME, B.DATA_TYPE, B.DATA_LENGTH, B.DATA_PRECISION, A.COMMENTS FROM USER_COL_COMMENTS A,      USER_TAB_COLS B,      USER_TAB_COMMENTS C WHERE A.COLUMN_NAME||A.TABLE_NAME = B.COLUMN_NAME||B.TABLE_NAME   AND C.TABLE_NAME = B.TABLE_NAME

SAP – Configurar DBLink x DBCON

Primeiro deve ser configurado no TNS do servidor do SAP a conexão com o oracle.
Apos feito esse procedimento é só adicionar esse nome ao SAP na DBCON.
Abaixo estou enviando as formas utilizando o exec sql.
Existe uma outra forma de efetuar a execução do exec sql que é utilizando classes, essa já e um pouco mais complicada mas é mais interessante pra fazer algo dinamico.
Segue o exemplo que é utiliado na minha empresa.

CON NAME: ORACLE                    
DBMS                          ORA                       
USER NAME                sapdes                    
PASSWORD                V01/0014ZctvSB67Wv3u6Yybiy
CON ENV                    DESENV-RAC                
DB RECO                                      
MAX CONNECTIONS    0                         
OPT CONNECTIONS    0

Abaixo segue alguns comandos do exec sql

Variáveis:
DATA: dbtype     TYPE dbcon_dbms,
      dbcur      TYPE cursor,
      exc_ref    TYPE REF TO cx_sy_native_sql_error,
      error_text TYPE string, 
      dbs        TYPE dbcon-con_name VALUE 'ORACLE'. 


Efetuando a conexão com o banco

  " Procurar uma conexão oracle.
  SELECT SINGLE dbms
    FROM dbcon
    INTO dbtype
    WHERE con_name = dbs.

  " Se for uma conexão oracle para DBLINK.
  IF dbtype = 'ORA'.
    TRY.
        " Executar a chamada no dblink "DBGERAL"
        EXEC SQL.
          CONNECT TO :dbs
        ENDEXEC.
      CATCH cx_sy_native_sql_error INTO exc_ref.
        error_text = exc_ref->get_text( ).
        MESSAGE error_text TYPE 'E'.
    ENDTRY.
  ELSE.
    MESSAGE 'Não foi encontrado uma conexão oracle'  TYPE 'E'.
  ENDIF. 


Exemplo de Select

  CLEAR:  wa_fat_status.
  REFRESH it_fat_status.

  TRY.
      " Verificar termino do faturamento
      EXEC SQL.
        OPEN dbcur FOR
          SELECT setor_atividade,
                 tp_nota,
                 to_char(dt_faturamento, 'yyyymmdd'),
                 to_char(dt_boleto, 'yyyymmdd'),
                 status,
                 to_char(dt_inclusao, 'yyyymmdd')
            FROM ZSAP_FAT_STATUS
      ENDEXEC.

      " Comandos para gravar todos os dados em uma internal table
      DO.
        CLEAR: wa_dados.
        EXEC SQL.
          FETCH NEXT dbcur
          INTO :wa_fat_status-setor_atividade,
               :wa_fat_status-tp_nota,
               :wa_fat_status-dt_faturamento,
               :wa_fat_status-dt_boleto,
               :wa_fat_status-status,
               :wa_fat_status-dt_inclusao
        ENDEXEC.
        IF sy-subrc <> 0.
          EXIT.
        ELSE.
          APPEND wa_fat_status TO it_fat_status.
        ENDIF.
      ENDDO.

      " Encerra o cursor
      EXEC SQL.
        CLOSE dbcur
      ENDEXEC.

    CATCH cx_sy_native_sql_error INTO exc_ref.
      error_text = exc_ref->get_text( ).
      MESSAGE error_text TYPE 'S'.

      PERFORM gravarErro USING error_text.
  ENDTRY. 

Exemplo de um update

  TRY.
      IF wa_dados-dt_processamento IS INITIAL.
        EXEC SQL.
          UPDATE ZSAP_FATURAMENTO
            SET DT_PROCESSAMENTO = sysdate,
                USR_PROCESSAMENTO = :sy-uname,
                STATUS = 'P'
            WHERE cd_zsap = :wa_dados-cd_zsap
        ENDEXEC.
        IF sy-subrc IS INITIAL.
          EXEC SQL.
            COMMIT
          ENDEXEC.
        ELSE.
          MESSAGE 'Erro na atualização das datas' TYPE 'E'.
        ENDIF.
      ELSE.
        EXEC SQL.
          UPDATE ZSAP_FATURAMENTO
            SET DT_REPROCESSO = sysdate,
                USR_REPROCESSO = :sy-uname,
                STATUS = 'P'
            WHERE cd_zsap = :wa_dados-cd_zsap
        ENDEXEC.
        IF sy-subrc IS INITIAL.
          EXEC SQL.
            COMMIT
          ENDEXEC.
        ELSE.
          MESSAGE 'Erro na atualização das datas' TYPE 'E'.
        ENDIF.
      ENDIF.

    CATCH cx_sy_native_sql_error INTO exc_ref.
      error_text = exc_ref->get_text( ).
      MESSAGE error_text TYPE 'E'.
  ENDTRY. 

Fonte: http://www.grupos.com.br/group/abap4/Messages.html?action=message&id=126814010656086&year=10&month=3&prev=1

SAP – Valores de SY-SUBRC

Values of SY-SUBRC on different ABAP statements

'sy-subrc' is a return code, set by the following ABAP statements.
As a rule, if SY-SUBRC = 0, the statement was executed successfully.

1. ASSIGN sets SY-SUBRC to 0 if the field symbol assignment was possible, otherwise to 4.
2. AUTHORITY-CHECK sets SY-SUBRC to 0 if the user has the required authorization, otherwise to 4, 8, 12, 16, 24, 28, 32, or 36 depending on the cause of the authorization failure.
3. CALL DIALOG with USING sets SY-SUBRC to 0 if the processing is successful, otherwise to a value other than 0.
4. CALL FUNCTION sets SY-SUBRC in accordance with the defined exception handling.
5. CALL METHOD sets SY-SUBRC in accordance with the defined exception handling.
6. CALL SELECTION-SCREEN sets SY-SUBRC to 0 if the user chooses Enter or Execute, and 4 if the user chooses Cancel.
7. CALL TRANSACTION with USING sets SY-SUBRC to 0 if the processing is successful, otherwise to a value other than 0.
8. CATCH SYSTEM-EXCEPTIONS sets SY-SUBRC after the ENDCATCH statement if a system exception occurs. The value is set in the program.
9. COMMIT WORK sets SY-SUBRC to 0. COMMIT WORK AND WAIT sets SY-SUBRC to 0 if the update is successful, otherwise to a value other than 0.
10. COMMUNICATION INIT DESTINATION ... RETURNCODE sets SY-SUBRC as specified.
11. CONCATENATE sets SY-SUBRC to 0 if the result fits into the target variable, otherwise to 4.
12. CREATE OBJECT sets SY-SUBRC if the exceptions of the instance constructor are handled in the program.
13. CREATE OBJECT in OLE2 sets SY-SUBRC to 0 if an external object could be created, otherwise to 1, 2, or 3, depending on the cause.
14. DELETE sets SY-SUBRC to 0 if the operation is successful, otherwise to 4 or another value other than 0, depending on the cause.
15. DEMAND ... MESSAGES INTO sets SY-SUBRC to 0 if the message table is empty, otherwise to a value other than 0.
16. DESCRIBE LIST sets SY-SUBRC to 0 if the line or list exists, otherwise to 4 or 8.
17. EXEC SQL - ENDEXEC sets SY-SUBRC to 0 in nearly all cases. It does, however, set SYSUBRC to 4 if no entry is read in a FETCH statement.
18. FETCH sets SY-SUBRC to 0 if at least one line was read, otherwise to 4.
19. GENERATE SUBROUTINE POOL sets SY-SUBRC to 0 if the generation was successful, otherwise to 8.
20. GET CURSOR sets SY-SUBRC to 0 if the cursor is correctly positioned, otherwise to 4.
21. GET PARAMETER sets SY-SUBRC to 0 if a corresponding value exists in SAP memory, otherwise to 4.
22. IMPORT sets SY-SUBRC to 0 if the import is successful, otherwise to 4.
23. INSERT sets SY-SUBRC to 0 if the operation is successful, otherwise to 4.
24. LOAD REPORT sets SY-SUBRC to 0 if the operation is successful, otherwise to 4 or 8 depending on the cause of the error.
25. LOOP sets SY-SUBRC to 0 if there is at least one pass through the extract. Otherwise, it is set to a value other than 0.
26. LOOP AT sets SY-SUBRC to 0 if there is at least one loop pass through the internal table, otherwise to 4.
27. MODIFY sets SY-SUBRC to 0 if the operation is successful, otherwise to 4.
28. MODIFY LINE sets SY-SUBRC to 0 if a line in the list was changed, otherwise it sets it to a value other than 0.
29. MODIFY sets SY-SUBRC to 0 if the operation is successful, otherwise to 4.
30. OLE2 Automation, executed successfully, otherwise 1, 2, 3, or 4, depending on the cause of the error.
31. OPEN DATASET sets SY-SUBRC to 0 if the file could be opened, otherwise to 8.
32. Open SQL statements set SY-SUBRC to 0 if the operation is successful, otherwise to a value other than 0.
33. OVERLAY sets SY-SUBRC to 0 if at least one character is overlaid, otherwise to 4.
34. READ DATASET sets SY-SUBRC to 0 if the read operation was successful, otherwise to 4 or 8, depending on the cause of the error.
35. READ LINE sets SY-SUBRC to 0 if a list line exists, otherwise to a value other than 0.
36. READ TABLE sets SY-SUBRC to 0 if table lines are found, otherwise to 2, 4, or 8, depending on the context and cause of the error.
37. REPLACE sets SY-SUBRC to 0 if the search string was replaced, otherwise to a value other than 0.
38. SCROLL sets SY-SUBRC to 0 if the scrolling within the list was successful, otherwise to 4 or 8, depending on the cause.
39. SEARCH sets SY-SUBRC to 0 if the search string was found, otherwise to 4.
40. SELECT sets SY-SUBRC to 0 if at least one line was read, otherwise to 4, or possibly 8 in SELECT SINGLE FOR UPDATE.
41. SET COUNTRY sets SY-SUBRC if the country code exists in table T005X, otherwise to 4.
42. SET BIT sets SY-SUBRC to 0 if the bit could be set, otherwise to a value other than 0.
43. SET TITLEBAR sets SY-SUBRC to 0 if the title exists, otherwise to 4.
44. SHIFT ... UP TO sets SY-SUBRC to 0 if the position could be found within the string, otherwise to 4.
45. SPLIT sets SY-SUBRC to 0 if the sizes of the target fields are adequate, otherwise to 4.
46. UPDATE sets SY-SUBRC to 0 if the operation is successful, otherwise to 4.
47. WRITE ... TO sets SY-SUBRC to 0 if the assignment is successful, otherwise to 4.