Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions within a SQL statement was relatively easy.

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(event_no)
FROM   events
/

However a slight adjustment is required for DML. The documentation suggests that

• If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint.

but does not give any examples, which I think is unfortunate - but thanks Tim for getting us started ;-)

UPDATE /*+ WITH_PLSQL */ events e
SET e.org_id =
  (WITH
     FUNCTION inline_fn(p_id IN NUMBER) RETURN NUMBER IS
     BEGIN
       RETURN p_id;
     END;
   SELECT inline_fn(e.org_id)
   FROM   dual);
/

Without the hint Oracle returns

ORA-32034: unsupported use of WITH clause

but I was getting

ORA-00933: SQL command not properly ended

What clued me in was the brief highlight SQL Developer makes over the statement before it executes. For me this paused at the return statement within the function.

I happened to be using one of the pre-built Oracle Developer VMs to play around, and it turns out the one I'm using has SQL Developer 4.0.0.13 supplied.

That particular version doesn't seem to be aware of this bleeding edge feature. I vaguely recall seeing this mentioned somewhere probably in the vicinity of thatJeffSmith fellow. I tried it in the command line SQL*Plus and it worked fine against the 12.1.0.1 instance.

It does ring a clear bell for once upon a time circa 2006 working on Oracle 9i or 10g when I sent an email to a colleague containing a SQL statement including a WITH clause.

He didn't have success in his Oracle 8i SQL*Plus windows client either... oh how I miss thee.

Returning BLOB file size

Occasionally I'll want some form of report noting file sizes of blobs in a database.

The solution is relatively simple, and I thought I'd write it up here for a place to copy syntax each time.

APEX users also have a handy table to verify this against (apex_application_files). Well, a synonym/view that ultimately maps to the core table wwv_flow_file_objects$.

It contains a doc_size column, which is no doubt evaluated at some point during upload of the file. For blobs in your own table you can use the supplied package DBMS_LOB to get the length of the file in bytes.

Multiply that by 3 orders of magnitude and you have it roughly in kilobytes - a digestable figure for most files dealt with these days (calc_size).
Add some pretty formatting and you have how Windows Explorer shows file sizes (calc_fmt).

Inside an APEX report you might consider the format mask "999G999G999G999G990" on 'calc_size' - without any trailing suffix so you can still order the results.

select id, filename
  ,doc_size
  ,round(dbms_lob.getlength(blob_content)/1000,2) calc_size 
  ,to_char(dbms_lob.getlength(blob_content)/1000,'999,999')||' kb' calc_fmt
  ,apex_util.filesize_mask(dbms_lob.getlength(blob_content)) size_mask
from apex_application_files
order by doc_size

/* UPDATE 2014-07-11 */
Carsten (@czarski) pointed out on twitter that an API that's available that returns a rounded off relative size with the appropriate trailing KB/MB/GB/TB.

It's not shown in the current 4.2 documentation - but I think it's probably just been missed rather than it not being supported. The APEX_UTIL package is pretty large should probably be broken up so little nuggets like this don't get missed.

He also mentioned a "FILESIZE" format mask, which isn't shown in the format popup (you just type it in) and also behaves differently to the API. Values under 1MB returned just numbers to 3 decimal places, and a 10MB file was labeled as 10KB - this was on 4.2.0.0

The only downside to the API formatting is as per calc_fmt, the ordering is impaired - by ordering on this column you will get results like:
1KB
1MB
2KB
***/

So in my sample results run in APEX SQL Workshop I can see tiny files to progressively larger files - up to an APEX application export of about 1.6 mb & a PDF over 3mb.

Filenames redacted to protect the guilty

Note, this view can't be queried from SQL Developer (or your tool of choice) since the current workspace is part of the view. Martin has an interesting post to get around this sort of thing by defining an APEX session using PL/SQL.

Scott

3 product releases from Oracle Dec 2013

So far this December Oracle has released version upgrades for three of their products:

1. Joel Kallman announced Oracle APEX 4.2.4 is now available. It appears to be mainly fixes & enhancements to Package Applications, so maybe it's time to give them all a go & see if they may be useful to you. [bugs fixed]
   
   I'd also like to revisit a few and see if templates have been made out of some of their cooler dynamic PL/SQL regions. I've reverse engineered their hover functionality using a template & a list, but it would be nice to have these available in themes.
   
   Bring on APEX5
   
