One of many ...

There's lots of little things in the upcoming release of sqldev. Frank's post reminded me of one that I forgot to show at openworld. We have support for the same DVT Gauge component that he mentions in sqldev's next version. However, all you have to do is write a sql statement. It's really simple to use. For example, I took the free space report from Tom Kyte and added guages.

The only you'll need to do in sqldev is select the correct value and it'll turn it into a gauge. In the base sql, the percent used is

((kbytes_alloc-nvl(kbytes_free,0))/ kbytes_alloc)*100 pct_used " .

In order to make it more graphical I just changed it to this

'SQLDEV:GAUGE:0:100:0:0:'||((kbytes_alloc-nvl(kbytes_free,0))/ kbytes_alloc)*100 pct_used

The SQLDEV:GAUGE trigger the check for values. The first 2 numbers are the min and max values. The next 2 are the low and high values. Lastly, the current value to plot.

Oracle Open World

Open world is almost here. Here's my connect card. I'll pretty much be at the booth or between our sessions. Stop by if you want to chat.

Subscribe to Public Reports

Mark's post on the forums got me to do something I've been meaning to do for a while. I updated the reports section of the exchange today so that you can upload a report.xml. Now when you goto "My Reports" There's a file upload to load the whole xml file. This should allow people to post reports with children or whatever is in the xml.

Also I made this link for getting all public reports. The link is useful if you want to pull public reports into your sqldev. Here's all you have to do:

1) Go into the preferences and add a User Defined Extension:


2) Restart

3) You'll see the Shared Reports show up grouped by tghe user that posted them ( otn user name):

What's up

I forgot I added a real simple way to see what DBs are up in sqldev a while back. If in the worksheet, you run a command, F5 (run script), "pingall" it will do a tnsping of all the connections defined and report back. From this screenshot you'll notice most of my DBs are down. My plan was to hook up an indicator in the tree for down database but never got it well tested.

More Apex/SQLdev

Here's a couple more shots of the up coming Apex and SqlDev intergration.

1) Export your app:



2)Import the app:


3)Import Options:

SQL Injection scanners

Here's a list of the free sql injection tools over on security-hacks.

Releases

In the last week, the team has released 3 times.

1) SQL Developer 1.1.3 was released
2) Jdev 11 Preview was released where sqldev is built in
3) SQL Developer Migrations EA #2 was minutes ago posted. If your running 1.1.3, you'll see the update balloon soon.


However, I looked up some stats on usage and the majority of people are using 1.0.0 which was release March 2006. Here's the release notes for 1.1.3 so you can see what your missing.

Process a zip file

Some one on the forum asked for an example of how to bulk upload files. Here is an example I used a couple years ago of processing a zip file and uploading the individual files into a table. Then we made an application to manage the file and followed "Create a Procedure to Download Documents" to download.

Here's the sql for the table and sequence:

create table docs(doc_id number,blob_content blob,file_name varchar2(2000));

create sequence doc_id_seq;

New patch with APEX features

I just pushed sqldeveloper 1.1.3. There's about 200 bug fixes in this build some more visible ones are here. There are a couple new things like a couple more sqlplus command supported , print , accept. The other thing that you will notice is new reports.


Mike Hichwa added some reports for Application Express. So now you can easily see the applications you have access to and some details about them.




The best thing about these reports is that Mike added them using nothing more than the report dialogs. The only thing that was manually added was the check for version of apex and that was just editing the xml file.

Here's what the Applications report looks like.



Here's another that let's you see the images the workspace.



The only down side is that APEX 3.0.1 is required and it hasn't been released yet.

csv with sqldeveloper

Laurent shows how to format a select * into csv.


Give this a try in sqldeveloper, simply add a comment in the select and it's csv. See this:

Interactive Reports part 3

The next step to having all these great reports in xml files would be to organize them. This one is the simplest by far. The name of the folder is split on the / and sub folders are made. This is very handy when a report gets into 100s of lines of xml since the reports can now be stored in multiple files but foldered together in the tool. Like in sqldev the "Data Dictionary Reports -> Database Administration -> Sessions -> Session Reports" report which allows kill/trace sessions is over 500 lines by itself and in a separate file.

<folder>
   <name><![CDATA[my/great/report]]></name>
   <display>.....</display>
</folder>

Interactive Reports part 2

In this post, I'll show how to add a context menu to a report. The same thing can be done to a tab which has been added which is described in Question #1 here.

For more on the <display> tag see this post. For more on the <item> tag see this post.

