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 :-)

APEX IR CSS tweaks

Recently I had a request to top align all the data within an APEX 3.2 interactive report.

There were some data in the report that made other information appear out of place. Consider the sample data below on demo_customers table. The address field is subtly pushing the other fields down. Combine this with an Edit record image and it starts to look silly.

Normally you can hop into the templates of your application and throw in the occasional tweak to make these adjustments, but I found nothing for an IR.

After a quick search, I found the solution here by Matt Nolan - who ironically enough commented on my blog recently.

My task wasn't as in depth, so I simply used the vertical align style and inserted it in my application's custom CSS file. Alternatively, I could have inserted it into my page footer, for instance.

<style type="text/css"> 
  table.apexir_WORKSHEET_DATA td {
    vertical-align:text-top; 
} </style> 

This adjusted my report to appear just how my customer wanted, and because I put it in my application's CSS file, it applied to all interactive reports.

ScottWe.

Apex Post Calculation Computations

A warning - perhaps my musings are different as I wrote this sitting in Melbourne airport, my mind exhausted after a few days of conducting training with not much sleep in the hotel bed.

Once upon a time I was a regular Oracle Forms programmer (and sometimes still current). These days I spend most of my time with Application Express. This makes me happy as I did enjoy mod_plsql - an ancestor (of sorts) of Apex.

Occasionally I notice some parallels between the two, even more occasionally I get around to writing an entry for the world to see - a strange urge for some but it seems that people read even more mundane topics.

There are many attributes available within the Apex environment. By attributes I mean little boxes in the various wizards ready for my to type something in. Sometimes it seems overwhelming. Then I remind myself how flooded with settings the Forms environment must seem. Of course I snap myself back to normal when I think about what I've seen of JDeveloper.

Have you ever wondered what some of these settings do?

Recently I was creating a copy of a data entry form within Apex so I could present a cut-down / read-only version of the page. There were some fields that instead of being Select Lists, I needed to display their descriptive value - not the return value that is stored in the column.

There are a number of solutions to this problem, as with most problems. One solution I came to involved utilising the "Post Calculation Computation" attribute of the item. This means that after I source the item from the database column, I can transform it's value into something else. The obvious solution here would be to pass the value to a function that determines the descriptive form of the value - from some sort of reference code table.
my_pkg.get_label(:P1_VALUE)

I mentioned forms programming before, right? Immediately I thought of post-query triggers and the pros and cons behind various coding techniques in these triggers. First and foremost was the very same practice of taking a value and converting it to a description. This was an expensive task as not only did it require an extra hit on the database, you needed another round trip from Forms runtime to the application server. The better solution was to incorporate the request within the query - perhaps via a key-preserved view.

The same rings true within Application express. Sure, we don't have another round trip between servers since all the action is happening on the database; however it still requires another select statement to be executed. For a dinky (a Aussie/British colloquialism meaning small and insignificant) little Apex page, what's an extra hit on the ever powerful Oracle database? Perhaps try see what happens when scaling your request to thousands of users.

So perhaps some of our old habits can carry on to this modern day programming tool? I'm certainly not saying this post calculation attribute is not useful. I have another field populated via a list manager with a popup lov. This means the values are separated by a colon. In my application, this field holds a list of e-mail addresses. When I want to present this list to the user in a pretty format, I can use this attribute to convert it to something suitable for a HTML page:
REPLACE(:P1_EMAIL_LIST, ':', '<br>')

Of course if you wish to do this, you may need to ensure your item type does not convert special characters.

It seems my plane is about to call for boarding, so I'll save you all from further ramblings... for now. Enjoy your weekend.

Oracle's Broken Links

It seems that like me, people like Tim Hall are finding some of the changes regarding the amalgamation of sites within Oracle a little frustrating.

I've noticed many of the comments within OTN contain links to pages that just aren't there any more.

This OTN entry here mentions a vital link to
http://www.oracle.com/technology/products/database/application_express/howtos/how_to_create_custom_popups.html
which unfortunately redirects to the Oracle Apex overview page
http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html