2. Jeff Smith gave use 10 reasons to upgrade to SQL Developer 4.0. I haven't used it much so far, but it seems many of the hiccups from the early adopter downloads have been addressed - plus a gazillion new features.
   
3. At the start of the month Jeff also announced Oracle SQL Developer Data Modeler 4.0 is available.

Do good things come in threes? No, they do not, but in this case we have a correlated co-incidence that we should certainly take advantage of ;-)

Get SQL Developer 4 going in 4 steps

After a little hiccup on my part, I've started using SQL Developer 4 Early Adopter 1 on two computers I work on. It's pretty easy:

1. Download SQL Developer 4 and it’s required JDK from:
   http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-download-v4-1925679.html
    
2. Unpack this to a folder on your system
   sqldeveloper-4.0.0.12.27-no-jre.zip
   I used
   c:\sqldeveloper4EA\ 
3. Install the latest JDK,  probably not accepting the new toolbar for your browser...
   jdk-7u25-windows-i586.exe
   
4. Open by running - I tend to copy a shortcut on my quick-launch bar
   sqldeveloper.exeWhen prompted for location of java.exe the first time you open, I used
   C:\Program Files\Java\jdk1.7.0_25
   I also allow it to copy my existing preferences across.

Job done, let's see what interesting features I encounter. 

The slightly flashier icon

If you want to actively learn more, you must visit www.thatjeffsmith.com

Scott

Ubuntu Milestones - Oracle

For many, many years I've yearned to move away from windows. Everyone has their reasons, some have motivation, I finally had opportunity to make some baby steps.

I needed to set up an environment using APEX 3.2, and I had an old XP laptop that I finally moved my ipod synch off so I figured it was free to experiment with. I was already running it with dual boot, but I never pulled my finger out to restart & play around with the Ubuntu half.

Earlier in the year I experimented a little with Ubuntu using VirtualBox on my existing Windows7 laptop - I've even drafted a blog post rambling about my experience, but I've got some fresh thoughts with my recent jump in the deep end.

First impressions - it's an OS worth me pursuing.

It's certainly a cultural change. Of course, you need to be able to do many of the same type of operations - move files, close windows, setup Oracle... but to use a balinese catch phrase - it's same same, but different.

Installing Oracle

I was a little bit of a cowboy with this - while I was keeping note of the websites that I was using as a reference, I didn't take note of each command I performed. This was going to be a temporary db, and I figured this first attempt justified a little gun-ho action.

Besides, my ultimate plan is to use Virtualbox to run different environments in their own confined buckets. This old laptop is destined to be a sandpit.

I used this Linoob address to help install the db. In the end I'm pretty sure the apt-get just opened up the software centre.

Oracle in Ubuntu Software Centre

I think it's a selling point for this particular distribution of *nix based systems, installing software is just like searching Google Play or the Apple Store - they aren't the scary domain of sysadmins anymore.

Setting up SQL Developer was a little more tricky since there is no download available specifically for Ubuntu. Instead, there is a package builder for .deb packages, so I still just needed to download the base file, run a few commands and it could be installed no problem.

Installing a JDK to support SQL Developer was a little more interesting. I used the manual option, which again is just a list of commands. I'm positive I haven't put the JDK folder in a 'typical' location, but that's the burn of learning.

Having to run commands like this in a way reminds me of the days of DOS, which you could take as no really progressing from the Windows environment of just clicking "Next" - but I don't see this as a con. I see it as liberation, a way to gain flexibility. Some of the commands seem foreign now, but I don't think it's far away from recognisable, then second nature.

All that being said, you could look at setting up SQL Developer in Ubuntu on par with setting it up in Windows. In the latter, I still need do download a set of files, extract, and on first run point it to the java.exe that I always need a few attempts with getting the path just right. In Ubuntu, I surprisingly got this path first go!

The only oops I did was download the latest and greatest JDK (7), which SQL Developer is not ready to support, though it ran anyway. I'll fix that later...

The closest I got to the OS was when I had to add my user account to the DBA group. I needed to do this before the database fired up. After an initial scare of making it all this way and not being sure why it wouldn't start - I found a pretty clear instruction on the OTN forum.

