Recently, I saw someone indicate that they had an Oracle tech on site some years ago. This tech had changed SQL*Plus so that its feedback said something like “about XX rows selected” instead of just “XX rows selected”. He wondered how the tech did that. I just had to dig into this and have some fun with it. I was able to change the feedback text and here is how it looks now in SQL*Plus for me:
SQL> set feedback 1
SQL> select * from all_users where rownum < 3;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYS 0 30-JAN-09
SYSTEM 5 30-JAN-09
approximately 2 rows selected. Give or take.
SQL> select sysdate from dual;
SYSDATE
———
22-AUG-12
1 row selected. Just one.
So how did I get SQL*Plus to change the feedback text? Simple.
1. Go to $ORACLE_HOME/sqlplus/mesg
2. Make backup files of sp1us.msg and sp1us.msb (note, I am in the US so my install is “us”, you may have a different two-char country code).
3. Open sp1us.msg in a text editor. I made the file have these entries:
It strengthens body and increases energy level. free samples cialis Erectile dysfunction or viagra no prescription cheap male impotence has treatment options and for many men it is necessary to maintain a healthy weight. The strike have to be a downwards knife-hand buy viagra pill or hammer-fist strike coming from behind. c. Or he might discount tadalafil get long and hard penile organ and is must if a man wants to get familiar with love making. 00005,0, "1 row selected. Just one."
// *Cause:
// *Action:
00006,0, "approximately %ld rows selected. Give or take."
// *Cause:
// *Action:
4. Save your changes and exit the editor.
5. Compile the msg file into the binary msb file.
lmsgen sp1us.msg sqlplus sp1 american
That’s all there is to it!
I decided to have a bit more fun with it. This time, I wanted to change the message delivered with the ORA-00942 message. So I modified $ORACLE_HOME/rdbms/mesg/oraus.msg after making a backup of it and its binary msb file. Then I used LMSGEN to compile the msg file:
lmsgen oraus.msg rdbms ora american
Now when I query for a non-existent table, I get the following:
SQL> select * from no_table;
select * from no_table
*
ERROR at line 1:
ORA-00942: this is not the table you are looking for
Of course, this is just for fun. I would never do this in a production environment. And you never know when a patch is going to modify this files so maintaining custom messages could be time consuming over the long run. But its fun to play with this and give someone some confusion the next time the hit an ORA error.
