Have you heard of Oracle Designer? Perhaps you even still use it?
Designer incorporates support for business process modeling, systems analysis, software design and system generation.
I don’t want to get you too excited, because this technology is being phased out. You can read the official statement of direction here, but but going forward we’re recommending you use JDeveloper, Fusion, and our other middleware technologies to build your applications.
What I want to get to, is a very frequently asked question from Oracle Designer customers who are starting to use Oracle SQL Developer and Oracle SQL Developer Data Modeler:
How Can We Get Our Table APIs?
One of the Oracle Designer features was the ability to create PL/SQL packages that handled your SELECTs, INSERTs, UPDATEs, and DELETEs for your tables. My colleague Chris Muir had this to say a few years ago about the feature:
Not only can Oracle Designer generate Oracle Forms, but it can also create a set of packages to wrap specified schema tables, known as the Table Application Programming Interface (Table API). The packages allow the calling program to indirectly select, insert, update, and delete the relating table data through the Table API PL/SQL packages.
So, the good news is that we can mostly do this in SQL Developer, mostly.
Just find your table in the connection tree, right-click, and ask for the code to be generated.
What gets ‘spit out’ is a PL/SQL package with 3 procedures:
Notice there’s no ‘SEL,’ hence my mostly qualifier.
Here’s the actual code generated for my BEER table:
CREATE OR REPLACE PACKAGE body BEER_tapi IS -- insert -- stuff PROCEDURE ins( p_STATE IN BEER.STATE%TYPE DEFAULT NULL , p_COUNTRY IN BEER.COUNTRY%TYPE DEFAULT NULL , p_ID IN BEER.ID%TYPE DEFAULT NULL , p_CITY IN BEER.CITY%TYPE DEFAULT NULL , p_BREWERY IN BEER.BREWERY%TYPE ) IS BEGIN INSERT INTO BEER ( STATE ,COUNTRY ,ID ,CITY ,BREWERY ) VALUES ( p_STATE ,p_COUNTRY ,p_ID ,p_CITY ,p_BREWERY ); END; -- update PROCEDURE upd ( p_STATE IN BEER.STATE%TYPE DEFAULT NULL , p_COUNTRY IN BEER.COUNTRY%TYPE DEFAULT NULL , p_ID IN BEER.ID%TYPE DEFAULT NULL , p_CITY IN BEER.CITY%TYPE DEFAULT NULL , p_BREWERY IN BEER.BREWERY%TYPE ) IS BEGIN UPDATE BEER SET STATE = p_STATE ,COUNTRY = p_COUNTRY ,CITY = p_CITY ,BREWERY = p_BREWERY WHERE ID = p_ID; END; -- del PROCEDURE del( p_ID IN BEER.ID%TYPE ) IS BEGIN DELETE FROM BEER WHERE ID = p_ID; END; END BEER_tapi;
A Final Word On Oracle Designer
While Oracle SQL Developer and Oracle SQL Developer Data Modeler will do many of the things Designer did, they will never do EVERYTHING Designer did. So we can help you move off Designer, but don’t expect the SQL Developer family to be complete replacement. In terms of the Designer ‘designs’ – these can be largely imported into new Oracle SQL Developer Data Modeler models. We’re always tweaking the import logic to add more and more support for Designer artifacts. Version 3.3 of the Modeler did quite a bit of work in this area specifically.