API Builder – Working Example
The following is a simple example of how API Generator works.
1. UnZIP and locate all files into single directory.
2. Load api_builder.sql script into Toad or SQLplus and run.
3. Base tables are created. These tables will contain the generated code.
4. Base packages are created. This is where the api packages are created.
5. Output is spooled from the base tables to a physical file, output_script.sql.
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.
Assumptions
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