Tuesday, November 18, 2008

A Java PL/SQL Port Scanner

Saturday night I started to wonder how I could get the movies of my DVR. Recently Verizon FIOS added a boatload of HD stations so I've naturally begun to record those. Problem is, the 50 hours or so I have available are quickly eaten up by the HD versions.

We have the multi-room DVR setup, which means we have one DVR but we can view those movies from any TV (with a cable box). Naturally all these boxes are on my internal network (router provided by Verizon).

I understand that with some boxes you can go out and buy additional storage, but I wanted a DIY solution. So, if I could find an open port on one of these boxes, then perhaps I could figure out a way to get the movies onto my computer.

Java to the rescue. Well, not really, but I have always wanted to learn more about that kind of lower level programming (sockets/ports/TCP/whatever). See, I don't really know what I'm talking about which is why I did it.

Since I use JDeveloper for writing SQL and PL/SQL, creating a java class (with the Main method) is easy.
import java.io.IOException;
import java.net.Socket;
import java.net.UnknownHostException;
import java.util.Date;

public class Class1
private static void p ( String text )
System.out.println( text );

public static void main(String[] args) throws UnknownHostException, IOException
Date startDate = new Date();
String machine = "";
Thread thread = new Thread();

Socket socket;
int j = 0;
int x = 0;
p( "start date/time: " + startDate.toString() );
for ( int i = 1000; i < 10000; i++ )
p( "checking port: " + machine + ":" + i );
socket = new Socket( machine, i );
p( "Port open: " + machine + ":" + i );
catch (UnknownHostException e) { }
catch (IOException e) { }
p( "ports checked: " + x );
p( "ports open: " + j );
p( "start date/time: " + startDate.toString() );
p( "end date/time: " + new Date().toString() );
As LC would say, "That's not so bad."

Only it was slow, 1 port at a time. I did learn that trying to open up a port connection is s l o w. How can I speed this up?

I could make it threaded right? No. That would take too much time.

But hey, I know how to do it in PL/SQL (threaded that is). UTL_TCP should work nicely. I've never had to use it, so a great opportunity to familiarize myself with it (not that it's needed very often).

So I start Oracle XE and initially just try it out to see how it works:
c := utl_tcp.open_connection
( remote_host => '',
remote_port => 1,
tx_timeout => 1 );

utl_tcp.close_connection( c );
WHEN others THEN
utl_tcp.close_connection( c );
END check_port;
Easy enough. Now let's start use DBMS_JOB to "thread" this.
l_table R_RECORD;
job_count PLS_INTEGER;
job_number PLS_INTEGER;
l_table(1) := '';

FOR i IN 1..l_table.COUNT LOOP
FOR j IN 1..10000 LOOP
INTO job_count
FROM user_jobs;

WHILE job_count < 20 LOOP

( job => job_number,
what => 'BEGIN check_port( ''' || l_table(i) || ''',' || j || '); COMMIT; END;' );

So it's running, and running, and running...I opened up another session and did a count on user_jobs. 10000. 12000. 20000. Uh, I think there's something wrong here.

I finally kill the session and there are 36,446 jobs in the queue. All checking the same port.

Then I can't even issue a simple SELECT statement. Maximum number of sessions reached or some such rubbish. Just a reminder, it was Saturday night and I was just trying to see if I could do this quickly.

Since I couldn't connect, I tried bouncing the database (a loud gasp from the DBAs out there). Come on! It's a local XE instance...there's nothing important there.

Start it back up, connect as SYS and still can't issue any queries. I do notice as I hit the / sign and the Enter key a bunch of times that once in a while it will let me do a query. So I create a quick PL/SQL block to remove all the jobs from the queue.
FOR i IN ( SELECT job FROM user_jobs ) LOOP
dbms_job.remove( i.job );
I go through the / + Enter key about 50 times until it finally runs. Success!

Or so I think. Still receiving the max sessions error. There are still 36,446 jobs in user_jobs. I can't remove them anymore as I get "job doesn't exist" when I try to dbms_job.remove it again. Hmmm...How about?
DELETE FROM dba_jobs;

36,446 rows deleted.

How can that be? I didn't expect that to work at all...but it did. I have yet to look up the particulars of my actions (deleting from dba_jobs) but it did the job.

And yes, I'm still planning on writing this (correctly) in the near future...I want more movies!


oraclenerd said...

Some more information on the UTL_TCP package (and related errors):


tomcatkev said...

This is funny.

Of course the most productive approach might be to google "nmap" and find a free GPL port scanner.

But if you didn't write the code yourself hey you never know exactly if it does what you want, and PL/SQL UTL_TCP gives you the control.

oraclenerd said...


Using Ubuntu now gives me the requisite tools to do port scanning...I just wanted to see what was involved. More of a curiosity thing I guess. Would never claim to really know what I'm doing...just wanted to see what was possible.