Oracle Application Express on APEX.ORACLE.COM was upgraded to v5.1 last week.
One of the new things coming in v5.1 is a new packaged app called ‘Quick SQL.’ VP of Development Tools Mike Hichwa demonstrated it at UKOUG Tech 16 this month, and the audience seemed to eat it up. Basically, it allows you to use a shorthand of sorts to describe a table or tables, and it will generate the DDL for you.
Heck, it will even generate data, and across tables with foreign key relationships for you too.
So I wanted to have a go at it.
Do you have a workspace yet? If not, it only takes a few minutes to sign up and get started.
Once you do, you can install Quick SQL.
Installing is just a matter of clicking the button.
Once it’s available, you can click the ‘Play/Run’ button. You’ll then be prompted to setup the application – basically, who is going to get to run it and/or administer it.
So after a click-click-click, we’re ready to rock and roll.
Cool, so let’s create a couple of tables that are ‘related.’ Oh, and I want some demo data too.
Maybe you don’t like exactly how this works out of the box? For example, you don’t want IDENTITY columns. No worries, you have preferences.
Alright, now I want to run this.
I have a few options. If I were running APEX 5.1 and the Quick SQL app in my own database, I’d just copy the generated code over to SQL Developer and run it.
But, I’m running this in the APEX Cloud, so I’m going to run it there. The SQL Workbench has a really nice script feature I can use. This lets me save this work as a ‘create’ script, and I can run it now, or over and over again if I decide to blow everything away and start from scratch.
Alright, so I have my two tables and my data – I should create an app, right?
Now, please don’t judge APEX or APEX 5.1 based on what I’m about to show you – because I AM NOT an APEX developer. But, in a few moments, I was able to figure out how to setup a master/detail form and throw in a picture, and run it with my new tables and data.
One Last Thing
While hacking my app together, I was very happy to see how NICE the design process is. As I was putting in my SQL…it was very quick to remind me I had made a mistake.
It was awesome to spend time with Mike today at Oracle code event at Washington DC. The first thing that I started doing was to try this feature out :). I just got to say it was impressive and really cool when I experienced Mikes presentation first hand. Quick request. Can we get a support for boolean keyword by converting it into char(1) or varchar2(5) and generate statements with either true or false or Y or N for values.
What I mean is this
in my worksheet I jot the following details
class /insert 500
My intention for lab_boolean is wether the class has a associated lab or not. The DDL converted it into varchar2(4000). If this can be converted into char(1) (with corresponding insert statements generating a value of ‘Y’ or ‘N’) or varchar2(5) (with corresponding insert statements generating values of ‘true’ or ‘false’).
Thanks and Regards
Mike really liked your idea and added it to his ‘list’ 🙂
Thanks for coming out to the event yesterday and sharing your feedback!
Awesome! Now if we could just get folks to understand the basics of good relational design, say via examples in blog posts, our apps would be so much happier! 😉
Examples in blog posts? Who has that kind of time?
Very cool feature. Nice that it automatically builds the RI and add audit columns. Now if only there were an option to define a secondary unique key constraint for the natural key (e.g., brewery or beer name) so you don’t accidentally enter duplicates in the tables.
If only? But there is.
And the generated code…