The purpose of plugging Oracle XMPP is to integrate Instant messaging in a secure way in already existing production environments, as it seems like more and more people do use XMPP for different reasons. As a DBA, will appreciate to get Alert Messages from my instances without having to open ports to the world wide web as network people do dot really appreciate to open ports to the outside. So, here we are, i'll provide you a very simple tutorial on how to interact with Openfire from Oracle XMPP.
In this howto, i'll assume that you will use Openfire and Spark default settings, so if you perform a Next/Next install, things will be straight for you.
In this very short tutorial, be sure to follow some very basic conditions.
Be sure that the Oracle-XMPP account and target user do accept messages the one from the other. In my case (surprise !!!), the XMMP used to send messsages from Oracle is the openfire configured scott/tiger user, and i'll send messages to prslump. In my case, everything runs locally on my laptop and my hostname is l20323.
In my configuration, i connect my self to the Openfire with the Spark client as prslump. That's all, we are ready to start make interact Oracle with Openfire/Spark.
Log on with your prslump/target IM account (the one that will receive incoming messages) with Spark (or any other XMPP client of your choice, like Pidgin) to your running Openfire server
Notice that everything is here running locally, so for a more realistic example, replace localhost with the server name that hosts Openfire.
Next, run from your Oracle session :
select dbms_xmpp.test_connection('localhost',
5222,
'spark',
'scott',
'tiger') xmpp_return_code from dual;
And you should get the following output :
XMPP_RETURN_CODE
----------------
0
this is it. We are ready to go further.In this section, scott/tiger will send a message to prslump, and it will be a bit fun. Before to run the script, be sure to always have your Spark client, logged as prslump, and at a give instant, you will see scott online, then you get the message, then back offline.... hmmm looks like dba have funny games ;-)
Therefore, just run :
select dbms_xmpp.send_plain_text_message('localhost',
5222,
'spark',
'scott',
'tiger',
'prslump@l20323',
'Dummy subject',
'Hi, this is scott talking ! Can you run stats on my schema please ?') xmpp_return_code
from dual;
And you should get the following output (and of course your message in your IM client) :
XMPP_RETURN_CODE
----------------
0
Finally, discover scott's contacts. Hence we should (at least) discover prslump, therefore, just run :
- get XMPP rosters
col ID for 99
col ROSTER_NAME for a30
col ROSTER_USER for a30
col ROSTER_TYPE for a5
col ROSTER_STATUS for a30
set linesize 135
select ID as "Id",
ROSTER_NAME as "Name",
ROSTER_USER as "User",
ROSTER_TYPE as "Type",
ROSTER_STATUS as "Status"
from table(dbms_xmpp.rosters_table('localhost',
5222,
'spark',
'scott',
'tiger'));
Then we get something like that :
Id Name User Type Status --- ---------- ---------------- ---------------- ---------------------- 1 prslump prslump@l20323 both ITEM_STATUS_NULLThat's all.
As you can see, there's nothing more to configure to interact with Openfire and Spark from Oracle-XMPP. Initially, i wanted to interact with Openfire for two main reasons :