Barry McGillin

Using the Oracle Developer Days VirtualBox Image

2011-12-07T01:07:00.001-08:00

As many of you know @krisrice put together a great VM for the Oracle Developer Days and while the content is great for education, I find us using it more and more a default scratch database on laptops. As usual, being 'Networkly challenged', it took me some time to figure out which network adapters did what and why. This is as much a note for me as it is to share with you :).

First of all, you need to install VirtualBox, which is found here. Then download one of the prebuilt VirtualBox images for the Developer Days. You can choose your one here. Download the ova file and then import your VM into VirtualBox.

To import, choose File> Import Appliance in VirtualBox and click choose to select the directory where you want the VM to live, then select your ova file. In this example, I'm using @krisrice's Oracle DeveloperDays VM. Once you click finish, and agree to the licenses, you'll see the 'Oracle Developer Days' VM in VirtualBox with a powered off state.

Great. We're ready to fire up the box and play inside it. All passwords for this are 'oracle', so you cant go wrong. In this, the database is all setup automatically, as is this list of stuff.

Oracle Linux 5
Oracle Database 11g Release 2 Enterprise Edition
Oracle TimesTen In-Memory Database Cache
Oracle XML DB
Oracle SQL Developer
Oracle SQL Developer Data Modeler
Oracle Application Express
Oracle JDeveloper
Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

So now, login to the vm with oracle/oracle and a terminal window will show you whats available and the network profiles you setup in the VM settings. The network settings I setup for this is here

These two adapters do separate things. The bridged adapter will assign an IP address from the wireless NIC. This is setup by default when you install the VM and allows you to get access to the internet from the VM. The NAT adapter will allow you to access the VM from the host machine when you have no physical NIC or internet available. This happened me yesterday when presenting some SQLDeveloper functionality. By enabling the NAT adapter and setting up a few port forwarding rules, we can ssh into the VM and connect SQL Developer to the normal LISTENER port.
The NAT adapter looks like this.

and setting up two port forwarding rules, gives us this

When you login to the VM, you'll see this on the terminal window.

This setup will allow you to spark up firefox in the VM and connect to the internet using the local network ip 192.168.1.42. Now, if we switch off the Airport and disable the bridging adapter, we should still be able to connect to the VM from outside. Restarting the VM, gives us this terminal window on login to the oracle account

Now, we have no external IP, but we have our port forwarding rules set up to get access to the VM from outside. Now, we have two rules, one which maps anything on port 2222 to port 22 on the guest. this means we can ssh into the VM on port 2222 on the host. So, connecting with this

and we get the login terminal from the VM

Brilliant. Now, Lets see SQL Developer connect to the VM too. We setup the connection like a connection to xe on localhost, except the SID is orcl on the VM.

and we can look at the database version report which shows us what database we are connected to

And now, you're connected and good to go. This is great for doing demos in a canned environment, especially when you dont have access to an internet connection when doing a demo or showing something off.

Explain Plan, Autotrace and Diff

2011-12-05T10:42:00.000-08:00

A SQL statement can be executed in many different ways, such as full table scans, index scans, nested loops, and hash joins. The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN button on the worksheet. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. SQL Developer looks at the table and displays the tree operations. Looking at the example, we can see the query is doing a Cartesian product

Similary, when you use autotrace, which, on SQL Developer, is the button beside the explain plan. AND, you can diff two plans to see what the difference is between two plans, as you can see below.

However, even though we can show you what a plan looks like, it cannot differentiate between well-tuned statements and those that perform poorly.

For example, an EXPLAIN PLAN output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient. In this case, you should examine the following:

The columns of the index being used
Their selectivity (fraction of table being accessed)

It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption.

And from the doc!:

In addition to running the EXPLAIN PLAN command and displaying the plan, you can use the V$SQL_PLAN views to display the execution plan of a SQL statement. After the statement has executed, you can display the plan by querying the V$SQL_PLAN view. V$SQL_PLAN contains the execution plan for every statement stored in the cursor cache.

The V$SQL_PLAN_STATISTICS view provides the actual execution statistics for every operation in the plan, such as the number of output rows and elapsed time. All statistics, except the number of output rows, are cumulative. For example, the statistics for a join operation also includes the statistics for its two inputs. The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set toALL.

The V$SQL_PLAN_STATISTICS_ALL view enables side by side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time. This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information for every cursor.

Anyway, don't take my word for all this, try it on SQLDeveloper today and you can follow Tom Kyte's process for tuning sql statements.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8764517459743

UKOUG 2011

2011-11-28T03:03:00.001-08:00

I'm speaking next week at the UK Oracle User Group at the ICC in Birmingham. The topic will be one I've posted several times about which is Tuning, Refactoring and Instrumentation. Have a look at the agenda, and if you are in town, come along. You can click on the image to go to the conference site and check out the agenda.

login.sql, SQLPROMPT and worksheets

2011-11-18T17:57:00.001-08:00

SQLDeveloper has had support for a login.sql for several releases now. You can set this in the preferences at

Tools -> Prefernces -> Database

You can set your login.sql here.

Now, when SQL*Plus starts up, it looks for a global login script called glogin.sql in the $ORACLE_HOME/sqlplus/admin directory. If found, this script will be executed.
Thereafter, SQL*Plus will try to find a local login script called login.sql in the directory where you start sqlplus from, alternatively the directories listed in the SQLPATH environment variable. When found, sqlplus will execute it. Here's my login.sql for SQL*Plus

define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
  from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '

and when I login to sqlplus, I get this.

SQL*Plus: Release 11.2.0.2.0 Beta on Mon Nov 21 11:05:58 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta


GLOBAL_NAME
-----------------------------------------------------------------

barry@XE

barry@XE>

Obviously, in SQLDeveloper, this won't mean anything as prompts are not there, however, the variables you set and the column formats, titles, pagesizes etc, will be preserved.

For example, in our login2.sql list in the preferences above, we set a couple of column settings and for fun, lets set the prompt variable too.

As shown above, we now have a login.sql defined in the preferences. When we make a connection, the login.sql will be run and any settings will be applied to the database. We will also hold onto any SQL*Plus variables defined so they can be used in any worksheet that is started using this connection.

When you connect, you get the new worksheet, and the sql that was run produces any output in the messages.log. The reason for this is you can connect to the database without spawning a worksheet and this lets you know of any output from that login script we put together earlier.

Now, we are connected, any worksheet created on that connection will have the context of the original script.

Lastly, you can also set your worksheet name to be a substitution variable as well

set worksheetname &gname

which will swap you default worksheet name to your connection credentials shown above.

Substitution Variables in SQL*Plus

2011-11-15T08:52:00.001-08:00

Working through security issues uncovers some interesting things. Anyone who has developed scripts for building out schemas for an application will have had the issues of passing variables to subscripts or managing password visibility when creating users, building objects or granting permissions