Here's an xml file of the 2 combined. The things to look at are that the <item> is inside the <display> . Then look at the bind variables :TABLE_NAME which will be taken from the row clicked on in the grid. Lastly in the sql and confirmation there's #TABLE_NAME# which will also come from the row clicked on and do a replacement of that text.

<?xml version="1.0" encoding="UTF-8"?>
<displays>
  <display id="" type="" style="Table" enable="true">
    <name><![CDATA[Good Stuff Report]]></name>
    <description><![CDATA[my great report]]></description>
    <tooltip><![CDATA[This is the best report ever]]></tooltip>
    <query>
      <sql><![CDATA[select * from user_tables]]></sql>
    </query>
    <item>
      <title>Logging</title>
      <prompt>
        <label>Logging:</label>
        <value><![CDATA[STATIC:LOGGING:NOLOGGING]]></value>
        <default><![CDATA[select logging from user_tables where table_name = :TABLE_NAME]]></default>
      </prompt>
      <prompt type="confirm">
        <label>Change logging status of selected table?</label>
      </prompt>
      <sql><![CDATA[alter table "#TABLE_NAME#" #0#]]></sql>
      <help>Change the logging status of a table.  LOGGING will log changes to the redo log file.<br/>
  NOLOGGING will not log changes to the redo log file.</help>
      <confirmation>
        <title>Confirmation</title>
        <prompt>Table "#TABLE_NAME#" set to #0#</prompt>
      </confirmation>
    </item>
  </display>
</displays>

Here's the menu choice:



Here's the dialog:



Here's the confirmation:

Extending the Navigator with children

I started to answer this question with a reply to the comment but thought I'd just put a post together since this is should make it easier to find.

Question #3: Do you have the xsd file ?
There is an XSD for the navigator in sqldev 1.1 and it's located here. In the xsd you'll notice there's quite a bit that I didn't mention before.

Question #2: How to add multi-level folder like "Storage" > "Datafiles" > "Files" ?
The full xml from the first post is here. The part that will add the children is the <node/> tag which quite empty in the example. All that has to be done to add children is change the <node /> tag to something like this with PARENT_NAME being the bind for the immediate parent in the tree.

<node>
   <childType id="COLUMN"">
      <icon RSKEY="/path/to/my/icon.png"/>
      <queries >
          <query>
            <sql>
              <![CDATA[SELECT COLUMN_NAME
      FROM SYS.ALL_TAB_COLUMNS
      WHERE OWNER = :SCHEMA
      AND TABLE_NAME = :PARENT_NAME
      ORDER BY COLUMN_ID]]>
            </sql>
          </query>
        </queries>
   </childType>
</node>

Question #1: How to link navigator "id" with editor "node" in the case I want to add say "SQL*Profiles" ?

The posts I did a while back explain how to get tabs added with Forgotten tabs however this has changed quite a lot in 1.1. The simpliest way now to add a tab to the broswing is to goto into the reports section and define your report exactly how you'd like to see it. Be sure to use :OBJECT_NAME , :OBJECT_TYPE , :OBJECT_OWNER as the bind variables. Then once everything is setup just right click on the report and choose Export. Export this file to some location and open it in your favorite xml editor. You'll get something that looks like this. Now in the <display tag add objectType="TABLE" Oracle will be the default for any tabs added to change to something else add connType="TimesTen".

The resulting display tag will look something like this:

<display id="" style="Table" enable="true" connType="Oracle" objectType="TABLE">

Now to add this file, it just like adding a file for the navigator. Go into the preferences and click add row , choose EDITOR.

Interactive Reports part 1

The reports in sqldev 1.1 change a lot. Among the changes are different types of reports, master details , context menus and drill downs. Alot of these features can be created/changed by the Create Report dialog but a number of them can not. I'll try and explain how to do things not in the dialog in the next few blogs working up to a interactive report which will show how to add these to your own reports.

In this post I'll talk about about the basic changes to the xml. In 1.0, the report looked like this:

<report type="report" enable="true">
  <name><![CDATA[Good Stuff Report]]></name>
  <description><![CDATA[my great report]]></description>
  <tooltip><![CDATA[This is the best report ever]]></tooltip>
    <query >
       <sql><![CDATA[select ......]]></sql>
  </query>
</report>

If you had some reports in 1.0 and have since start 1.1, you can look at the file in $HOME/.sqldeveloper/UserReports.xml and see the file was migrated. This same report in 1.1 will look like:

<display id="" type="" style="Table" enable="true">
  <name><![CDATA[Good Stuff Report]]></name>
  <description><![CDATA[my great report]]></description>
  <tooltip><![CDATA[This is the best report ever]]></tooltip>
  <query>
    <sql><![CDATA[select dummy from dual]]></sql>
  </query>
</display>

While this mostly looks the same, the noticeable addition is the id, type, style attributes. The id attribute is what shows up in the LOV of the Advanced Tab of the dialog when the Add Report is pressed. The type attribute isn't used in this example yet. The style is what kind of report it is and the default styles are

• Table - normal grid of data
• Code - A big text field of the data concatenated together
• Chart - renders a chart from the data
• Script - runs the contents of the sql tag as a sql script
• plsql-dbms_output - This will grab the dbms_output from the plsql and render it as html
• More later on how to create your own

To make a master detail report all that's needed is to add another display tag inside the existing one. The binds for the child will be looked up into the row which is selected in the parent.

<display id="" type="" style="Table" enable="true">
  <name><![CDATA[Good Stuff Report]]></name>
  <description><![CDATA[my great report]]></description>
  <tooltip><![CDATA[This is the best report ever]]></tooltip>
  <query>
    <sql><![CDATA[select dummy from dual]]></sql>
  </query>

  <!-- child -->
  <display id="" type="" style="Table" enable="true">
    <name><![CDATA[Details of stuff ]]></name>
    <description><![CDATA[my details ]]></description>
    <tooltip><![CDATA[This is very interesting stuff]]></tooltip>
    <query>
      <sql><![CDATA[select :DUMMY parent_data from dual]]></sql>
    </query>
  </display>
</display>

Now I've shown nothing more than what the Report dialog can do to create a master/detail report. The first thing you can do which is not in the dialog is take these display tags and nest them as deep as you'd like. Such as this which adds a grandchild to this example:

<display id="" type="" style="Table" enable="true">
  <name><![CDATA[Good Stuff Report]]></name>
  <description><![CDATA[my great report]]></description>
  <tooltip><![CDATA[This is the best report ever]]></tooltip>
  <query>
    <sql><![CDATA[select dummy from dual]]></sql>
  </query>

    <!--  child -->
    <display id="" type="" style="Table" enable="true">
       <name><![CDATA[Details of stuff ]]></name>
       <description><![CDATA[my details ]]></description>
       <tooltip><![CDATA[This is very interesting stuff]]></tooltip>
       <query>
         <sql><![CDATA[select :DUMMY parent_data from dual]]></sql>
       </query>
      
        <!-- grandchild -->
        <display id="" type="" style="Table" enable="true">
           <name><![CDATA[Details of stuff ]]></name>
           <description><![CDATA[my details ]]></description>
           <tooltip><![CDATA[This is very interesting stuff]]></tooltip>
           <query>
              <sql><![CDATA[select :PARENT_DATA parent_data from dual]]></sql>
           </query>
        </display>
    </display>
</display>

Targeted Job Ads?

While checking my gmail, I noticed this sponsored link. Targeted job ads based on history seems like a very easy use of the massive data google compiles.

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.

Better APEX urls

I was cleaning up a webserver and found this code I wrote a few years ago to help make HTMLDB now Application Express entry points nicer. Hopefully everyone knows Application Express and uses it everyday, if not you should go sign up and kick the tires at apex.oracle.com. Prior to working on SQL Developer, I did some work in Application Express and built some internal systems with Carl and others. One issue I had was an easy way to map a url to an application in order to make the entry point nice and easy like http://myapp.mycompany.com which really point to http://apex.oracle.com/pls/otn/f?p=MYAPP:MYPAGE

Now you may think just a simple Apache re-write would solve this and you'd be correct but I had to add multiple re-write and didn't want to bounce Apache in order to add new ones or change existing ones. So the answer was in mod_perl. What I did was make a mapping file as follows:

  mycompany.com;/pls/apex/f?p=3701:1
  internal.mycompany.com;/pls/apex/f?p=3701:1
  yourcompany.com;/pls/apex/f?p=111:1
  /path;/pls/apex/f?p=1:1

You can see from the mapping file that it's fairly flexible. You can map /myapp or myapp.mycompany.com. The best part about this solution is that the perl code below re-reads the file every 5 minutes in case you added more mappings and no bounce of apache.

These 2 lines will have to be placed early in the httpd.conf so it can have the opportunity to look at all the incoming request. Here's the 2 lines needed to change in the httpd.conf file

PerlModule HTMLDB::VirtualServer
PerlTransHandler HTMLDB::VirtualServer

This code will have to be adjusted for location of the config file and maybe you'll adjust the reloading of the file but it should mostly work. This file will have to be placed into the perl library path with something like this:

SetEnv PERL5LIB  "/mypath/to/the/path"

package HTMLDB::VirtualServer;
#
#
# Apache httpd.conf entry
# PerlModule HTMLDB::VirtualServer
# PerlTransHandler HTMLDB::VirtualServer
#
#
# Sample config file
#mycompany.com;/pls/apex/f?p=3701:1
#internal.mycompany.com;/pls/apex/f?p=3701:1
#yourcompany.com;/pls/apex/f?p=111:1
#/path;/pls/apex/f?p=1:1
#
#
  use Apache::Constants qw(REDIRECT DECLINED); 
  use strict;
  # path to config file
  my $configfile = "/export/home/oracle/HTMLDB/htmldbvirtual.conf";
  my $debug = 0;
  my $lastLoad;
  my %names = ();


# simple routine to see what's going on
sub logMe{
   my $logLine = shift;
   if ( $debug eq 1 ) {

  # log file if $debug = 1

     open(L,'>>/tmp/htmldbvirtual.log');
     print L $logLine;
     close L;
  }
}
  
# load the config file  
sub loadConfigFile{
  my $now = time;apex/home
  # cache the config for 5 minutes then reload
  if ( ( $now - $lastLoad ) > 600 ) {
    open CONF,$configfile;
    my $name;
    my $uri;
    while (){
      chomp;
        ($name,$uri) = split /;/;
          $names{$name} = $uri;
    }
    $lastLoad = time;
      if ( $debug) {
        while ( my ($key, $value) = each(%names) ) { logMe( "L:$key => $value\n"); }
       }
  }
   %names;
 }


 sub handler {
      my $r = shift;
      my %names = loadConfigFile();
      logMe("Request:" . $r->uri . "\n");

     if  (  $r->uri eq "/" 
            ||  $r->uri eq "/index.html" 
            ||  ( length($names{$r->uri}) > 0 ||  length($names{$r->uri."/index.html"} ) > 0 )  ) {
            	
      # grab vars for use.
      my $s = $r->server();
      my $hostname = $r->hostname();
      my %args = $r->args;
      my $base =  $s->port() == 443 ? "https://" : "http://";

      if ( length($hostname) > 0 ) {
        $base = $base . $hostname;
      } else {
        $base = $base . $s->server_hostname();
      }

      if ( $s->port() != 80 && $s->port() != 443 )  {
       $base = $base . ":" . $s->port();
      }
      logMe($base . "\n");

      my $key = length($names{$r->uri} ) > 0 ? $r->uri : $hostname ;

      if (  length($names{$key} ) > 0
          && ! $args {"p"} ) {
           #$r->header_out(Location => $base . $hostname );
           $r->header_out(Location => $base . $names{$key} );

         logMe("HTMLDB::VirtualServer:". $base .  $names{$key} . "\n");
         return REDIRECT; # means we did a redirect
      }
   }
         return DECLINED; # means we did not handle the request
  }
1;

Sql Worksheet Tips

in a sql worksheet try some of these:

ctrl-shift-0..9

This will pop the 0-9th sql form the history and replace the current sql worksheet contents.

The rest need to be run as if they are scripts ( F5 ):

Want to change the tab in the worksheet while a script runs to see an easy status.

   set worksheetname hello

Working on APEX or mod_plsql routines?

   set owacgienv on
   set getpage on
   begin
      htp.p('hi');
   end;
   /

This will make the owa.init_cgi_env before a plsql block and owa.get_page after and print in the script output.


Lastly, if you have any issue you can turn debug on with this and then take it to the forums to post and get answers:

setloglevel oracle INFO

Learn something new everyday

I was testing various things in sql developer and you'd think I'd know what's in there but not so. As most people know sql developer uses swing for the UI. Swing components allow you to render the content as html. This is really easy for example a JLabel.setLabel('<html><b>hi') will bold the text hi. The limitation is it's html 3.2 in java 1.5.

So I tried this:

select '<html><b>'||dummy from dual;
or
select '<html><i>'||dummy from dual;
or maybe more useful:
select '<html><i>'|| object_name object_name,
     '<html><b>'|| object_id object_if,
    decode(status,'VALID',null,'<html><font color="red"><u>')||status status
from user_objects

And here's what the last one looks like: