Home » Developer & Programmer » Precompilers, OCI & OCCI » sys.dbms_system.ksdwrt syntax
sys.dbms_system.ksdwrt syntax [message #382510] Thu, 22 January 2009 15:29 Go to next message
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 #382522 is a reply to message #382510] Thu, 22 January 2009 15:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Well,
I have never used Pro*C or C.
This is just a wild guess.

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

Works fine as-is inside an sql*plus shell.
It seems that EXEC need to be used in Pro*C also.
But this sys.dbms_system is actually a package/procedure and you might
want to call it between BEGIN and END.
Here is a simple example.
http://download.oracle.com/docs/cd/B10500_01/appdev.920/a97269/pc_07pls.htm#1490
Re: sys.dbms_system.ksdwrt syntax [message #382527 is a reply to message #382510] Thu, 22 January 2009 17:10 Go to previous messageGo to next message
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 #382553 is a reply to message #382527] Thu, 22 January 2009 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
#include <sqlca.h>
or
EXEC SQL INCLUDE sqlca;
is missing in your code.

Regards
Michel
Re: sys.dbms_system.ksdwrt syntax [message #382839 is a reply to message #382553] Sun, 25 January 2009 15:06 Go to previous messageGo to next message
cassy
Messages: 8
Registered: January 2009
Junior Member
thanks I try it but I still have the same problem.
Re: sys.dbms_system.ksdwrt syntax [message #382864 is a reply to message #382839] Sun, 25 January 2009 23:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you correctly declare sqlca section you can't have this error:
Quote:
PLS-S-00201, identificateur 'SQLCA.SQLCODE' doit être déclaré

Check your code.

Regards
Michel
Re: sys.dbms_system.ksdwrt syntax [message #382965 is a reply to message #382864] Mon, 26 January 2009 15:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #383302 is a reply to message #383212] Wed, 28 January 2009 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The aim is to log sqlca.sqlcode and sqlca.sqlerrm.sqlerrmc...so how can I do that?

Use bind variables.

[QUOTE]sys.dbms_system.ksdwrt(3, to_char(sysdate,
.......1
PLS-S-00201, identificateur 'SYS.DBMS_SYSTEM' doit tre dclar/QUOTE]
You need to have the privilege to execute this procedure.

It is not a good idea to log application error in alert.log
It is a very bad idea to grant privilege on dbms_system to an application user.

Regards
Michel
Re: sys.dbms_system.ksdwrt syntax [message #383400 is a reply to message #383302] Wed, 28 January 2009 15:01 Go to previous messageGo to next message
cassy
Messages: 8
Registered: January 2009
Junior Member
Use bind variables.

Confused
I have never coded in pro*C...and google doesn't translate this expression "bind variables" so I don't know what bind means...
What would be the code to use bind variables?

Quote:
It is not a good idea to log application error in alert.log
It is a very bad idea to grant privilege on dbms_system to an application user.


Do you have an alternative solution?
Re: sys.dbms_system.ksdwrt syntax [message #383448 is a reply to message #383400] Wed, 28 January 2009 23:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Pro*C/C++ Programmer's Guide
Part I Introduction and Concepts
Chapter 1 Introduction
Section Can I Use Bind Variables Anywhere in a SQL Statement?
And follow the links.

Quote:
Do you have an alternative solution?

Use utl_file and write it in your own file, for instance.
Or record it in a log table, it is then easily queryable with SQL by hour, application, user, client machine, IP and all other kind ot things you can put in the table.

Regards
Michel
Re: sys.dbms_system.ksdwrt syntax [message #384028 is a reply to message #383448] Sun, 01 February 2009 22:28 Go to previous messageGo to next message
cassy
Messages: 8
Registered: January 2009
Junior Member
So...
sqlplus /nolog
SQL> connect / as sysdba

Quote:
Connecté.

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

Quote:
Répertoire créé.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: How to report an Oracle bug?
Next Topic: PCC-S-02201 Compile error
Goto Forum:
  


Current Time: Mon Dec 05 11:18:15 CST 2016

Total time taken to generate the page: 0.11028 seconds