With all this gun slinging, I forgot to configure the DB.
/etc/init.d/oracle-xe configure

Configuring APEX

From here it was familiar territory in upgrading APEX from v2 to my target of v3.2.1

These commands I could run from SQL Developer
Upgrade
@apexins SYSAUX SYSAUX TEMP /i/

Load images to EPG (I wasn't game to attempt APEX Listener just yet)
@apxldimg.sql /home/scott

Unlock the public user account (I was doing this mostly from memory, after setting up APEX so often, I even remembered this one after my attempt to log in just stalled in the browser)
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

The only script that didn't want to run from SQL Developer was the one to change the ADMIN password (apxchpwd.sql)
Instead, I just pasted the code in the SQL worksheet and ran it from there.

Restarting the OS

After restarting, the database didn't restart (an option I chose), but the scripts in the menu did not work. Again, I think this just came down to me lacking understanding of some permission issues, and I found a command that worked for me.
sudo /etc/init.d/oracle-xe restart

Conclusion

I got up and running in a few distracted hours. While there was a few hurdles, there was never anything that really concerned me or had me stuck for too long. Google was my dear friend, but I'll keep perusing my Beginning Ubuntu Linux book.

Next I'll be looking for alternatives to some programs I regularly use in Windows.

Managing APEX using APIs

You can still manage much of your APEX workspace from the command line - whether that be SQL*Plus, SQL Developer, or some other tool of choice.

They key thing is, not everything needs to be point and click - that feels a little important.

I like to frequently blow away & re-create a bunch of users, workspaces & applications in one foul swoop. I'd like to share a little of what I've done since it was a topic of discussion recently at the Perth APEXposed.

To use the APIs below, your schema will require the APEX_ADMINISTRATOR_ROLE, described by Martin here.

To create & define all the training accounts, I use the following procedure in a package I compile in my parsing schema.
Privileges are a little different when encapsulated in a package, so direct EXECUTE access on APEX_INSTANCE_ADMIN would be required to avoid the infamous PLS-00201. Permissions such as CREATE USER would also be required.

PROCEDURE define_users
  (p_nbr_users  NUMBER)
IS
  lc_user  VARCHAR2(10);
BEGIN
  << define_users >>
  FOR i IN 1..p_nbr_users LOOP

    lc_user := 'train'||i;

    << drop_user >>
    DECLARE
      e_no_user exception;
      pragma exception_init(e_no_user, -1918);
    BEGIN
      EXECUTE IMMEDIATE 'DROP USER '||lc_user||' CASCADE';
    EXCEPTION WHEN e_no_user THEN
      NULL;
    END drop_user;
    EXECUTE IMMEDIATE 'CREATE USER '||lc_user||' IDENTIFIED BY '||lc_user;

    EXECUTE IMMEDIATE 'ALTER USER '||lc_user||' QUOTA 100M ON users';
    EXECUTE IMMEDIATE 'GRANT CONNECT TO '||lc_user ;

    EXECUTE IMMEDIATE 'GRANT CREATE SYNONYM TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TYPE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TRIGGER TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO '||lc_user;

  END LOOP define_users;
END define_users;

All my other procedures just encapsulate the following steps and example API calls. I highly recommend specifying formal parameter names as the signature to these APIs often between APEX versions.

My procedure to define my workspace makes calls

APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE(lc_user,'N','N');

Create the workspace

APEX_INSTANCE_ADMIN.ADD_WORKSPACE
  (p_workspace_id   => ln_workspace_id
  ,p_workspace      => lc_user
  ,p_primary_schema => lc_user
  ,p_additional_schemas => '')

A new call required for 4.1.1, found by Dimitri, announced by Patrick

apex_instance_admin.enable_workspace;

Set the context regarding which workspace

apex_util.set_security_group_id(p_security_group_id => ln_workspace_id);

Create a number of users, with various developer privileges.

APEX_UTIL.CREATE_USER(
        p_user_name                     => 'ADMIN'
       ,p_web_password                  => /* my attempt to keep generic accounts passwords unmentionable */
       ,p_email_address                 => 'username@sample.com.au'
       ,p_developer_privs               => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL'
       ,p_default_schema                => lc_user
       ,p_allow_access_to_schemas       => lc_user
       ,p_change_password_on_first_use  => 'N');

Then before installing an application, you need to set the workpace and schema

APEX_APPLICATION_INSTALL.SET_WORKSPACE_ID(p_workspace_id);
APEX_APPLICATION_INSTALL.SET_SCHEMA(p_schema);

Then you can either nominate the application id or have one generated

APEX_APPLICATION_INSTALL.SET_APPLICATION_ID(p_app_id);
-- or
APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;

You may also choose to set such properties as application name and alias

APEX_APPLICATION_INSTALL.SET_APPLICATION_NAME (p_app_name);
APEX_APPLICATION_INSTALL.SET_APPLICATION_ALIAS(p_app_alias);

Then you generate an offset for all the IDs in the export script

APEX_APPLICATION_INSTALL.GENERATE_OFFSET;

If you're creating multiple applications for one workspace, all you need each time is

APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;
APEX_APPLICATION_INSTALL.GENERATE_OFFSET;

To execute these APIs among calls to import previously exported applications in SQL*Plus (wasn't that a mouthful), I used

set define '^'
accept my_app DEFAULT 'C:\my_everything\f_123_example.sql'

-- set app context
exec apex_application_install.generate_application_id;
exec apex_application_install.generate_offset;
-- install app from export
@^my_app
-- rinse and repeat

And that's essentially how I do it, just bundled up to suit my needs. It'll be pretty easy for you to grab what you need and sort out your own scripts.

Scott

SQL Developer 3.2 prepares to connect to the cloud

Have you downloaded SQL Developer 3.2 yet? check this out:

I guess Oracle Open World will have a few announcements this year...

I'm not going, but I find solace in that I'll be at the AFL grand final this year - which is the day before.
At least I know Pearl Jam is eliminated from the list of acts for the grand final - still to be announcement.

How long before someone jokes about connecting to "heaven" as a cloud connection? ;-)

SQL Developer Preference Favourites

Inspired by Jeff Smith's post on SQL Developer preferences, I thought I'd share what I usually tweak when I sit down on a new box

Line Gutter

Personally, I think seeing the line number is vital. Yeah, I can see it in the status bar, but for the sake of a few pixels in width, it's so convenient to have it in the worksheet canvas.

Do note, however, that certain error messages may not line up with your line number as some line number reports will come from the database, for instance when you're compiling PL/SQL modules. You may already have other content above your little ad-hoc procedure, or if you're compiling a trigger no doubt you'll have some carriage returns in the first line - it's always hard to tell where triggers start counting line one!

Date format

When I train people how to use SQL, sometimes I get a little carried away with dates. I think they're fascinating, and they're also the cause of many logical errors when it comes to reporting, as many people forget or don't see there is a time component to their date column.

These settings directly affect your session level NLS settings.

Completion Insight

Some people love this sort of thing - I don't. I hate lag, and this can introduce it. Sometimes I'll dial the delay right down, perhaps when I'm working in an unfamiliar database, but most of the time I turn it off.

The checkbox options underneath also pertain to certain tastes, the change case as you type can be nifty but again, the other options can be annoying.

Syntax Colours

This is fairly unimportant, but I'm used to green, so I make my comments screen.

The options are there, go nuts.

Shortcut Keys

I'm a huge fan of keyboard shortcuts - I try to limit the usage of my mouse - it gets enough of a workout when surfing the web, and I'm sure it's use doesn't help my posture.

You can find some cool features just browsing through the list, or you can adjust common commands to suit your habits. Just watch you don't create a conflict between existing shortcuts.

I often change the commands to set UPPER/lower; ensure F8 doesn't launch SQL history - that really grinds my gears.

"Advanced" Settings

Nothing mind-bending here, but I reckon the default of 50 rows for the first set of rows isn't enough. I bump it up to about 150. Just because there might be a zillion rows in the table, the makers of SQL Developer thought it would be prudent to return the first bunch for you.

To find out how many rows in your complete result set you can hit Ctrl+A; press Ctrl+Down, or right-click in your result set and select Count Rows for a snappier answer.

If there was a really subtle yellow selection for the background colour of nulls, I'd use that (as per PL/SQL Developer, or is that a naughty word? Perhaps not as naughty as Toad). Perhaps a colour picker à la Syntax Colours would be nice.

Do not check auto-commit.

SQL Formatting

I don't do much PL/SQL development in SQL Developer (Sorry Jeff, Kris, TextPad wins) but for those that do, this is a flexible feature. I'll mention PL/SQL Developer again - I think that had a formatter that perfectly matched my style (I had a big say in the coding standards where I used that ;-), but you do have to pay for that product - SQL Developer is free!

I tend to write my code directly into the format required, because I am a little pedantic that way, and TextPad makes it easy for me.

Worksheet Options

Two points to note here, SQL History limit - some people might like to bump that up a little - doesn't cost much HD space... Don't forget, if all else fails, you can try v$sqlarea

The other is the "default path to look for scripts". I wasn't aware of that option until I read the comments on Jeff's post - but it seems my script catalog was available even when this field was empty.

Who am I but to question why? If it ain't broke...

Now all the team needs to do is get the footprint down and I'll be happy. It grows over time, I restart occasionally to make my laptop happy. It doesn't have quite the zing of a non-JRE application... rant over.

A final word - if you're still on SQL Developer 1.2 - upgrade, to at least 2.x. Latest versions = more stable + less bugs + more features.

Edit June 2018 
The above statement regarding version upgrades still applies, it's amazing how many out there using old SQL Dev versions.

Code Editor - "Use change of case as word boundary" - off, so underscores are not counted when skipping words with ctrl-arrow

Anonymous PL/SQL blocks

Some of you may be familiar with the ability to administer Apex applications via SQL Developer, for instance I could modify the alias of my application thusly:

It opens a popup which allows me to write my new alias.
As with the other facilities of SQL Developer, you can opt to view the relevant SQL for this change.

All Oracle is really doing here is calling the relevant API, but what I also noticed the first time I used it was the autonomous transaction pragma it applied to the anonymous block.

declare
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  wwv_flow_api.set_security_group_id(p_security_group_id=>100001);
  wwv_flow_api.set_application_alias (p_flow_id=>101,p_alias=>'TIM_0101_b');
  commit;
end;
/

In the past I've quite happily applied this pragma for a procedure defined within a package, but this demonstrates we can also define independent transactions within these anonymous PL/SQL blocks, in addition to certain triggers.

Something I added to the memory bank when I first stumbled on it.

A final note - you can also kinda "name" your anonymous block to assist your documentation process, ie - there is no reason why you can't finish with:
END my_anonymous_block;

ScottWe

SQL Developer 3.0 is out there

One of the first Oracle related news I noticed this morning was SQL Developer 3.0 has just been released.

Make sure you check out the release notes, while I've downloaded it I think I won't have time to take it out of the box for a little while - so I'll have to be content with the manual :-(

Congratulations to the development team for reaching this significant milestone.

Another freebie from Oracle

On the back of my recent discussions on SQL Developer, at the start of the week Mike Hichwa announced on his blog that SQL Developer Data Modeler is now free!

This announcement was also confirmed by Sue Harper on OTN.

How there is no excuse not to model your applications :-)

More information on this product is available here.

note - us Aussies and probably a whole bunch of other countries spell it "Modeller" - good thing Google Search doesn't care.

SQL Developer gripes - Part II

Last week I commented on some issues I have with SQL Developer, from which a number of people commented and e-mailed me about.

Stew in particular commented on his usage of PL/SQL Developer (aka PSD) and asked the question:

  why would you move away from using PL/SQL Developer?

So I thought it would be polite to respond with a fresh post.

I think the best way for me to respond to that question is to highlight the most pertinent part of that statement - "move away"

To add some more context:

• I learned Oracle using SQL*Plus. I like using it, I still do for some tasks.
• I started using PSD when we (myself and the fellow that sent the original e-mail that spurred this discussion) pushed the company we were working for to supply a GUI tool. We evaluated a few, and PSD fitted the bill. It served me well, and the company managed the licences.
• In mid 2005 I started with another company doing mainly Oracle Forms programming - so I was essentially forced to stop using PSD as I no longer had a licence, and I didn't really need a GUI at the new job.
• When SQL Developer was first released, I did download and use it occasionally, but since I learned Oracle using SQL*Plus and a fancy text editor, I managed to retain productivity. I have a suit of scripts that displays the same information I get out of the GUI tool and have them ready to fire in a nice output in SQL*Plus.
• Since joining Sage Computing Services, I've relied on SQL Developer even more-so for some positives I'll explain later.

So in essence I was forced to move away from PL/SQL developer mainly because of job changes. Had I still been working there, I may still be happily using it - unless of course they've done the cheap and gone to SQL Developer. (nope, I asked the question - they still use PSD)

If I had use of a PSD licence, I don't see any real reason why you'd purposely shift to SQL Developer, unless you are a real Oracle love-bird and want to use only their products.

However, for the position I currently find myself in, SQL Developer is great  (over others) because:

1. It's free - Licensing for PL/SQL Developer starts at US$180+60. I had trouble finding an exact figure for Toad, but "six times the price" seems like a common phrase.
2. Easy to "install" - if I find myself on a client's PC, I can drop the SQL Developer folder in with no admin rights, open it up and use it. There are other similar advantages your typical consultant might cite.
3. Forget TNSNAMES.ora - that little file has bugged me for a long time. Not having to worry about it has made my time easier and I can connect to any database I have the host, port & SID for - without having to worry about multiple copies of tnsnames.
4. It's evolving - Oracle haven't dropped a grenade and ran - they are continually updating, refining & extending SQL Developer. Good things come to those who wait. In fact, I heard a little whisper that improvements to the JRE will help improve the saskatchewan footprint over time. 
5. Did I mention it's free?

As for combining it's use with SQL*Plus

1. it's thin - half a second and you have a fresh connection to the DB
2. preparation is key - I have a login script that suits me nicely (for Windows versions prior to 11g) and a bunch of scripts that I can fire on demand to get the same info the GUI provides:
   SQL> @constraints my_table
   I even have scripts called "source" and "pksource" which quickly give me a nice view of compiled objects.