In an effort to find the contents of this page, I used my friend Google. I took a key part of the URL and used a special search facility that Google has:
inurl:how_to_create_custom_popups

This gave me three results

1. The original document, which still redirects to the overview page. However, if you click the cached link next the described URL, you can open Google's cached version of the page - which should usually suffice.
2. Some Xmarks widget, which didn't help me - but I do recommend Xmarks, by the way.
3. A Japanese hosted version of this page. Google offered to translate it for me, which worked very well. This also solved my problem.

So until Oracle gets up to speed with all the older links, perhaps give the inurl: search option a go.

ScottWE

ps - congrats to Tim for his recent induction into the OakTable Network.

Insync 2010 - I wasn't there.

Hello everyone

I'm starting to see a bit of feedback from the Insync Conference 2010 in Melbourne Australia.

It seems Down Under we're still working on creating the conference vibe, but I'm impressed to find a dedicated (yet thin) blog for the Melbourne leg. Here I found interviews with Connor McDonald & Tom Kyte.

http://insync-conference.com.au/blog/?p=211

I'll note that video of Connor confirms that I'm not the only one that seems to associate alcohol consumption with Oracle. I noted in the latest AUSOUG Foresight Magazine, Marc Lancaster gave a warm review of my appearance at a Brisbane branch meeting - apparently I commented how wonderful the beer & pizza concept is, or something along those lines!

Congratulations to Jeff Kemp, who took out Steven's live PL/SQL Challenge.
http://feuerthoughts.blogspot.com/2010/08/plsql-challenge-live-event-in-melbourne.html
I was looking forward to the live challenge this Friday when Steven comes to Perth, but alas - I have a previous date with our wonderful coastal isle - Rottnest.

Next time, Kempy... next time ;-)

I do look forward to catching up with friends this year at the Perth's leg of the conference series in November, perhaps I'll see you there.

All Programmers Should Read Delightfully Creative Information

Now if you think that post title was a little strange, it's because it's a mnemonic.

Now they haven't really worked for me in the past - just like the poem to remember how many days in each month - I found it easier to remember the raw facts than the poem itself. Besides, the knuckle trick was easier.

Anyhow, this is an Oracle related blog, and I wanted to share an idea to remember the Apex URL syntax of:
f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

Most of the time I remember the order fairly well off the top of my head, though sometimes I need to double check; and sometimes within the mix of a bunch of colons it's hard to work out what is what. Perhaps if you're reading a sentence in your head as you move the cursor between colons...
f?p=MY_APP:HOME::::::

So maybe what I (and others) need is a mnemonic to help us remember the order:

All	Application
Programmers	Page
Should	Session
Read	Request
Delightfully	Debug
Creative	Cache
Information	Items
	Values
	Printer Friendly

I didn't include the last two, since you don't often need to consider the printer friendly attribute and once you have items, you should know values follow. Besides, I tried not to make it too complicated or difficult to remember!

What do you think? Any other suggestions? This really was the first thing that rattled off my head after a productive day of Apexing.

Yup, I also felt like inventing a new verb.

Oracle Sues Google

No, that is not a pun.

Time has been weighed in the favour of personal life recently, but one can't help notice some big news in the local industry.

http://www.zdnet.com/blog/btl/oracle-sues-google-for-java-patent-copyright-infringement/38013

That particular link Scribd the official document, which relates to Google Andoid.

Oracle states "one of the most important technologies Oracle acquired with Sun was the Java platform." Past that slowly dissolved into lawyer speak that I often wonder how our ancient authors of writing could bare to comprehend.

I did notice at the end of page nine a "prayer for relief". Interesting the terminology from all directions that become common-speak. From "googling my employee" to "fall from grace" - sayings and idioms of always been a distant world to me.

Courtesy of Mr Spendolini.

**

On a lighter note, I've been informed I'm doing fairly well this month in Steven (& Finn's) PL/SQL Challenge. I think today's quiz might create some discussion on Steven's associated blog. Hopefully I can keep up my recent form and snare a prize ;-)