SQLDeveloper and SQL*Plus have substitution variables to solve this problem. Basically, there are two types of substitution variables, & and &&. &foo is used to refer to the variable foo. &&foo is also used to refer to the variable foo. The main difference between the two variables is that first time SQL*Plus comes across a variable defined with &&, e.g., &&foo, it prompts for the value and then uses this values for every other occurrence of the variable. &foo on the other hand will prompt for the variable, use it and then discard the value so the next time it is seen, it will prompt again.So, now an example. Lets assume we want to create a user db1 and db2 with some tables in each user. We can define a simple script for each one.

define db1_password=&&db1
define db2_password=&&db2
@@users.sql
@@db1.sql
@@db2.sql

Now, this script does three things.

it defines two variables for the passwords of the two users we are going to create.
@@users.sql sets up the users for us. Remember, in SQL*Plus, there is only ever one connection active, so whenever you have a connect statement in your script, that will be the user running the script until you change it. In this case above, we're starting with a privileged user to create the users.
@@db1.sql and @@db2.sql create our users for us and we will create a table in each one. We'll see that script in a moment.

drop user user1 cascade;
prompt creating user user1
create user user1 identified by &&db1_password;
grant connect, resource to user1;
drop user user2 cascade;
prompt creating user user2
create user user2 identified by &&db2_password;
grant connect, resource to user2;

db1.sql and db2.sql are identical apart from user names.

connect user1/&&db1
create table table1 (id number, name varchar2(10));
insert into table1 values (1,'barry');

Substitution variables are passed to any subscript called from the main script. The initial &&db1 and &&db2 are prompted for and set to the variables db1_password and db2_password
From then on, the subscripts use the variables to connect to each user.

Finally, the output from all of this is below. I have VERIFY=ON here to show the substitutions going through, but for any real world scenario, you'll want to switch that off avoid printing the passwords.

user USER1 dropped.
creating user user1
old:create user user1 identified by &&db1_password
new:create user user1 identified by db1
user USER1 created.
grant succeeded.
user USER2 dropped.
creating user user2
old:create user user2 identified by &&db2_password
new:create user user2 identified by db2
user USER2 created.
grant succeeded.
old:connect user1/&&db1_password
new:connect user1/db1
Connected
table TABLE1 created.
1 rows inserted.
old:connect user2/&&db2_password
new:connect user2/db2
Connected
table TABLE2 created.
1 rows inserted.
Connection created by CONNECT script command disconnected

With verify off, you get cleaner output with no passwords. If you need more ouput here, you can also use the prompt command to identify which script you are in and what is running.

user USER1 dropped.
creating user user1
user USER1 created.
grant succeeded.
user USER2 dropped.
creating user user2
user USER2 created.
grant succeeded.
Connected
table TABLE1 created.
1 rows inserted.
Connected
table TABLE2 created.
1 rows inserted.
Connection created by CONNECT script command disconnected

SQLPLus as an external tool in SQLDeveloper

2011-11-14T07:20:00.001-08:00

@thatjeffsmith asked me today about running SQL*Plus from SQLDeveloper for his current sql file. This has been shown before, but there is a simple way to add it and to get it to run your file under SQL*Plus.

The only caveate on this example, is that we are using the bequeath adapter to connect to a local XE database. You can amend this to add a service after the username

The main steps are to point the program executable to your SQLPlus, which will populate the executable and the Run directory. Next you need to populate the arguments, which for sqlplus are like this.

sqlplus [LOGIN} @{FILENEMAME]

where LOGIN can be any of this.

{[/][@] | / }
[AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]

So, our arguments will be

${sqldev.dbuser}/${promptl:label=Password} @${file.dir}/${file.name}

You can check the variables you can submit with the insert button. I am choosing a labelled prompt for the password here for security.

Once we have all that done we can see our External tool in the list of tools.

Now, all we have to do is make sure our file is in focus and click the SQL*Plus button. This will run the file with the current user of the connection we have on the file and prompt for the password.

My File looks like this

clear screen
select username from all_users where username like 'B%';

and running it with my new SQL*Plus button gives me this.

C:\oraclexe\app\oracle\product\11.2.0\server\bin>
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe barry/barry "@C:\Documents and Settings\bamcgill\Desktop/Untitled1.sql"

SQL*Plus: Release 11.2.0.2.0 Beta on Mon Nov 14 15:38:25 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

 

USERNAME
------------------------------
BARRY
BARRY2

SQL>

Now, thats not so secure either, since you can see my password, so we want to switch off banner output too which will suppress the login string and the header. You can do this by editing your external tools and adding it to the arguments like this.

-S ${sqldev.dbuser}/${promptl:label=Password} @${file.dir}/${file.name}

giving you what you want here.

C:\oraclexe\app\oracle\product\11.2.0\server\bin>
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe -S barry/barry "@C:\Documents and Settings\bamcgill\Desktop/Untitled1.sql"
 

USERNAME
------------------------------
BARRY
BARRY2

Proxy Connections

2011-10-18T06:06:00.000-07:00

While looking at some issues with specific connection upgrades, I've been playing with proxy connections in SQL Developer, and while easy to do, can be interesting to get your head around. There are a number of things to do which are important. So, Lets start with a proxy user called proxy and a target user called target. (Nice and original)

drop user proxy cascade;
drop user target cascade;
create user proxy identified by proxy;
create user target identified by target;
alter user target grant connect through proxy;
grant create session to proxy;
grant connect, resource to target;
connect target/target;
create table target (id number);
insert into target values (1);
connect proxy[target]/proxy;
show user
select * from target;

This set of commands run as Sys in the worksheet will create the two users. The proxy privilege is granted using

alter user target grant connect through proxy;

The target user is granted resource role to create a table, in this case, we call it target and put some data in it. Next, we can connect to the target user, through the proxy using

connect proxy[target]/proxy;select * from target;

This all gives us this feedback including the user which is actually connected.

user PROXY dropped.user TARGET dropped.user PROXY created.user TARGET created.user TARGET altered.grant succeeded.grant succeeded.Connectedtable TARGET created.1 rows inserted.ConnectedUSER is TARGETID-- 1 Connection created by CONNECT script command disconnected

We can also set this up in SQL Developer using the connection dialog. Given the users have been created as above and the appropriate privileges have been granted, we can set this up in the dialog

Once we make the connection, we can expand the table tree and see the target table from the user we proxied into to.

The data is exactly the same as the data from the Worksheet script output as well.

One thing I did forget to mention was the ability to create distinguished proxies as well. You do the same thing with the connection panels and but switch to disctinguished name. You can set up a distinguished user doing the following.


drop user dproxy cascade;
create user dproxy identified globally as 'CD=dproxy,OU=europe,O=oracle,L=omagh,ST=tyrone,C=ie';
alter user dproxy grant connect through barry authenticated using distinguished name;

SQL Developer 3.1 EA1 Available

2011-10-11T03:30:00.000-07:00

SQL Developer 3.1 EA is now available for download here. Have a look and try out several of the new features introduced as part of this release. We have done a lot of work around adding DBA functionality for RMAN, Datapump and other features and vastly improved Database Diff, Database Copy and Database Export.
In Migration, we've introduced a TeraSQL translator and greatly enhance our Sybase TSQL parse. We've also done a lot of work with insight, including templates for most statements directly from published documentation and added column formatting for SQL*Plus. These are only some of the highlights for this release and we will publish a full list soon.
As usual, you can ask us any questions on the forum or add a feature request. Let us know what you think!

Tuning, Refactoring and Instrumentation

2011-10-05T15:34:00.000-07:00

For all those who attended the talk today, this is a promise partially fulfilled in that I had said I would post in more detail about the talk. At a high level, we talked about three different topics. Tuning, Refactoring and Instrumentation. I'll list out the main points here today and flesh these out over the next few days for each of the bullet points.

SQL Developer support several types of tuning activities. These are:

Explain Plan/ Autotrace and Diff
Monitoring SQL
SQL Tuning Advisor
PLSQL Hierarchical Profiler

For Refactoring, there are several options as well, and these grow with every release.

Code Surrounding
Procedure extraction
Local variable renaming
Obfuscation

Instrumentation is a way of finding out what an application is doing, who is using it, how its doing and and how long it has taken. In order to look at this from a database application point of view, we can break application instrumentation into a number of key areas.

Debugging
Logging
Runtime registration
Metric Collection

Each of these key areas have specific tools and processes which should be implemented as part of your application development and we will get into those in the section on Instrumentation.

SQL Developer Data Pump Support - Part 2

2011-09-12T10:17:00.000-07:00

Welcome to part 2 of this feature on our introduction of data pump functionality into SQL Developer. Previously, we walked through exporting from the database. This post will go through the importing the data export to a new schema in our database.
In the DBA navigator, go to the data pump node, and choose 'Data Pump Import Wizard'.

When the wizard appears, choose the type of import you want. In our previous episode, we exported the 'BARRY' Schema. We'll now choose to do a schema import.

For the input directories, choose a directory that exists and you have access to. The dump files from the export session need to reside on this directory and conform to the filename as specified.
Hitting next, will parse the files and step 2 shows us the available schema to choose from. Since we only export 'BARRY', we only have one to choose from in this case.

Step 3 involves remapping. In this case, on my database, the Barry schema exists. I need to create another schema to create these objects in. I've done this already before we started the wizard.

Once the schema has been created and we're back in the wizard, we can set the destination as 'BARRY2'

Step 4 has two parts, one is for logging and we choose an appropriate database directory for that which exists and we have access to. The second is for the actions on tables if they exist. In my case, I want to replace them. For this example, we know its a fresh schema with nothing in it, but if it did, we'd be replacing the tables.

Lastly, we can schedule the import, and like last time, I want to do this immediately.

The summary shows us what will be done on our behalf and once we hit the finish button, an import job will be created and kicked off immediately.

When the job starts, there will be an import job shown in the dba navigator. its corresponding editor will show the job executing.

Finally, we can create a connection for the BARRY2 schema and look at the data.

So, that's it in a nutshell. Over two parts, we've shown you how to export any part of a database to file using the data pump utilities which have been integrated into Oracle SQL Developer. In this part, we took those files from the previous post and imported them into Oracle.

SQL Developer Data Pump Support - Part 1

2011-09-12T09:20:00.000-07:00

In 3.1 we're adding support for Data Pump, which has replaced exp and imp. In SQL Developer, we've added this in the DBA navigator which has support for several new things this release. In this post, I'm only going through Data Pump export and in part 2 of this post, we'll visit the import. We'll revisit several of other new DBA features in the very near future. For export, the demonstration will show that this is pretty easy to use, however, get a cup of coffee, cos we've a few snapshots to go through!

Ok, so first off, we need to fire up the DBA Navigator which can be reached from the view menu. Select DBA and you get the navigator shown on the right. This image shows the sys user which has been selected as the current connection. We're going to use the barry connection for using data pump today.

From the data pump node, select the menu item for the Data Pump Export Wizard.

On the first page of this wizard, we are choosing schema today. You also have the option of exporting the entire database, some tablespaces or a block of tables. When you're ready press next

As we said earlier, we are only choosing schema today and in the image below, we are only selecting one schema for export. The 'BARRY' schema.

Step 3 allows you to filter what is exported by including or excluding various things, you can choose none of these things or, a selection of each with an appropriate filter string in the value field.
We won't add any today and let it take everything out of the schema.

In Step 4, the data tab, we can add data filters to all the tables, or individual tables as required. Again for this demonstration, I'm not going to choose any of that and let the data pump export everything.

Step 5 allows us to specify the options for the export. Our primary interest is in the directory for the logging.

Step 6 focuses on our Output directory. Specify the one you want to use here. Remember, as in Step 5, this directory must exist, and you must have the privileges to write to it from your user. Check on the main connections navigator for directories. By default there is a DATA_PUMP_DIR setup, but make sure that the directory exists. We've created a directory called 'BARRYS_DPUMP_DIR' for this example.

Step 7 specifies the schedule which will dump the data for you. We're choosing immediately as our option. You can specify whenever you like for this job to run, repeatedly if required.

Lastly, we have the summary screen, which is split in two parts. The main summary screen shows what actions you are taking and how they will be carried out. The second panel shows the actual PL/SQL which will be run on your behalf to create the data pump job, and dump the data.

When you click finish, and job submission is successful, you'll get a new job listed in the DBA navigator under Data Pump export Jobs.

If it doesn't appear, then it's highly likely you'll get this error, primarily due to the directory that you are writing the files or the logs to. This is easily fixed by going back and making sure the directories you have chosen, exist and that you have access to read from and write to them.

Lastly, you can go to your database directory and see your exported file, together with the log file.

Your log file will contain something like this if you're successful. (I've cut a lot out of it as it is long)

Starting "BARRY"."EXPORT_JOB_SQLDEV_327":  
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "BARRY"."MD_ADDITIONAL_PROPERTIES"              3 MB
.  estimated "BARRY"."STAGE_TERADATA_TABLETEXT"          2.062 MB
.  estimated "BARRY"."MD_DERIVATIVES"                        2 MB
.  estimated "BARRY"."MD_FILE_ARTIFACTS"                     2 MB
...
.  estimated "BARRY"."文化大革命"                                 0 KB
Total estimation using BLOCKS method: 17.75 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
.....
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "BARRY"."MD_ADDITIONAL_PROPERTIES"          13.54 KB      59 rows
. . exported "BARRY"."STAGE_TERADATA_TABLETEXT"          1.197 MB      25 rows
. . exported "BARRY"."MD_DERIVATIVES"                    49.60 KB     386 rows
...
. . exported "BARRY"."文化大革命"                                 0 KB       0 rows
Master table "BARRY"."EXPORT_JOB_SQLDEV_327" successfully loaded/unloaded
******************************************************************************
Dump file set for BARRY.EXPORT_JOB_SQLDEV_327 is:
  D:\DEMO\DPUMP\EXPDAT01.DMP
Job "BARRY"."EXPORT_JOB_SQLDEV_327" successfully completed at 15:42:52

So, for today, thats exporting from the Oracle database using the Datapump built into Oracle SQL Developer 3.1 which will be available soon! We'll have part 2 on importing this dump file next.

SQL*Plus Formatting Commands

2011-09-07T07:48:00.000-07:00

Since the last post on SQL*Plus commands was completed, we have also added more functionality to the SQL Developer's SQL*Plus support.
Specifically, we've added some formatting reports to help the folks who have tons of plus reports running for various things. These features will be a part of the next major release of SQL Developer. Lets have a look and see whats supported.

It might be easier to tell you whats not supported now. Its 'break' and 'sum'. Most other SQLPlus formatting is now supported. So, lets take a look and see how it looks.

These features will be available in the next release of SQL Developer.

In SQL Developer, as you add SQL*plus command, they stay around for the lifetime of your worksheet, just like SQL*Plus unless you get rid of them. To Clear all your column formatting just use

clear columns

Now we've a clean setup and ready to start. As usual, if you want to see what settings are in force, you can issue the command:

show all

which will give you the usual list of suspects as you have seen before here. With formatting now, there are a few more. We'll see those shortly when we do a little bit of formatting. Using the standard HR schema in XE, a simple query on the employees table

select * from employees where rownum < 5;

gives us this

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE                JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------------ ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03                AD_PRES         24000                                      90 
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         21-SEP-05                AD_VP           17000                       100            90 
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-01                AD_VP           17000                       100            90 
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         03-JAN-06                IT_PROG          9000                       102            60 

 4 rows selected

Lovely, right? Of course, but we could do better. So, lets remove the columns we're not interested in using the column noprint option.

column employee_id noprint
column job_id noprint
column commission_pct noprint
column manager_id noprint
column department_id noprint
select * from employees where rownum < 5;

which now gives us this.

FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE                    SALARY 
-------------------- ------------------------- ------------------------- -------------------- ------------------------ ---------- 
Steven               King                      SKING                     515.123.4567         17-JUN-03                     24000 
Neena                Kochhar                   NKOCHHAR                  515.123.4568         21-SEP-05                     17000 
Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-01                     17000 
Alexander            Hunold                    AHUNOLD                   590.423.4567         03-JAN-06                      9000 

 4 rows selected

Ok, its looking better, but theres a lot of white space in there. Lets take that out.

column employee_id noprint
column job_id noprint
column commission_pct noprint
column manager_id noprint
column department_id noprint
column first_name format a10 
column last_name format a7 
column email format a8 
column hire_date format a10 
select * from employees where rownum < 5;

which now looks a lot tidier.

FIRST_NAME LAST_NA EMAIL    PHONE_NUMBER         HIRE_DATE      SALARY 
---------- ------- -------- -------------------- ---------- ---------- 
Steven     King    SKING    515.123.4567         17-JUN-03       24000 
Neena      Kochhar NKOCHHAR 515.123.4568         21-SEP-05       17000 
Lex        De Haan LDEHAAN  515.123.4569         13-JAN-01       17000 
Alexander  Hunold  AHUNOLD  590.423.4567         03-JAN-06        9000 

 4 rows selected

Now, this fits nicely in to the page and we can also make the separators and titles a little fancier


clear columns
set colsep "|"
column employee_id noprint
column job_id noprint
column commission_pct noprint
column manager_id noprint
column department_id noprint
column first_name format a10 heading "First|Name"
column last_name format a7 heading "Last|Name"
column email format a8 heading "Email"
column hire_date format a10 heading "Hired"
column phone_number format a12 heading "Phone"
column salary format $999,999 heading "Salary"
select * from employees where rownum < 5;

resulting in this


First     |Last   |        |            |          |             
Name      |Name   |Email   |Phone       |Hired     |    Salary 
---------- ------- -------- ------------ ---------- ---------- 
Steven    |King   |SKING   |515.123.4567|17-JUN-03 |   $24,000
Neena     |Kochhar|NKOCHHAR|515.123.4568|21-SEP-05 |   $17,000
Lex       |De Haan|LDEHAAN |515.123.4569|13-JAN-01 |   $17,000
Alexander |Hunold |AHUNOLD |590.423.4567|03-JAN-06 |    $9,000

 4 rows selected

which spruces it up a bit. Now, we can add titles top and bottom using standard SQL*Plus syntax too


clear columns
CLEAR screen

set colsep "|"
column employee_id noprint
column job_id noprint
column commission_pct noprint
column manager_id noprint
column department_id noprint
column first_name format a10 heading "First|Name"
column last_name format a7 heading "Last|Name"
column email format a8 heading "Email"
column hire_date format a10 heading "Hired"
column phone_number format a12 heading "Phone"
column salary format $999,999 heading "Salary"
SET linesize 80
SET pagesize 15

ttitle LEFT '09/07/2011' RIGHT 'Page:' SQL.pno SKIP 2 CENTER 'HR REPORT' SKIP
BTITLE COL 35 'CONFIDENTIAL' ON

select * from employees where rownum < 7;

and our report looks like this now.


09/07/2011                                                             Page:   1
                                                                                
                                   HR REPORT                                    
First     | Last  |        |            |          |            
Name      | Name  | Email  | Phone      |  Hired   |   Salary 
---------- ------- -------- ------------ ---------- ----------
Steven    |King   |SKING   |515.123.4567|17-JUN-03 |   $24,000
Neena     |Kochhar|NKOCHHAR|515.123.4568|21-SEP-05 |   $17,000
Lex       |De Haan|LDEHAAN |515.123.4569|13-JAN-01 |   $17,000
Alexander |Hunold |AHUNOLD |590.423.4567|03-JAN-06 |    $9,000
Bruce     |Ernst  |BERNST  |590.423.4568|21-MAY-07 |    $6,000
David     |Austin |DAUSTIN |590.423.4569|25-JUN-05 |    $4,800



                                                                                
                                   CONFIDENTIAL                                 

 6 rows selected

Pretty nifty, if you've got several SQL*Plus reports around that you need to run on SQL Developer. Remember, like SQL*Plus, you can check the formatting set at any time by typing

column

giving

COLUMN  'first_name' ON
FORMAT  a10
HEADING  'First|Name' headsep '|'
JUSTIFY right

COLUMN  'hire_date' ON
FORMAT  a10
HEADING  'Hired' headsep '|'


COLUMN  'phone_number' ON
FORMAT  a12
HEADING  'Phone' headsep '|'


COLUMN  'commission_pct' ON
noprint

COLUMN  'email' ON
FORMAT  a8
HEADING  'Email' headsep '|'

COLUMN  'manager_id' ON
noprint

COLUMN  'department_id' ON
noprint

COLUMN  'last_name' ON
FORMAT  a7
HEADING  'Last|Name' headsep '|'

COLUMN  'salary' ON
FORMAT  $99,999
HEADING  'Salary' headsep '|'


COLUMN  'employee_id' ON
noprint

COLUMN  'job_id' ON
noprint

column employee_id

giving


COLUMN  'employee_id' ON
noprint

You can find out more about SQLDeveloper in the usual place, and if there are features you'd like to see in the tool, submit a Feature Request. If you need more details on SQL*Plus, the latest documentation is here.

SQL*Plus Commands

2011-07-04T09:37:00.000-07:00

Its funny how many people forget we support a lot of the basic SQLPlus functionality in SQL Developer. For example, today, Paulo blogged on how to stop scanning for substitution variables in a script.

He rightly pointed to the deprecated 'SET SCAN OFF' and the current 'SET DEFINE OFF' to fix his problem.

We support a lot of plus parameters in SQL*Plus and are adding more on each release. For now, though, if you are using SQLDeveloper and are looking for the current settings in the worksheet, you can use the 'show all' command which shows the state of any parameters that are in force at that time.

e.g., The default will give you this.

appinfo is OFF and set to "SQL Developer"

arraysize default

autocommit OFF

autoprint OFF

autotrace OFF

copycommit 0

define "&"

echo OFF

escape OFF

FEEDBACK ON for 6 or more rows

null ""

serveroutput OFF

spool OFF

sqlcode 0

termout ON

timing OFF

USER is HR

verify ON

For supported SQL*Plus commands you can also use 'help' command which lists the commands supported. e.g.,

For help on a topic type help

List of Help topics available:

APPEND

ARCHIVE LOG

CHANGE

COLUMN

CONNECT

COPY

DEFINE

DEL

DESCRIBE

DISCONNECT

EDIT

EXECUTE

EXIT

GET

HOST

INPUT

LIST

PASSWORD

PAUSE

PROMPT

QUIT

REMARK

RESERVED WORDS

RUN

SAVE

SET

SHOW

SHUTDOWN

SPOOL

SQLPLUS

START

STARTUP

TIMING

UNDEFINE

VARIABLE

WHENEVER OSERROR

WHENEVER SQLERROR

XQUERY

I'll follow this up with another post of the SQLDeveloper 3.1 functionality where we are supporting more of the printing functionality around column formatting and titles.

Scanning Applications

2011-06-17T10:00:00.000-07:00

When re-platforming any application, there are specific things that must be changed in order to make that application work on the new platform. Database migration has been available in SQL Developer since 1.2. However, this is only part of the process of changing an application.

In SQL Developer 3.0, we introduced the ability to scan application source code for items of interest that required migration. We call these items rules. These rules are defined in a simple XML format. We have included rules for Sybase CT and DB lib client applications by default.

In 3.1 we extended the support for applications and have opened up the rules API so that a rule can be specified for any type of file. We have included a mechanism to recognize files with and without extensions so we only apply rules appropriate to the file type.

This produces a nice summary of the contents of the application, no matter what source is used. As you can see each rule is made is made up of a regular expression. Each rule is applied to the file and the results aggregated and summarized by type, call and volume.

High Level Overview

-------------------

63 total calls found

43 distinct calls found

8 files scanned

8 language types

12 total files in source

443 lines of code in scanned application

-------------------------------------------------------------

File Type Summary

----------------------

perl 1 file

sql 1 file

c 2 files

php 1 file

java 1 file

ksh 2 files

asp 1 file

specialized 1 file

-------------------------------------------------------------

As we get closer to release we can take a closer look at how we can replace some of the things we found automatically.

Oracle Database Development Tools (ODDT): We are looking for APEX and HTML / CSS Developers

2011-06-03T02:30:00.000-07:00

Oracle Database Development Tools (ODDT): We are looking for APEX and HTML / CSS Developers: The Oracle Application Express (APEX) team is expanding and looking for additional developers. We are looking for people who can build web applications, SQL and PL/SQL skills are helpful, as are HTML, CSS, JS. We are also looking for an iPhone (Objective C) developer as well. If interested apply at Oracle http://recruitment.oracle.com IRC1545977, IRC1549990, IRC1549989.

QR Codes - SQL Developer

2011-05-03T07:03:00.000-07:00

Using QR codes is a cool way to jump links in mobile content. This one points you to Oracle SQL Developers home page. You can generate your own at http://qrcode.kaywa.com/

Oracle XE 11.2 is here

2011-04-04T05:28:00.000-07:00

Straight from Kris' Blog

Oracle XE 11.2 is now in Beta. It can be downloaded here with the documentation here. The forum is here for any questions or issues uncovered.

SQL Developer 3.0

2011-03-30T01:23:00.000-07:00

Last night we released SQL Developer 3.0. This has been a large release with a lot of new base features and new extensions. At a high level, these are the features added.

Schema browser
Query Builder
PL/SQL updates
Import and Export updates
PDF file support
Extended Unit Testing
DBA Navigator
Extended Tuning Support
DBMS Scheduler
Database migration
Integrated spatial support
Data Miner

Download it today and have a play with it. As usual, you can comment on the forum or submit feature request on the exchange.

Detecting Hangs in Java

2011-01-17T04:08:00.001-08:00

One of the things we are looking into right now is performance in SQL Developer and concurrency of operations. Sometimes, operations that are spawned on the EDT should not be there.

These are typical scenarios of the SwingUtilities.invokelater(Runnable).

These operations show themselves to the user as a perceived hang in the UI which is not wanted.

There are two ways to find these in Java.

You can use JStack, which is part of the Java SDK. This is very simple to run, giving it the Process ID of the java program and it prints a stack dump of the JVM.

Here is some example output from a recent one I found with it.

"AWT-EventQueue-0" prio=6 tid=0x041ac800 nid=0x10f0 runnable [0x0398b000]
java.lang.Thread.State: RUNNABLE
at java.util.regex.Pattern$BnM.optimize(Pattern.java:4946)
at java.util.regex.Pattern.compile(Pattern.java:1473)
at java.util.regex.Pattern.(Pattern.java:1133)
at java.util.regex.Pattern.compile(Pattern.java:823)
at oracle.dbtools.migration.parser.ext.ExtAutoIndentWriter.
(ExtAutoIndentWriter.java:30)

This is the relevant snippet of the trace which shows that we are running on the event queue. Fixing this is a simple case of moving it out of this thread to one of its own, in this case, we've created a RaptorTask to take care of it.
The other way to find these could be to build in a log handler to your application which can pop a stack trace when a hang is detected. In SQL Developer, we have log handlers like this built in and when the appropriate flag is set, and the EDT is waiting for a predetermined amount of time, we pop a stack trace, right there to show what was running at that point in time.

As we go through these, its making SQL Developer much more performant and responsive working with remote databases and allowing the user to keep control of the UI.

Clicking around the worksheet

2011-01-14T03:33:00.000-08:00

As we have been getting close to another EA release, one thing which struck me as pretty cool and something we haven't talked about in a while are the click actions in the worksheet.

Looking at the image on the right, we can see the first action that is available. By holding the down while we move the mouse over the table employees we can see that it is highlighted and underlined. If we click on this, it will open up the appropriate editor.

Next we can see the list of SQL*Plus files below the query in the worksheet. Normally, these are written by locating the file and putting the appropriate syntax in place.

Well to make reviewing this files easier, we've added a similar functionality to these files. This way when we click on a file call, and the file exists, we can open that in an editor for reviewing and editing.

There are three places where we will look for files. The first is straight forward and is the complete file path name.

@/MY/FULL/PATH/NAME/BARRY.SQL

The second is taking files in your ORACLE_HOME directory and the '?' directive denotes that directory. so a typical directory looks like this.

@?/admin/catalog.sql

Finally, the last one, has the format:

@barry.sql

This last file structure takes the file from the USER_HOME directory in SQL Developer or, the place where you ran a script, or if you have the script directory set in the worksheet preferences, it will look there.

This will be available in our next EA, EA3.

Enjoy!

Disconnecting Folder Connections

2010-12-07T14:13:00.001-08:00

A post came in on the forum the other day about disconnecting numbers of connections in SQL Developer that are open. This was on the Feature request application for a while and having a few hours free with snow 9 inches deep outside, I plugged it in.

This feature now has 2 options. The first is on the main connections node where you can now disconnect all connections that are open.

The other option added was on folders. Now you can disconnect all the connections in a folder which if you have them organised by category or type can help speed up your maneuvers around the tool.

This will be in the next EA of SQL Developer 3.0.

Cross Connection Queries

2010-11-19T10:45:00.000-08:00

When we were hooking up the "Copy to Oracle" functionality, Dermot added a cool bit of code to allow us to do queries across connections.

The Copy to Oracle function allows you point at a table in any supported connection type and choose copy to oracle from the menu. This then goes and creates the table in Oracle and populates it with data from the source table.

Now, as I said, to do this, Dermot added some functionality to enable this. This functionality is very flexible and the UI in Copy to Oracle uses this mechanism to achieve the functionality.

The command shown here is just the basics of what this can do and we will document this in more detail as we go on.

BRIDGE DEMO_CUSTOMERS AS access

(SELECT * FROM "Order Details") ;

This command can be broken into a few pieces. After the BRIDGE token, the identifier is the name of the new table to be create in the current Oracle connection. The access identifier represents the connection that we will copy the table from and the query between the braces is the query which will be run on the source connection as the data for the new table.

Running the commands listed here, drop the table if it exists and creates it on the Oracle Connection.

We then run a query against that connection we get the following results in the results panel.

This is just a small view into what the bridge command can do and how it is used in the Copy to Oracle Functionality today.

Now we have the table is in Oracle. However, we can do something even cooler than this. We can join a table in Oracle with a table in Access and show the results.

BRIDGE TEMPcustomers AS access

(SELECT * FROM "Order Details")

SELECT * FROM TEMPcustomers,Products

where Products.productid=TEMPcustomers.ProductID;

This is a little step further. We take the table in Access, copy it to Oracle as a temporary table and join it with a table in Oracle to show the results below.

Migration Estimation from SQL Developer

2010-10-28T03:15:00.000-07:00

Last post we mentioned all the features of 3.0 for migration. This one, we're taking it a bit further and looking at the overview of the migration project itself.

For most users, the main thing they want to know is how much of the migration will be completed automatically. We've introduced a new spreadsheet which gets generated from the repository and gives a birds eye view of your database servers and your applications which are being migrated.

Here we have a number of migration projects in the migration navigator which are Sybase, SQL Server and DB2. We have converted all three and need to know how much of the migration the tools can do.

Very simply, we can right click on the top projects node, or any individual project node and choose to "create an estimation plan". This will generate a spreadsheet with the detail of the project with a high level summary of the state of the project.

On the summary page, there are 4 different graphs. The top two deal with the databases being migrated and how much automation the tool can provide. Specifically, they show how many objects failed as part of the migration and which area the failures occured in. The bottom two show the applications which have been scanned and show what percentage of the files which make up the application have database calls which need to be changed. This example shows the Sybase ctlib sample applications, the most of which have things to change.

This represents the first page of the migration plan. Each of the other pages in the plan break down the database migration into tables, stored procedures, triggers and view detail. The user is allowed to add specifics for their organisation which will help put together a project estimate of how long it will take and how much resource it will take to bring the database migration project to a production state.

Migration Features in SQL Developer 3.0

2010-10-18T12:34:00.000-07:00

Since we released 2.1, we've worked with several customers and have added several great new features which have helped these customers increase the automation within their migration and the information they receive about it.

Here's a quick breakdown and we'll delve into these in later posts.

Command line interface

Most customers are looking to use a command line interface to do at least some of the migration for them, These can include all the steps that SQL Developer supports:

Capture - Connect to a source database, scan the data dictionary and create an independent model of the source.
Analyze - Analyze the model and report on what the source database contains and what issues there might be in it which need manual intervention
Convert to Oracle. - Convert the source model into its Oracle equivilent.
Generate Oracle Model - From the converted model, the user can generate the DDL for all the new schema in his database including all PLSQL
Build new database - The tool can run the sql produced and report on the errors which were found, even correlating between objects in the source and objects in the target
Move data - You can move data in two ways, either connected to the target database or you can generate unload files for the source database and SQL*Loader scripts to load this into Oracle

Enterprise Capture

This new feature allows the user to point at a server and capture all the databases in that server. This has been tested on Servers with up on 100 databases, captured and processed in minutes. Its one of the biggest requests we have had over the last few releases and this makes multi schema migration a breeze.

Application Scanning

In 3.0 we have introduced the concept of applications tied to databases. For this release we have focused on ctlib and dblib programs. Today, we can find all sybase calls in any program. We generate details reports on the contents of your application which provide information on size and complexity of the migration problem.

Estimation Reports

3.0 has introduced extensive database reports on the migration. Specifically, the reports include

High level object summary
High level error summary
Detailed error summary
Detailed object size summary
Detailed comparison between source objects, capture and converted objects and the new Oracle object
Temporary table usage

Migration Project Navigator

3.0 has also introduced a brand new project concept for Migration Projects. This is a new navigator which holds all the servers in a project all together. This has the benefit of being able to run several projects together, but report centrally on any one of them at any time with detailed information.

Migration Wizard

In 3.0, we have changed the quick migrate wizard to be a generic multi entry, single source of true path through a migration. This replaces the whole Quick Migrate concept and is now the only way to do a migration with the tool.

Parser and Translation enhancements
A lot of work has gone into fixing issues within the translators so they can perform in a better way and produce more consistent output. We are continuing to improve this with each release.

Copy to Oracle

Lastly, we have a new concept on all the third party navigators which is called "Copy to Oracle". This allows the user to copy tables and procedures from third parties into an oracle schema, without a migration repository.

SQL Developer 3.0 EA1

2010-10-16T10:32:00.000-07:00

After nearly a year in development, we're ready to show you what we have been doing in Sql Developer. You can try it out from here.

Over the next few weeks, I'll be going through some of the features and giving some ideas as to what this can be used for.
B

Kissing - Keeping it simple

2009-11-19T08:27:00.000-08:00

I spotted this today and couldnt resist talking about it. I was reading Eric Burke's comic look at typical product masters and looking inwardly at our own products. Most of the 'Walk up and use' products are things like the iPod, the toaster and other.

Avoiding the 'me too' scenario is going against the grain in many places. For SQL Developer, we have tried to keep the amount of functionality exposed as clear and simple as possible.

In 2.1 we have pushed a lot of the added functionality down into menus which do not need to be used all the time. We have simplified the number of dockables that we start and made a lot of the basic functionality of the navigator, worksheet and data management much cleaner and simpler.

We're getting ready to cut a release candidate so you should check it out for yourself.

Database Unit Testing:- Do you do it?

2009-11-13T09:13:00.000-08:00

Testing. When you're writing code, how often do you test it? Ok, you test it, but how often do you spend the time to put a set of discrete tests together to make sure that your code is doing what you thought it would do and moreover, it doesnt do what its not supposed to.

As part of SQL Developer 2.1, Unit Testing is one of the main features we have added. This is very different to anything you've seen in this area before. There are 5 different things on the new navigator we have added.

Tests: These are individual or discreate tests against a piece of code like a stored procedure or a function or package. Each test can have several implementations of the test. I.e., if it is a stored procedure, you can have an implementation with n implementation depending on the permutations of the paramters that you want to support.
Suites: This is a regular concept, like JUnit, which you can use to group your tests and run them together, in sequence. In fact, with 2.1, you can run unit tests from the command line which means you can hook them up with your build machine and run these in as part of a standard build and publish results with your builds
reports: We have canned several reports so you can see how your suite of tests ran, what the coverage numbers are and allow you to drill through each implementation that was run.
Lookups: Lookups are setup to allow you to predefine static parameter values that can be group by functional need and used in test implementations.
Libraries: Like the word says, these are libraries of startups, teardowns, validations, and defined dynamic values that we want to use regularly in tests.

The test here shows each implementation of the test. The implementation is a set of parameters which will give you a specific result, based on processing the parameters as part of the stored procedure.

This is our first release of Unit Testing and we will add more features in the next few releases. This release allows you to set up suites of tests with parameters covered by all the scalar datatypes. We will cover more abstract types in future releases.

This is available as part of SQL Developer 2.1, which at time of writing this is available as an EA from http://otn.oracle.com/sqldeveloper. Download it and play with it. If there are things you like, let us know and if there are things you would like added, then you can let us know on the feature request page as usual which is http://sqldeveloper.oracle.com

Everyday tasks with Sql Developer

2009-10-11T12:18:00.001-07:00

Sue has kicked off open world introducing SqlDeveloper 2.1.
You should check it out on http://sqldeveloper.oracle.com .
If you're around openworld tomorrow, you need to check out Kris Rice's talk on Unit testing. It's new, different and really cool!

Products and Services | Oracle

2009-08-27T09:58:00.001-07:00

Products and Services | Oracle

Shared via AddThis

APEX on MacOSX

2009-04-02T05:30:00.001-07:00

Well, I eventually got around to it and have APEX running on my Macbookpro with 10GR2.
Very nice. Building up an apex application is neat when you can do it where I live.

This leaves my dev environment all set up on mac now with:

SQLDeveloper 1.5.4
Apex 3.2
Oracle 10gr2
and Java with Eclipse :)

So, ok, whats weird or unusual about this setup you ask? Speed. The database and apex responses are instant on the macbook pro, and when I'm done I can export the app and deploy it on apex.oracle.com to share it with other folks.

Installing Oracle.....

2009-03-10T10:28:00.000-07:00

Well, We do it alot and this is no exception. or is it...

SQL Developer 1.5.4 coming soon

2009-02-27T12:07:00.001-08:00

Working through the last issues with SQL Developer 1.5.4. We've done a lot of work with this to address functionality that people required in the base product and now have translated it to 9 languages.

English
German
Spanish
French
Italian
Portuguese
Chinese
Simplified Chines
Japanese
Korean

Once we are happy with the current builds, we'll be out to users as soon as we can. I also hope that this is the version to be adopted in the Oracle Database 11GR2 database

Image Authoring

2009-02-27T11:51:00.001-08:00

I'd forgotten about GIMP. No, not the one in the movie, the image manipulation one. Needed to touch up an image for SQL Developer with the correct copyright information and it just works.

Take the Splash Screen for SQL Developer. Rewrite the copyright and publish. This apps a keeper. :)

Prague Develop Event.

2009-02-11T05:13:00.001-08:00

Well, its not as cold as it was in Moscow, but the welcome was just as warm.
We're here in Prague delivering the Oracle Develop event.

I'm here talking about our new modelling tool. Oracle SQL Developer Data Modelling which is available as an early adopter release on OTN.

Oracle SQL Developer Data Modelling is the latest product offering to join our Oracle Database Tools. SQL Developer Data Modelling has Entity Relationship modeling, Relational (Database Design), Data Type and Multidimensional modeling, full forward and reverse engineering and code generation. It includes importing from and exporting to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of Design Rules.

All in all, fills in one of last pieces of the database development cycle allowing us to model our databases before we go ahead and physically lay them out and create them. My personal favourite is the multidimensional modelling features.

Years ago, I was involved in a huge implementation of a warehouse which was all defined by hand; the fact tables, the dimensions and all the aggregates. This was fine at the time, but being able to go the database and extract the structure of it and show it as a model would have been great. Have a look at the image and the way the fact, dimension and aggregate objects are laid out.

Anyway, the talk today was great with loads of questions around how we'd handle Designer, Erwin and other formats, and why we just didn't use Rational or JDeveloper. There are a lot of answers for this in the FAQ.

For now, we have a couple more hands on labs where the attendees can get their hands on the software and ask questions about what they are doing.

Download it and try it and if you have questions, ping the forums and we'll help you get going.

Moscow Develop Event

2009-01-27T09:10:00.001-08:00

After several days of visa processing, I'm clear to go to Moscow. Spasiba Mockba!
I'm speaking on Wednesday 4th on our new data modeling tools. I'm hoping to go through a presentation of the features by demo on the fourth starting with a reverse engineering of a couple of schema to a relational model and abstracting that into a logical model. I want to show several views and slices of the model.
I also want to show a load of the property management capabilities such as naming conventions to constrain object names.
From there, we can push down and create a physical model, containing the attributes of that model, such as tablespaces, indices and multi dimensional objects.
Lastly, We'll complete the circle and do some DDL Generation.
On Thursday, I have set up two hands-on labs to allow attendees to work through a prescribed text showing much of the same features I'll discuss in the talk.
Technorati Tags: Oracle Develop, SQL Developer, Modeling

Back on the Chain Gang

2009-01-06T01:33:00.000-08:00

After a great holiday season, its time to get back to the grindstone. We released SQL Developer 1.5.3 before Christmas which is our first translated release. Its just japanese right now and we'll be extending that to nine languages in 1.5.4.
The other major part of 1.5.4 I want to sweep up are as many issues as possible from our customers on the forums. Over the next few weeks, we'll be focusing on locking down as many of these as possible.
In terms of conferences, I'm off to Moscow in February as part of the series of Oracle Develop conferences. I'll be doing a couple of sessions on SQL Developer and hope to get under the covers to show exactly how you can build your own additions to it. I was watching Sue present at UKOUG and it was the one thing that struck me that people were looking for as heads were bobbing around the room.

Winter is here

2008-11-08T04:09:00.001-08:00

iPhone blogging

2008-11-08T04:01:00.001-08:00

While taking a break from 2.0 the iPhone has this cool application called shuzu which lets you blog from the phone.

Ants in my pants

2008-09-06T14:10:00.000-07:00

Well, just spent the day playing with Ant adding some beanshell scripts and some other bits and pieces.

Here in Oracle, we have the ability to translate to 32 languages pretty quickly given we give our translation teams the appropriate file formats. In SQL Developer, we primarily use three file formats.

property files
xliff files ( to denote strings in xml)
rts files, home grown strings files we process to java files for inclusion.

Anyway, the upshot of all of this is we are currently going through the pain of translating SQLDeveloper because we are present in close to 100 countries around the globe, even antartica. (Yes guys, we saw you). Translating a tool is an interesting business, both from an operational point of view (how to do it) and when to do it (when in the lifecycle). We'll come to that over time as we solidify this.

Anyway, We've decided that we will drop our first translated delivery with our JDeveloper integration. They normally translate to Japanese, but allows us to work out all the kinks as we go. This will be called our 1.5.2 release and will only be released as part of JDeveloper. As soon as we have that completed, We'll put together 1.5.3 which is a translation of the same tool into 9 languages (including yours hopefully)

Multi Tasking - Men can't do it.

2008-09-02T06:34:00.000-07:00

But SQL Developer can. We've spent some time reworking the worksheet for 2.0 to allow several things to happen.

Allow you to register pre and post statement listeners so you can do things with the code before and after it is run.
Added a tasking framework which handles background tasks to allow the users more immediate control, response and information as they use the tool.

The listeners are being integrated into the worksheet and the tasking framework is being used in several places already for 2.0. The navigator is making use of it as will most things which take more than a few seconds to complete.
On the bottom of the tool, we have a 'knight Rider' bar showing the task running.
We house these tasks in another dockable window which shows the tasks and allows the user to configure and manage them through out their life before they disappear when the task is complete and the threaded task they represent is gone.All of these things, we'll be showing at Open World from the 15th of September so if you're going drop along to a session on base SQL Developer stuff or some of the other Migration or modeling sessions. You can see whats going on here.

DBMS_OUTPUT from multiple worksheets

2008-09-02T06:28:00.000-07:00

While I'm talking today, heres another feature people have been asking for. Separated DBMS_OUTPUT from the main worksheet.

This way you can now have several worksheets on the same connection pumping out anything onto dbms_output and it appears nicely in the dockable window. You can still specify it inline in the code with
set serveroutput on
and it will appear in the script output window as an alternative.

Do you have odbc or JDBC data?

2008-09-02T03:07:00.000-07:00

We do. Most of the emails and pings I get are to see if we have a plugin for the migration workbench for this or that, mostly databases we rarely hear from anymore like the paradox's supra's etc

At Openworld, we're introducing a generic JDBC and ODBC browser and migration extension to allow you to connect to 'any' jdbc source or odbc source and copy your data across to Oracle.

Right now, we've tried it on postgres and firebird, but there are a lot of others to test too. This will be GA in the 2.0 timeframe of SQL Developer.

These connections allow you to view your data in the source database and you can drag your tables into the worksheet kicking off a table migration to that connection.

Come by and see us at OpenWorld. I'm doing a session on the Monday about Migration in general and have a couple of customers who have been through the process using SQL Developer and have come to tell their tale to you.

Oracle World and UKOUG

2008-05-27T07:53:00.000-07:00

One other thing is we're revving up for Oracle world and UKOUG too, so make sure when you get there that you look us up. Dont have specific locations, but we're normally in the database campgrounds.

1.5.1 on the horizon

2008-05-27T07:46:00.000-07:00

Since we released 1.5, we've been busy getting a few issues sorted out for 1.5.1. Most of these issues, you wont see, but when you do, they are painful. Here's a working list of what we're up to:

Tightening up NLS/MB issues.
Hardening some connection issues (to do with internal dependencies)
Fixing some migration related issues for our customers to bring their migration projects to close to 100% successful generation.
Making the Check for updates better so we can release patches more easily on this platform.

There are a load of other minor things in here, but these are the big ones We want to get out before we get really stuck into the next major release. Make sure you check out our site and if you have features you want to see go to the feature request pages

SQL Developer 1.5 is now Production

2008-04-24T01:59:00.000-07:00

We have just released SQL Developer as production yesterday. Its been a long road for getting the functionality into this release and making the whole tool more stable and more usable than any other release.

Over the next couple of months, we will be building some other cool stuff which we hope to showcase at Openworld.

DBDoc your schema

2008-04-02T11:32:00.000-07:00

Well, we've been working on EA3 and its been worth it. A sneak peek at this nice feature
You can now right click on a connection and generate a set of DB doc, not unlike Java Doc. This missed the cut for EA2 but will be in for EA3

We will probably make this a little prettier before production but this gives you the general idea.

SQL Developer 1.5 Early Access.

2008-02-04T14:52:00.001-08:00

Well, we now have early access 1 out the door. There are a couple of things that have not made it in yet, like SCM and a few others, but we are releasing updates every few weeks as we harden the product down. We've done a load of updates, specifically with user requests from the exchange.
I'll keep this updated as we release each cycle and focus on specific changes to each of the specific areas.

Oracle Open World

2007-11-19T09:44:00.001-08:00

Well, here's another successful Openworld conference. We had several
sessions on SQL Developer from the Hands on session, Sue Harper and I
did on Monday, through to the Migration session I did on Wednesday,
detailing how to consolidate your database infrastructure under an
Oracle Database.

Sue and Kris also did papers on SQL Developer. The first was an
introductory paper and the second was an advanced paper.

For me, meeting our customers is great and really makes you realize how
much you take for granted as you live in the space all the time. I
think over the next few posts, I want to try and get more information
out about extensions, Migration Plugins, doing a migration and many more.

For now, though, this is a wrap. The next post will be technical, but I
haven't decided on what yet!!

New Oracle Wiki

2007-10-24T08:55:00.000-07:00

There is now a new "Official" Oracle wiki out there. This is pretty neat as it lets Oracle employees and everyone else to collaborate together. This will be cool from my point of view since we deal with SQL Developer and the Oracle Migration Workbench.

The point of all this is that normally the most interaction we have is either through the forum (SQL Developer and Migration Workbench) or through Support.

Now we have the ability to add information about product and drop in loads of ideas, solutions and best practices from everyone.

Forum posts and questions

2007-10-17T13:47:00.000-07:00

One of the things I do quite a lot of is monitor the forums (fora?) for both SQL Developer and the Migration Workbench. Some of the features we're putting together should appear here in different forms as I get up and running with this a bit more. :)

Editing data in big tables

2007-10-17T13:22:00.000-07:00

I saw this floating around the forums over the last few days and it's one of those things that are more of an annoyance than anything else. Consider having a table with 100 columns that you want to edit a row of data for. Well, now you can do it in the single record view which has been remodeled to size properly for any table up to the oracle limit.

This example shows a table of about 100 odd columns ( I got tired adding them thru the table create dialog or we would have more)

Now We're Blogging

2007-10-17T13:16:00.000-07:00

Here we are now. Bloggin.
I've been able to stay buried in code for long enough and now I have to publish. Well, I am the development manager for SQL Developer and I hope some of the stuff we post here should be useful over time.
B