Bash completion for Sql*Plus

If most people are like me, you don't like to type something twice. Here's something I wrote a while back to add completion to sqlplus in bash. I had copied it to a new machine to and thought I'd post it in case someone might find it usefull so here it is.


This file will check the ~/.bash_history for previous sqlplus commands as well as complete @.../foo.sql

To try this grab this and drop it into the ~/.bashrc or ~/.bash_profile. If Bash Completion is loaded, it can be dropped into /etc/bash_completion.d/ .


------FILE-------
_sqlplus()
{
local cur
COMPREPLY=()
cur=${COMP_WORDS[COMP_CWORD]}
prev=${COMP_WORDS[COMP_CWORD-1]}

if [ $COMP_CWORD -eq 1 ] && [[ "$cur" == -* ]]; then
# return a list of switched
COMPREPLY=( $( compgen -W '-H -V -C -L -M -R -S' -- $cur ) )
elif [[ "$cur" == "/" ]]; then
# only /nolog is possible
COMPREPLY=( $( compgen -W '/nolog' -- $cur ) )
elif [[ "$prev" == "as" ]]; then
# as sysdba sysoper
COMPREPLY=( $( compgen -W 'sysdba sysoper' -- $cur ) )
elif [[ "$prev" == "-R" ]]; then
# added for completness
COMPREPLY=( $( compgen -W '1 2 3' -- $cur ) )
elif [[ "$cur" =~ "@" ]]; then
# if @
base=${cur:1}
COMPREPLY=( ${COMPREPLY[@]:-} $( compgen -f -P "@" -X "$xspec" -- "$base" ) $( compgen -d -P "@" -- "$base" ) )
elif [[ "$prev" =~ "@" ]]; then
# if @
COMPREPLY=( ${COMPREPLY[@]:-} $( compgen -f -P "@" -X "$xspec" -- "$cur" ) $( compgen -d -P "@" -- "$cur" ) )
elif [[ "$*" =~ "/" ]] ;then
# already has a / assume it's the pass
COMPREPLY=
else
#default
_history
fi
}
complete -F _sqlplus $nospace $filenames sqlplus


_history()
{
local cur
cur=${COMP_WORDS[COMP_CWORD]}
COMPREPLY=( $( compgen -W '$( command grep "^sqlplus" ~/.bash_history ) ' -- $cur ) )
}
------FILE-------

If you notice the history funcion is pretty simple so modifying it to check other files would be very easy

Drillable Reports

Sue blogged about how some reports have drill thru ability to get the underlying object. This rasises the question of how can I do that to my report which lists my most interesting objects?

The answer to this is very simple. Here's an example which is simple query of which object were altered most recently and makes it a drill thru.


This SQL run in the worksheet gives you a list of object.
select owner,
object_type,
object_name,
last_ddl_time
from all_objects ao
where owner <> 'SYS'
order by last_ddl_time desc;

Instead of then finding your way to that object in the tree let's make it drill thru. This is done very easily by adding a few columns.
select owner,
object_type,
object_name,
last_ddl_time ,
owner sdev_link_owner,
object_type sdev_link_type,
object_name sdev_link_name
from all_objects ao
where owner <> 'SYS'
order by last_ddl_time desc;


The fist thing that is obvious will be that these columns will not show in the results. Now when the query is run and a row is double clicked you will be taken to that object. The only catches are it has to be an object which is supported ( basically see the tree ) and you must have access. Before anyone askes there is currently no way to go to tab #X of the object.

I'm sure someone can make better use of this for things like my largest object report or maybe a report on V$DB_OBJECT_CACHE

This can be used in the worksheet or in the sql of a report.

SQL Developer Podcast

A few weeks ago I sat down with Tom Haunert and we recorded a podcast. Now that SQL Developer has gone published Here's a podcast which was done a few weeks ago.

Listed here http://www.oracle.com/technology/syndication/techcasts/index.html

And direct here MP3

1.0 has landed

Ok if the name had stayed raptor the title would have made more sense.

As Brian Duff and Eddie Awad and Andrew Clarke have all stated SQL Developer is finally gone 1.0.

The interest as truely amazed me. The first code checkins were early September. From then we demoed in Chuck and Andy's keynotes as well as did numerous demos in the demo pods stealing time from the migrations and apex pods. In under 6 months, we have made or 1.0 release available with 7 early access releases which started December 27th.

Here's some quick stats on the tool up thru yesterday.

• Over 80,000 Downloads
  
• Over 140 countries ( 72% of countries in the world have downloaded and according to Number of Countries )
  
• 94% Windows , 5% linux and 0.6% Mac OSX
  

Another thing that surprised me was that there were 2 extensions written which we were still in an EA status. First was georaptor followed by Fourth Elepant's Insider for SQL Developer

Here's some of the press coverage

• Officel PR
  
• Info Week
  
• eWeek
  
• Internetnews.com
  
• InfoWorld
  
• Computer World
  

Well now we're on to 1.1 planning but that doesn't mean we'll be silent. I almost have a SQL Developer Studio done which will allow for 3rd parties to register extensions, define reports , snippets and who know what else I can get squeezed in. I'll keep the examples coming as well as any updates I can share.

Enhanced Excel and Save Snippets

I just updated the files out on esdev.sf.net.
Here's the changes in the export to Excel:

• Print column names into a frozen row
  
• Now it can be canceled
  
• Remembers last file
  
• Number are truely number in the cells
  

There's also some new stuff around snippets.


I've added the ability to hightligh some text in the worksheet and save it as a snippet so it can be easily referenced later. It's pretty simple take as you will see


First select the text to be saved in the worksheet.




Now give it a name , category and description.



Now use it.


This will save the file into ~/.snippets.xml

This file is added different than my previous blog on snippets. It uses
SnippetAddin.registerSnippet(SnippetController.snippetFile.toURL());

This call could be used by other extensions to add custom snippets more easily.

There's a similar call to add reports by bunlding an xml compliant file to your jar file
ReportAddin.registerSnippet(someUrlObject);

XMLTypes , Excel and an Example Extension

Since new coding is pretty much ramped down for SQL Developer , I needed a place to test out some features in a way that everyone can benifit. I've created a project on sourceforge, http://sourceforge.net/projects/esdev. The current extension there enables support for readonly XMLTypes and adds export to excel in native excel inthe worksheet thanks to Poi.


I hope this project also shows how easy it is to extend base functionality. The web cvs isn't working yet but once it is, take a look and see what it took to add this.

If you would like to see these examples in action, add the center.xml to your check for update. The center file is http://osdn.dl.sourceforge.net/sourceforge/esdev/center.xml

I'll probably tinker here with trying to intergrate some open source projects into sql developer.

Wonder twin powers activate!

Form of an Application, Form of a debugger..

The standard manner to debug any APEX application is done with wwv_flow.debug which execute when the page is run by a developer in debug. Wouldn't it be nice to have a full stack of the code executing with inspection? Read on to see how to initiate a full debug session in SQL Developer from an APEX based application.


There's some setup involved the form of persmission which have to be granted to the user who owns the code as well as the user who is configured to run APEX.

First grant the ability to connect a debug session to the parse_as in APEX. In this case klrice.
SQL> grant DEBUG CONNECT SESSION to klrice;
Grant succeeded.

Second grant the ability to debug to the user configured to run APEX in mod_plsql. Since I'm using XE as my example it's public.
SQL> grant debug on klrice.ls to public;
Grant succeeded.


Now everything is setup and on to the APEX application. This is a very simple example but should show enough of how to make it more generic.

Here's a screenshot of my page.



There is nothing special in the plsql region except it's a plsql region running a very small procedure name LS which loops and does a HTP.P. This procedure needs to be compiled for debug.

Next there's a checkbox for flagging when to connect for debugging and not. Then the real debugger code is in the Processes. The first is a Before Header to be able to debug anything executed on the page. This process is conditional based ont he value of the P1_DEBUG checkbox as follows:

dbms_debug_jdwp.connect_tcp('localhost',4000);

While localhost is hardcoded something like owa_util.get_cgi_env('REMOTE_ADDR') could be used to make it more generic. Also note the port is hardcoded to 4000 , this is important in a sec.

Since the debugger would now be connected, it's nice to disconnect so that's done like this in the After Footer Process:

dbms_debug_jdwp.disconnect;
:P1_DEBUG := 'N';


I've also reset the value of P1_DEBUG to force the checkbox to be choosen each time.

Now into SQL Developer. Right Click on the connection to the user which is the same as the APEX application and choose Remote Debug



Now to bring it all together. Run the application check the checkbox to start the debugger.

This is what should result in SQL Developer:


I hope this helps in the much enjoyed task of bug fixing.