3. it works - without fail, every time.  

Obviously this is all my opinion subject to my personal working scenario. 

If anyone's interested, there are detailed reviews to other software tools for Developers and DBAs here at OraFAQ. There is a more succinct and colloquial comparison written at OTN here. And here is an old but interesting & relevant article (2007).

Oh, and Kris - if you're still listening - handling serveroutput was ok a few versions ago, but very frustrating in 2.x

It would be simply super if output to calls to dbms_output.put_line() became more predictable and was controlled by an icon/shortcut key/menu item. :-)

SQL Developer gripes

When Project Raptor finally came to fruition as a download in mid 2007 as SQL Developer 1.2, I was excited at the prospect of having a free GUI that I could take with me to various clients, without worrying about licensing.

I was using PL/SQL Developer at the time which was great for the PL/SQL development I was doing. I still use SQL*Plus fairly regularly as a lightweight tool, put as OraFAQ suggests, SQL Developer was intended as a complimentary tool.

I've heard a few comments recently saying how flakey and unreliable SQL Developer is. After further questioning, it appears they are referring to the 1.2 version which I will admit, was obviously an entry-level product. v1.5 was the better stable release before 2.x came out.

I'm currently using the most recent patched release (2.1.1.64), and I'm fairly happy although I still have a few gripes.

What made me think of this was an e-mail from a colleague now working in the UK, here are my extended responses:

I started a new contract this week and have been trying to use SQL Developer again, but it doesn't always do things I expect. They are using some cross database tool called DBArtisan (Sybase background) and I'm not impressed with it. 

It's always good to get feedback on tools from other databases - interesting.

So far there are 2 things I haven't worked out. The first is multi-session/threads, is this possible?

Most of the time I work with one connection for each schema/database that I happen to be working on. This means that any DML is reflected in all views of that information - on the worksheet, the table data view etc.

If you define a new named connection for the same schema, then you have started a separate session that will not see your uncommitted DML.

The second is that there code base is not exactly formatted very nice so I wanted to try auto-format it. I end up with line breaks where i don't expect, not enough white space and it doesn't seem to allow you to set the case on anything other than keywords, rather frustrating.

I've been thinking about this, and I think it just boils down to personal style. An evolving formatting tool couldn't possibly cater for everybody, and I know our styles are similar. I remember the formatter for PL/SQL Developer & Toad being very effective - but they're more mature products. That being said, I don't really use auto-formatters. In fact, I still do most of my PL/SQL development using TextPad. I use SQL Developer mainly for ad hoc SQL queries and looking at table definitions.

