Forum Stats

  • 3,751,235 Users
  • 2,250,335 Discussions
  • 7,867,351 Comments

Discussions

write to syslog from pl/sql

An oracle DBA
An oracle DBA Member Posts: 8
edited Jun 28, 2013 6:53AM in SQL & PL/SQL
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

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond
    edited Feb 2, 2012 8:17AM
    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)
  • Harm Joris ten Napel-Oracle
    Harm Joris ten Napel-Oracle Member Posts: 135 Employee
    edited Jun 28, 2013 6:54AM

    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

    [[email protected] 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

This discussion has been closed.