API Builder – Working Example

The following is a simple example of how API Generator works.

1. UnZIP and locate all files into single directory.

API Builder

2. Load api_builder.sql script into Toad or SQLplus and run.

API Builder

3. Base tables are created. These tables will contain the generated code.

API Builder

4. Base packages are created. This is where the api packages are created.

API Builder

5. Output is spooled from the base tables to a physical file, output_script.sql.

API Builder

6. output_script.sql is then run and commited to your schema.

7. One package will be created for each table providing all DML functions required to handle data management. There is also a P_UTIL_GLOBAL package created to handle sequences.This package contains functions to get the current and next values for all sequences in your schema.

8. You’re done! You can now run this as many times as you like. Either everytime your database changes or as a part of a daily build architecture.


There are some features that must be in place for the API Generator to work.

All tables in our schema must have a corresponding sequence with the suffix “_SEQ”. eg. CUSTOMER table has sequence CUSTOMER_SEQ. However, this convention can be changed should you decide to modify the source code.

The API Generator is built and tested on Oracle 10g. Other versions of Oracle can not be guaranteed but will probably work from version 8 onwards.

You must run the API builder from a tool that is able to spool to a physical file. For example TOAD, or SQL*PLUS. Please note that Oracle’s SQL Developer DOES NOT spool to files and therefore will not with with the API Builder. (at the time of writing!)

Leave a Reply

Your email address will not be published. Required fields are marked *