Last week, a coworker on the SQLDeveloper team linked via his Twitter account to a blog post he’d written in 2013 about using SQL Developer to generate table APIs. I mentioned that this seemed very similar to functionality in APEX, which he wasn’t familiar with; so this post is by request. First time ever!
The functionality I’m going to talk about has existed since at least APEX 4.0 (when I first started using APEX); I’m not sure when it was originally introduced. But it’s not talked about much or widely known, so you might consider this a hidden gem.
Sometimes, it is desirable to restrict developers from interacting directly with tables, especially for DML statements. You may want to include extra security checks or logging, for instance, which may be cumbersome to implement and maintain through triggers; it can be far easier to create packages with get, set, update, and/or delete functions for your tables and require your developers to use those. You can then implement whatever features you want in those functions; you could even update the underlying table structure without breaking applications. Pretty nifty. (Of course, this does introduce a bit of extra overhead for all DML statements; you’ll have to decide for yourself what’s appropriate. It’s certainly possible to create these packages by hand, but APEX can help you.
To start, log into the APEX Builder and go to the SQL Workshop.
From here, select Utilities (fourth icon), and then select the Methods on Tables entry.
This will take you through a very short wizard, where you specify the package name, then select any tables you want included, and then have the option to have APEX create the package directly, or just provide you with the specification and body so you can edit them before creating the package. By default, the package will include INS_, UPD_, DEL_, and GET_ functions for each of the chosen tables. (Note that SQLDeveloper’s implementation does not provide the GET_ function).
In my experience, I’ve generally wanted to add business logic to the functions to provide security, calculate complex default values, and so on, so I’ve generally bailed out of the wizard once it had provided me with the basic framework, but there’s no reason you can’t edit the package once APEX has compiled it.
How exactly can you use the get method when running an interactive report? Since the Interactive Report doesn’t like using a function to get the columns returned? You have to go through and use a collection or something like that to get the data out, correct?
Thank you,
Tony Miller
Los Alamos, NM
The get_ function that is generated is fairly limited–it specifically requires you to provide the primary key for the row that you’re wanting to retrieve. So it’s not going to work as the basis for an interactive report. If you want to use an API for that, my recommendation would be to build a pipelined function and wrap it in a view, but at this point, APEX doesn’t generate that for you.