Kevin Kempf's Blog

December 7, 2009

Write to a socket from PL/SQL

Filed under: 10g, 11g, Linux, Oracle — kkempf @ 4:32 pm

During a code clean-up, I had to figure out how to send a sequence number to a dumb (are there any other kind?) Windows application server over a tcp socket.  This sequence number will effectively act as a trigger for the app server to go print a label (with data referenced by this sequence number).  The Linux guy showed me how to create a testing socket on my Linux desktop so I could verify it was working, and I thought it was interesting enough to pass along.  Really the only variables are the remote host IP/name (10.10.1.1 in my case), the remote port, and what you’re writing:

declare
 bt_conn               utl_tcp.connection;
 retval                binary_integer;
 l_sequence            varchar2(30) := 'Sequence Number 101021';
begin
 bt_conn := utl_tcp.open_connection(remote_host => '10.10.1.1'
                                   ,remote_port => 5000
                                   ,tx_timeout => 1
                                   )
 ;
 retval := utl_tcp.write_line(bt_conn,l_sequence)
 ;
 utl_tcp.flush(bt_conn)
 ;
 utl_tcp.close_connection(bt_conn)
 ;
exception
 when others then
  raise_application_error(-20101, sqlerrm)
  ; 
  utl_tcp.close_connection(bt_conn)
  ;
end
;
/

On my Linux box, I just opened a terminal and ran netcat, then ran the PL/SQL from another terminal session:


netcat listening on port 5000

Advertisements

Create a free website or blog at WordPress.com.