Friday, March 02, 2007

Add the ability to make a connection read only

To show how simple it is to add functionality to the context menus in sqldeveloper without writing any java like I posted before about. I'll take a case which was on the forums today about how to make a connection read only. As most people probably know there's a set transaction read only; command which can be issued which prevents the session from issuing updates. To make it easy to toggle this in sqldeveloper, here's what I did:

The format of the xml is the same as the post before. Save this xml to a file:
<?xml version="1.0" encoding="UTF-8"?>
<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="dialogs.xsd">
<item type="CONNECTION" reload="true">
<title>Set RO</title>
<prompt type="confirm">
<label>Confirm Setting Connection to Read Only.</label>
</prompt>
<sql>
<![CDATA[begin dbms_transaction.read_only; end;]]>
</sql><help>Issues dbms_transaction.read_only; </help>
<confirmation>
><title>Confirmation</title>
<prompt>Success</prompt>
</confirmation>
</item>
<item type="CONNECTION" reload="true">
<title>Set RW</title>
<prompt type="confirm">
<label>Confirm Setting Connection to Read Write.</label>
</prompt>
<sql>
<![CDATA[begin dbms_transaction.read_write; end;]]>
</sql>
<help>Issues dbms_transaction.read_write; </help>
<confirmation>
<title>Confirmation</title>
<prompt>Success</prompt>
</confirmation>
</item>
</items>


Now go into the Preferences->User Defined Extensions. Prior to 1.1, this would have needed some java coding but now it's very easy to load this xml file. While this shows the file on the file system, it could just as easily use referenced a URL such as http://myhost.mycompany.com/some/path/to/somewhere/ro.xml

Now when after I restart the tool, I'll see the menu option and here's what happens when I try to update the data in a table:


Then If I want to edit again, I choose the other menu which sets things back to read write.

3 comments:

Jared said...

Very nice extension Kris.
I've always tried to be very careful when examining a production database with SQL Developer.

This extension will relieve some anxiety. :)

leegallonesq said...

Kris apologies I'm not a member but I have a question. I've searched around on the web with no joy for this, is there a way to make SQL Developer permanently read only as part of the install taht you are aware of?

Kris Rice said...

You could put the same read-only call into the login.sql script.
-kris

Disqus for Kris' Blog