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.

Look for it under the 'Software Development' category.
Look for it under the ‘Software Development’ category.

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.

The help is very helpful - you get lots of scenarios and explanations of the syntax and flags.
The help is very helpful – you get lots of scenarios and explanations of the syntax and flags.

Cool, so let’s create a couple of tables that are ‘related.’ Oh, and I want some demo data too.

What kind of tables did you think I was going to create?
What kind of tables did you think I was going to create?

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.

I'm fine with the defaults from what I can tell so far.
I’m fine with the defaults from what I can tell so far.

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.

Ran the script, got the results. EZ-PZ.
Ran the script, got the results. EZ-PZ.

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.

The pic is from the Norway Oracle User Group conference from a couple of years ago...
The pic is from the Norway Oracle User Group conference from a couple of years ago…

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.

Dummy, fix your code!
Dummy, fix your code!
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

7 Comments

  1. Hi Jeff,

    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
    subject vc(100)
    teacher vc(30)
    roomid
    lab_boolean

    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
    Venkata

    • Mike really liked your idea and added it to his ‘list’ 🙂

      Thanks for coming out to the event yesterday and sharing your feedback!

  2. 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! 😉

  3. 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.

Write A Comment