Figured this one out by sheer, dumb luck.

<cfscript>
driverManager = createObject("java","java.sql.DriverManager");

conn = driverManager.getConnection("jdbc:macromedia:oracle://111.11.11.111:1521;SID=mySID;serverName=111.11.11.111;user=**********;password=**********");

stmt = conn.createStatement();
recordSet = stmt.ExecuteQuery("select * from table order by 1 desc");
results = createObject("java", "coldfusion.sql.QueryTable").init(recordSet);
</cfscript>

<cfdump var="#results#">

The trick was to add the username and password as properties of the connection string rather than having them inline.

This fails

driverManager.getConnection("jdbc:macromedia:oracle:username/password/111.11.11.111:1521;SID=mySID;serverName=111.11.11.111;")