Multi-purpose code in Oracle forms

A few years ago, when Oracle abandoned the client's forms, one of the built-in functions that disappeared was about passing arguments to the alert message function. If you are dealing with errors or missing input parameters (you should), part of the forms migration process is to change this built-in function from the 6i version to the 9i version. Complex applications may contain thousands of warning messages, and changes in a major application (i.e. forms) can lead to thousands of changes. It is really a nuisance to make such a change.

On the other hand, how many times have you written dbms_output as a DBA and programmer using Pl/sql? Put_Line (")" a dbms_output that must be written or typed. Put_Line become very boring, using convenient, built-in short code is not better? It may not be deliberate, but it's more about good luck or the fact that the same thing has to be knocked over and over again. Clever forms programmers create their own built-in functions, A process is used to generate a warning message. The same principle can be used in your daily Pl/sql code. In fact, you can create a small message store to manage many types of output messages. Let's look at some of these possibilities.

A simple warning message process

As indicated in the title of this chapter, the first method is very simple. Let's say you have a common requirement to output the number of records updated by a procedure, function, or code block. Let's assume that the number of rows being updated is 46. After using the following procedure, a simple "AM (46);" Statement can be the output you need:

CREATE OR REPLACE procedure am (msg number) as
　　 begin
　　 dbms_output.put_line('Records updated: '||msg);
　　 end;
　　 /

Another version can handle the string type, so for "AMS (' your message here ');" The call significantly reduces the number of times you typed it. In the debugging or problem-solving code, there is a simple built-in function that is invaluable in the statement that the "Where AM I" code is being exported. Location reports can confirm, for example, which branch of the If-then-else statement you are entering. If your problem code calls many times for other objects (procedures, functions, and so on), the output of state information like "calling function X" or "returned from function X" can confirm the flow of the process. Finally, another use is the report value. You can report or track how the value of a variable is changed.

Create a warning message store

Of course, the complexity and flexibility of your message base depend entirely on you. If your (output) message is simple, keep the function process simple. To be more precise, keep the number of function processes to a minimum. As long as two simple processes, AMS and AMN, can be used to output messages based on strings and values.

If you need to change the output of the text according to the output of the operation, such as the output of the DML statement, you may need three new built-in processes (insert, update, and delete operations each). You may want to specify the type or cause of the deletion. For example, one step in a batch job is to calculate the number of duplicate records. So output like "Records counted:46" is useful, but in this case "duplicates counted:46" will be more effective. Therefore, we have added 2 new built-in processes.

In this way, we now have at least 6 different processes. Now, the management of the problem should be more obvious. We are looking for something simple, but also a robust process. There are at least two ways you can use to simplify the functionality you need. One way to do this is to have the warning message process receive two input parameters. Another approach, which I'm going to introduce, is to package these processes.

Increase the number of input parameters

Again, if the previous simple method can meet your requirements, then there is no need to go further. Create a procedure with two input parameters, the first parameter is the message text or the base, and the second parameter can be an output, a position, a state, or a numeric value. If you are concerned with the conversion of data types, then the combination of the two input parameters Text/text and Text/number can be unified into text/text types. Do you really have to do this kind of conversion? No, but in order to be consistent with what you already have, if you do a type mapping somewhere else, then there's also a type mapping. Regardless of these, the following example shows the flexibility of the first method.

CREATE OR REPLACE procedure am (msg1 varchar2, msg2 varchar2) as
　　 begin
　　 dbms_output.put_line(msg1||msg2);
　　 end;
　　 /

After compiling, here is the example used.

SQL> set serveroutput on
　　 SQL> exec am('Here I am',46);
　　 Here I am46
　　 PL/SQL procedure successfully completed.