2 Replies Latest reply on Jun 28, 2013 10:54 AM by Harm Joris ten Napel-Oracle

    write to syslog from pl/sql

    An oracle DBA
      Hello Gurus !

      I would like to write to syslog from pl/sql without 3rd party tools.
      Do you have an idea how to achieve this ?

      Thank you for your answer !

      Ros
        • 1. Re: write to syslog from pl/sql
          Billy~Verreynne
          External procedure interface can be used. I have not implemented the openlog() and closelog() calls (got a weird problem with that). The syslog() call works fine on its own though.

          o/s: Kubuntu 11.10
          Oracle XE 10.2.0.1
          // call:
          //    void syslog(int priority, const char *format, ...);
          // library:
          //    libc
          
          SQL> create or replace library libc as
            2          '/lib/i386-linux-gnu/libc.so.6'
            3  /
          
          Library created.
          
          SQL> 
          SQL> create or replace procedure WriteSysLog( priority binary_integer, message varchar2 ) is
            2          external
            3          library libc
            4          name "syslog"
            5          language C
            6          calling standard C
            7          parameters(
            8                  priority int,
            9                  message string
           10          );
           11  /
          
          Procedure created.
          
          SQL>
          SQL> declare
            2          LOG_INFO        constant binary_integer := 6;
            3  begin
            4          WriteSysLog( LOG_INFO, 'All your syslogs messages are belong to us.' );
            5  end;
            6  /
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          And in +/var/log/syslog+ :
          ..
          Feb  2 00:00:00 hostname extprocPLSExtProc: All your syslogs messages are belong to us.
          ..
          Note you need a properly configured external procedure (EXTPROC) service in the local Listener, and a valid TNS alias for it in the local tnsnames.ora file. (local, as in local on the Oracle server where the PL/SQL code is executed)
          • 2. Re: write to syslog from pl/sql
            Harm Joris ten Napel-Oracle

            Hi BillyVerreynne ,

             

            I tested it out and it works, on 64-bit OEL5 I created the library as follows:

             

            create or replace library libc as '/lib64/libc.so.6';

            /

             

            Also set SET EXTPROC_DLLS=ANY in $ORACLE_HOME/hs/admin/extproc.ora

             

            [root@nlsupport03 admin]# tail -1 /var/log/messages

            Jun 28 10:39:57 nlsupport03 extprocv1120: All your syslogs messages are belong to us.

             

            Greetings,

             

            Harm ten Napel