-
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.
-
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
-
I have also looking for utl_file
so...
Code:
sqlplus /nolog
SQL> connect / as sysdba
Code:
SQL> CREATE DIRECTORY ERROR_LOG AS '/home/app/oracle/admin/TST';
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|