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:

Quote:

---

--------------------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.

That is a SQL*Plus context of EXEC.

The Pro*C form is documented here.

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]

up :confused:

I have also looking for utl_file
so...

Code:

---

sqlplus /nolog
SQL> connect / as sysdba

---

Quote:

---

Connecté.

---

Code:

---

SQL> CREATE DIRECTORY ERROR_LOG AS '/home/app/oracle/admin/TST';

---

Quote:

---

Répertoire créé.

---

Code:

---

SQL> GRANT READ, WRITE ON DIRECTORY ERROR_LOG TO PUBLIC;

---

Quote:

---

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:

Quote:

---

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:

Quote:

---

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' ?