Home » Developer & Programmer » Precompilers, OCI & OCCI » sys.dbms_system.ksdwrt syntax
sys.dbms_system.ksdwrt syntax [message #382510] |
Thu, 22 January 2009 15:29 |
cassy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
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:
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.
|
|
|
|
Re: sys.dbms_system.ksdwrt syntax [message #382527 is a reply to message #382510] |
Thu, 22 January 2009 17:10 |
cassy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
Thanks for your answer...
So this is the code in .pc file
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: | 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
|
I try:
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: |
--------------------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)
|
[Updated on: Thu, 22 January 2009 17:19] Report message to a moderator
|
|
|
|
|
|
Re: sys.dbms_system.ksdwrt syntax [message #382965 is a reply to message #382864] |
Mon, 26 January 2009 15:36 |
cassy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
I try:
#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
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;
//}
return sqlca.sqlcode;
}
AND
#include "HOST_oracle.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
EXEC SQL INCLUDE sqlca;
//if (sqlca.sqlcode <0)
//{
//on insre un message dans alert log
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;
//}
return sqlca.sqlcode;
}
When I precompile:
Quote: | Erreur la ligne 100, colonne 101 dans le fichier c:\dveloppement\host_oracle.pc
sys.dbms_system.ksdwrt(3, to_char(sysdate, 'dd/m
m/yyyy hh24:mi:ss') || ': Erreur SQL n' || sqlca.sqlcode + ': ' || sqlca.sqlerrm
.sqlerrmc);
................................................................................
....................1
PLS-S-00201, identificateur 'SQLCA.SQLCODE' doit tre dclar
Erreur la ligne 100, colonne 9 dans le fichier c:\dveloppement\host_oracle.pc
sys.dbms_system.ksdwrt(3, to_char(sysdate, 'dd/m
m/yyyy hh24:mi:ss') || ': Erreur SQL n' || sqlca.sqlcode + ': ' || sqlca.sqlerrm
.sqlerrmc);
........1
PLS-S-00000, Statement ignored
erreur smantique en ligne 99, colonne 4, fichier c:\dveloppemen\host_oracle.pc:
BEGIN
...1
PCC-S-02346, PL/SQL a trouv des erreurs smantiques
|
|
|
|
Re: sys.dbms_system.ksdwrt syntax [message #382992 is a reply to message #382965] |
Mon, 26 January 2009 23:06 |
|
Michel Cadot
Messages: 68728 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
There is no line 100 in what you posted.
ALWAYS post the code corresponding to the error.
ALWAYS try to reduce the code to the minimum that produces the error and post it and not the whole stuff.
In other words, you shoud post the compilation for the code you posted.
And keep the lines in 80 characters it greatly help to see the error in each line. You don't need an indentation of 8 characters, 2 or 3 are sufficient.
The following is not PL/SQL: "|| sqlca.sqlcode + ': ' ||"
1/ + is not the concatenation character
2/ sqlca.sqlcode is not a PL/SQL variable but your C program variable
Regards
Michel
[Updated on: Mon, 26 January 2009 23:08] Report message to a moderator
|
|
|
Re: sys.dbms_system.ksdwrt syntax [message #383212 is a reply to message #382992] |
Tue, 27 January 2009 15:10 |
cassy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
Quote: | 1/ + is not the concatenation character
|
oups
Quote: | 2/ sqlca.sqlcode is not a PL/SQL variable but your C program variable
|
The aim is to log sqlca.sqlcode and sqlca.sqlerrm.sqlerrmc...so how can I do that?
But I remove sqlca.sqlcode just to see...:
#include <sqlca.h>
#include "HOST_oracle.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
sys.dbms_system.ksdwrt(3, to_char(sysdate,
'dd/mm/yyyy hh24:mi:ss'));
END;
END-EXEC;
//}
return sqlca.sqlcode;
}
Quote: | Erreur la ligne 32, colonne 8 dans le fichier c:\dveloppement\host_oracle.pc
sys.dbms_system.ksdwrt(3, to_char(sysdate,
.......1
PLS-S-00201, identificateur 'SYS.DBMS_SYSTEM' doit tre dclar
Erreur la ligne 32, colonne 8 dans le fichier c:\dveloppement\host_oracle.pc
sys.dbms_system.ksdwrt(3, to_char(sysdate,
.......1
PLS-S-00000, Statement ignored
erreur smantique en ligne 31, colonne 9, fichier c:\dveloppement\host_oracle.pc:
BEGIN
........1
PCC-S-02346, PL/SQL a trouv des erreurs smantiques
|
Thanks for your patience!
|
|
|
|
|
|
Re: sys.dbms_system.ksdwrt syntax [message #384028 is a reply to message #383448] |
Sun, 01 February 2009 22:28 |
cassy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
So...
sqlplus /nolog
SQL> connect / as sysdba
SQL> CREATE DIRECTORY ERROR_LOG AS '/home/app/oracle/admin/TST';
SQL> GRANT READ, WRITE ON DIRECTORY ERROR_LOG TO PUBLIC;
Quote: | Autorisation de privilèges (GRANT) acceptée.
|
This is my procedure:
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:
#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:
#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' ?
[Updated on: Sun, 01 February 2009 22:54] Report message to a moderator
|
|
|
Re: sys.dbms_system.ksdwrt syntax [message #384056 is a reply to message #384028] |
Mon, 02 February 2009 00:55 |
|
Michel Cadot
Messages: 68728 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | connect / as sysdba
|
- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.
NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.
Quote: | GRANT READ, WRITE ON DIRECTORY ERROR_LOG TO PUBLIC;
|
Do you REALLY want anyone be able to overwrite the files you create?
Give it only to the procedure owner: ORACLE. By the way, ORACLE is not a safe account name.
For the other error, read again the previous posts.
I recommend you to either follow a course on the subject, carefully read the documentation or hire someone that has the knowledge for this task. I will not debug all your code line by line and learn you Pro*C in forum topic.
Regards
Michel
|
|
|
Re: sys.dbms_system.ksdwrt syntax [message #384207 is a reply to message #384056] |
Mon, 02 February 2009 14:50 |
cassy
Messages: 8 Registered: January 2009
|
Junior Member |
|
|
Quote: | Do you REALLY want anyone be able to overwrite the files you create?
|
I see this syntax in a utl_file tutorial.
Quote: | I will not debug all your code line by line and learn you Pro*C in forum topic.
|
There is juste one line, and if I write to this forum it's to have some help.
12 days to finally have this answer: I don't want to help you.
I read your link and the syntax of blind variables is :variable and there is nothing else...
So if someone else can help me...
[Updated on: Mon, 02 February 2009 14:53] Report message to a moderator
|
|
|
Re: sys.dbms_system.ksdwrt syntax [message #384209 is a reply to message #384207] |
Mon, 02 February 2009 14:58 |
|
Michel Cadot
Messages: 68728 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | I see this syntax in a utl_file tutorial.
|
You have to understand a statement before using it.
Quote: | There is juste one line, and if I write to this forum it's to have some help.
12 days to finally have this answer: I don't want to help you.
|
You are ungrateful.
I helped (or tried to help) you during but you obviously lack knowledge. You are back to the previous error, the previous answer applies to it. You are currently not able to understand it because you don't study Pro*C before trying to use it. You have to work before understanding what is posted in forum. You obviously did not read or not understand the link I posted and what you are doing.
The point is not I don't want to help you, it is you are unable to understand my help.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Fri Dec 27 03:51:34 CST 2024
|