DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sys.dbms_system.ksdwrt syntax

  1. #1
    Join Date
    Jan 2009
    Posts
    4

    sys.dbms_system.ksdwrt syntax

    Hi,
    I have a .pc file where I would like to log oracle error in alert_log and trace log.
    So I use this syntax:

    Code:
    EXEC sys.dbms_system.ksdwrt(3, to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') || ': Erreur SQL n' || sqlca.sqlcode + ': ' || sqlca.sqlerrm.sqlerrmc);

    I have no error when I precompile this script .pc and a .c file is generated.
    But when I compile the .c file, I have these errors:


    --------------------Configuration: host8 - Win32 Debug--------------------
    Compiling...
    HOST_oracle.c
    C:\Dveloppement\HOST_oracle.c(248) : error C2065: 'EXEC' : undeclared identifier
    C:\Dveloppement\HOST_oracle.c(248) : error C2146: syntax error : missing ';' before identifier 'sys'
    C:\Dveloppement\HOST_oracle.c(248) : error C2065: 'sys' : undeclared identifier
    C:\Dveloppement\HOST_oracle.c(248) : error C2224: left of '.dbms_system' must have struct/union type
    C:\Dveloppement\HOST_oracle.c(248) : warning C4013: 'to_char' undefined; assuming extern returning int
    C:\Dveloppement\HOST_oracle.c(248) : error C2065: 'sysdate' : undeclared identifier
    C:\Dveloppement\HOST_oracle.c(248) : error C2015: too many characters in constant
    C:\Dveloppement\HOST_oracle.c(248) : error C2015: too many characters in constant

    What is the syntax in Pro*c language to log oracle errors?
    Thanks for your help.
    Cassy.

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    That is a SQL*Plus context of EXEC.

    The Pro*C form is documented here.

  3. #3
    Join Date
    Jan 2009
    Posts
    4
    Thanks for your answer...
    So this is the code in .pc file
    Code:
    	EXEC SQL EXECUTE
    		BEGIN 
    			sys.dbms_system.ksdwrt(3, to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') || ': Erreur SQL n' || sqlca.sqlcode + ': ' || sqlca.sqlerrm.sqlerrmc);
    		END;
    	END-EXEC;
    When I precompile I have this error:
    [quote title=Quote:]Erreur Ã* la ligne 99, colonne 108 dans le fichier c:\développement\host_oracle.pc
    sys.dbms_system.ksdwrt(3, to_char(sysdate, 'dd/mm/yyyy h
    h24:mi:ss') || ': Erreur SQL n' || sqlca.sqlcode + ': ' || sqlca.sqlerrm
    .sqlerrmc);
    ................................................................................
    ...........................1
    PLS-S-00201, identificateur 'SQLCA.SQLCODE' doit être déclaré
    Erreur Ã* la ligne 99, colonne 8 dans le fichier c:\développement\host_or
    acle.pc
    sys.dbms_system.ksdwrt(3, to_char(sysdate, 'dd/mm/yyyy h
    h24:mi:ss') || ': Erreur SQL n' || sqlca.sqlcode + ': ' || sqlca.sqlerrm
    .sqlerrmc);
    .......1
    PLS-S-00000, Statement ignored
    erreur sémantique en ligne 98, colonne 3, fichier c:\développement\host_oracle.pc:
    BEGIN
    ..1
    PCC-S-02346, PL/SQL a trouvé des erreurs sémantiques[/quote]

    I try:
    Code:
    		BEGIN 
    			sys.dbms_system.ksdwrt(3, to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') || ': Erreur SQL n' || sqlca.sqlcode + ': ' || sqlca.sqlerrm.sqlerrmc);
    		END;
    I can precompile the .pc file but when I compile .c file I have this error:
    [quote title=Quote:]
    --------------------Configuration: host8 - Win32 Debug--------------------
    Compiling...
    HOST_oracle.c
    C:\Dveloppement\HOST_oracle.c(249) : error C2065: 'BEGIN' : undeclared identifier
    C:\Dveloppement\HOST_oracle.c(249) : error C2146: syntax error : missing ';' before identifier 'sys'
    C:\Dveloppement\HOST_oracle.c(249) : error C2065: 'sys' : undeclared identifier
    C:\Dveloppement\HOST_oracle.c(249) : error C2224: left of '.dbms_system' must have struct/union type
    C:\Dveloppement\HOST_oracle.c(249) : warning C4013: 'to_char' undefined; assuming extern returning int
    C:\Dveloppement\HOST_oracle.c(249) : error C2065: 'sysdate' : undeclared identifier
    C:\Dveloppement\HOST_oracle.c(249) : error C2015: too many characters in constant
    C:\Dveloppement\HOST_oracle.c(249) : error C2015: too many characters in constant
    C:\Dveloppement\HOST_oracle.c(250) : error C2065: 'END' : undeclared identifier
    Error executing cl.exe.

    HOST_oracle.obj - 8 error(s), 1 warning(s)[/quote]

  4. #4
    Join Date
    Jan 2009
    Posts
    4
    up

  5. #5
    Join Date
    Jan 2009
    Posts
    4
    I have also looking for utl_file
    so...
    Code:
    sqlplus /nolog
    SQL> connect / as sysdba
    Connecté.
    Code:
    SQL> CREATE DIRECTORY ERROR_LOG AS '/home/app/oracle/admin/TST';
    Répertoire créé.
    Code:
    SQL> GRANT READ, WRITE ON DIRECTORY ERROR_LOG TO PUBLIC;
    Autorisation de privilèges (GRANT) acceptée.
    This is my procedure:
    Code:
    CREATE OR REPLACE PROCEDURE ORACLE.utl_file_write (
      path       in varchar2,
      filename   in varchar2,
      text  in varchar2)
    is
        output_file  utl_file.file_type;
    begin
        output_file := utl_file.fopen (path,filename, 'W');
    
        utl_file.put_line (output_file, text);
        utl_file.fclose(output_file);
    	
    end utl_file_write;
    This is the Pro*c code:
    Code:
    #include "HOST_oracle.h"
    #include "sqlca.h"
    
    EXEC SQL BEGIN DECLARE SECTION;
      char SQLSTATE[6];
    EXEC SQL END DECLARE SECTION;
    
    //############################ SQLERR_blank
    // 
    // ----
    // Paramtre entre  : le contexte d'xcution.
    // ------
    // Valeur de retour  : Le code de l'erreur.
    // 
    long sqlerr_blank(void *contxt)
    {
    EXEC SQL BEGIN DECLARE SECTION;
      sql_context contx;
    EXEC SQL END DECLARE SECTION;
       
      contx = contxt;
    
      EXEC SQL CONTEXT USE :contx;
      EXEC SQL WHENEVER SQLERROR CONTINUE;
      // on log les erreurs oracle si le code erreur est ngatif
      //if (sqlca.sqlcode <0)
      //{
        //on insre un message dans alert log
        EXEC SQL EXECUTE
          BEGIN 
    	char text[] = to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') || ': Erreur SQL n' || sqlca.sqlcode || ': ' || sqlca.sqlerrm.sqlerrmc;
    	utl_file_write ('/home/app/oracle/admin/TST','sql_error.log',:text);
          END;
        END-EXEC;
      //} 
      return sqlca.sqlcode;
    }
    But When I precompile I have this error:
    erreur smantique en ligne 32, colonne 66, fichier c:\dveloppement\host_oracle.pc:
    utl_file_write ('/home/app/oracle/admin/TST','sql
    _error.log',:text);
    .................................................................1
    PCC-S-02322, identificateur inconnu rencontr
    char text[] = to_char(sysdate, 'dd/mm/yyyy hh24:
    mi:ss') || ': Erreur SQL n' || sqlca.sqlcode || ': ' || sqlca.sqlerrm.sqlerrmc;
    .............1
    PLS-S-00103, Symbole "TEXT" rencontr la place d'un des symboles suivants :

    := . ( @ % ;

    erreur smantique en ligne 30, colonne 4, fichier c:\dveloppement\host_oracle.pc:
    BEGIN
    ...1
    PCC-S-02347, PL/SQL a trouv des erreurs syntaxiques
    So I change the code like this:
    Code:
    #include "HOST_oracle.h"
    #include "sqlca.h"
    
    EXEC SQL BEGIN DECLARE SECTION;
      char SQLSTATE[6];
    EXEC SQL END DECLARE SECTION;
    
    //############################ SQLERR_blank
    // 
    // ----
    // Paramtre entre  : le contexte d'xcution.
    // ------
    // Valeur de retour  : Le code de l'erreur.
    // 
    long sqlerr_blank(void *contxt)
    {
    EXEC SQL BEGIN DECLARE SECTION;
      sql_context contx;
    EXEC SQL END DECLARE SECTION;
       
      contx = contxt;
    
      EXEC SQL CONTEXT USE :contx;
      EXEC SQL WHENEVER SQLERROR CONTINUE;
      // on log les erreurs oracle si le code erreur est ngatif
      //if (sqlca.sqlcode <0)
      //{
        //on insre un message dans alert log
        EXEC SQL EXECUTE
          BEGIN 
            utl_file_write ('/home/app/oracle/admin/TST','sql_error.log',to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') || ': Erreur SQL n' || sqlca.sqlcode || ': ' || sqlca.sqlerrm.sqlerrmc);
          END;
        END-EXEC;
      //} 
      return sqlca.sqlcode;
    }
    And when I precompile I have this error:
    Erreur la ligne 31, colonne 135 dans le fichier c:\dveloppement\host_oracle.pc
    utl_file_write ('/home/app/oracle/admin/TST','sql
    _error.log',to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') || ': Erreur SQL n' || sql
    ca.sqlcode || ': ' || sqlca.sqlerrm.sqlerrmc);
    ................................................................................
    ......................................................1
    PLS-S-00201, identificateur 'SQLCA.SQLCODE' doit tre dclar
    Erreur la ligne 31, colonne 5 dans le fichier c:\dveloppement\host_oracle.pc
    utl_file_write ('/home/app/oracle/admin/TST','sql
    _error.log',to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') || ': Erreur SQL n' || sql
    ca.sqlcode || ': ' || sqlca.sqlerrm.sqlerrmc);
    ....1
    PLS-S-00000, Statement ignored
    erreur smantique en ligne 30, colonne 4, fichier c:\dveloppement\host_oracle.pc:
    BEGIN
    ...1
    PCC-S-02346, PL/SQL a trouv des erreurs smantiques
    Also how I can use ERROR_LOG instead of '/home/app/oracle/admin/TST' ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width