Any advice you can give me on how I might achieve this in SQL Developer. I will admit that what it does with statements when you are grouping by adding to the group by clause is pretty cool, it surprised me today when it appeared in something I was writing

This is what I told him about that one: As for completion insight - I have all those options turned off, first preferences I change on a new pc, that and line numbers in the gutter & NLS date parameter to include century.

There are a couple of other comments I let fly in my reply, things I felt compelled to address.

Compiling PL/SQL

Error reporting is a real pain in the behind. If you're just tweaking things, yeah, it works - but iterative development, I still compile my PL/SQL in SQL*Plus - my edits direct from Textpad. The error reporting is much clearer.
Unless I'm using it wrong, I don't find compiling code in SQL Developer helpful at all.

It's JRE based

It just makes things slower, introduces lag which grinds my gears. Now I may be pinning the tail on the wrong donkey, but all the other non JRE products I use don't have these issues. That, and it consistently leaves a 500mb footprint in my laptop's memory. However I know this will never change - tweaks in its guts may improve efficiency, but it will always be JRE based. I will say a clear advantage here is you don't need to "install" it, copy the files onto your OS and you're ready to go. Super!

Best I conclude my rant for now. Did I miss anything?

ScottWe

ps - commendations to Sue Harper, Kris Rice and the rest of the SQL Developer team - the product has come a long way in the past half-decade. People always have something to complain about :-)

Unexpectedly learning something new...

Today I was exploring the new SQL Developer in regard to managing Application Express.

There is an option for example to change the alias for an application. As usual there is the facility to see the SQL so I thought why not have a look what was going on.

I was presented with a useful looking anonymous block I never really thought about before.

I thought I'd try it out:

create table a ( a number);

insert into a values (1);

declare
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert into a values (2);

  commit;
end;
/

rollback;

select * from a;

Not long after I found another potential use, I was doing some work with triggers and I was trying to decide on the method to create a new record on the same table that fired a trigger, but avoid the mutating table issue - perhaps this could do the trick without the need for a procedure defined with the pragma? Alas:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here

How about that? I like learning new things, and I happened to find something about PL/SQL while researching SQL Developer managing Application Express!

SQL Developer 2.1 EAP

The latest major release of SQL Developer is now in the Early Adopter stage, starting at 2.1 (not to be confused with the much older & somewhat flaky 1.2 version).

I gave it a go and here are some quick observations:

1. Unzipped footprint including JRE from 166mb to 200mb (Update: I've just noticed the memory usage on my laptop has almost doubled to 237mb!)
   
2. On opening I still have to tell it where java.exe is, not the nicest thing to have to do for those more basic users.
3. Snappier to open, even considering I was migrating preferences from 1.5.4
   
4. All icons are sharper and clearer
5. Jobs included in navigator list
6. Free data model viewer, which makes sense to me and seems fair.
7. I can compile packages/procedures using F9 in worksheet - yay!
8. ... and I get syntax errors more appropriately.
9. Although saying that when I open the program from the navigator, I get erroneous errors on valid compile.
10. Syntax errors for queries aren't in popups, just in the results window. The number of rows fetched and timing just above results too, which is more friendly to the eye.
11. I like the fact the tabs above the result set can be closed if you don't want them.
12. I still can't figure out how to open to table views at the same time though.
13. There is a unit testing component that will be interesting to explore, giving the nice people at Quest some more competition in the testing market.
14. I like the preferences changes. One of which includes more options for SQL formatting, including a preview.
15. The coding templates are also a good inclusion. Let's ensure good standards are more widely adopted!
16. It didn't auto prompt me for a connection when I opened a new SQL file and attempted to execute.
17. F6 for explain plan has moved to F10
18. ...best I continue with work now and see how it performs with day-to-day tasks.
    

Official new feature list can be found here.

Some people think all it needs to do is run SQL and PL/SQL, but considering it's a GUI tool we want it to be brisk and easily customisable - otherwise we'll stick with SQL*Plus. Overall, I like the new improvements. I'm sure the kinks will be ironed out quickly and I look forward to the ongoing improvements to a good free product.

Tip o' the hat to Dimitri Gielis for the tip-off.