Monday 5 November 2012

Package for Creating Custom Code

Sometimes you will need to create your own processes for fetching values, validating and updating tables in a form. In some situations the wizard generated form will not be good enough to cover all you need. In that case you need to write your own code. Some programs like TOAD have a routine which generates update or delete procedures for you. However, you need to extend that and it is quite a bit of work. In my Demo Application I made the code available, which does almost all the work for you. This package has a couple of functions able to generate the code which requires only some minor changes. If you ever need to go away from the standard APEX form, you can do that this way:

1. Create the package "create_pkg_from_table" in your schema by running the code from the page I mentioned,

2. Create a form using wizard,

3. Remove the automatic processes and change the standard settings in the page items and use the following setting:

a) Set the "Source Used" to "Only when current..."
b) Set the "Source Type" to "Static Assignment..."

4. Create the custom package related to the table you reference in your form, for fetching, validating, updating and deleting records by running this SQL and executing the output:
SELECT create_pkg_from_table.create_package ('EMP', 'EMPNO', 1)
  FROM DUAL;
5. Finally, run the following SQL to create the calls to the package functions and procedures you will need in your page processes and validations:
SELECT create_pkg_from_table.create_plsql_block ('EMP', 'EMPNO', 1)
  FROM DUAL;
The only thing you need to do now is to format that code and paste it in the appropriate process.