What is the multi-purpose code in Oracle Forms?

In the past few years, when Oracle databases abandoned the relevant client OracleForms, some of the built-in functions that subsequently disappeared were about the function (alert message function) passing parameters. If you handle errors or missing input parameters.

Part of the Forms migration process is to change this built-in function from 6i to 9i.

Complex applications may contain thousands of warning messages, and changes to a major application (Forms) may lead to thousands of changes. Such a change is indeed an act of suspicion.

On the other hand, how many times have you written DBMS_OUTPUT.PUT_LINE ('') as a DBA and programmer using PL/SQL ('')? DBMS_OUTPUT.PUT_LINE, which must be written or typed, becomes boring. Isn't it better to use convenient and built-in short code?

It may not have been well thought out, but it is more likely to be attributed to good luck or the fact that the same thing must be tapped again and again. Clever OracleForms programmers have created their own built-in functions, the process is used to generate warning messages. The same principle can be used in your daily PL/SQL code. In fact, you can create a small message library to manage many types of output messages. Let's look at the possibilities.

A simple warning message process

As shown in the title of this chapter, the first method is very simple. Suppose you have a common requirement to output the number of records updated by a process, function, or code block. Let's assume that the number of rows updated is 46. After the following procedure is used, a simple "am (46);" statement can be output as needed:

 
 

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

Another version can process the string type, so calling "ams ('your message here ');" significantly reduces the number of times you typed in. When debugging or solving the problem, such a simple built-in function is very valuable for outputting the "where am I in the code" statement.

The location report can be confirmed. For example, you enter the branch in the IF-THEN-ELSE statement. If your problem code calls many other objects (processes, functions, etc ), output status information such as "calling function X" or "returned from function X" to confirm the process flow. Finally, another use case is the report value. You can report or track how the value of a variable is changed.

Create a warning message Library

Of course, the complexity and flexibility of your message library depend entirely on you. If your (output) message is simple, keep the function process simple. To be more accurate, the minimum number of function processes is maintained. Only two simple processes, ams and amn, can be used to output messages based on strings and values.

If you want to change the output text content according to the output of the operation, such as the output of the DML statement, you may need three new built-in processes (one for insert, update, and delete operations ). You may want to explain the deletion type or reason. For example, one step of a batch processing job is to calculate the number of repeated records.

Therefore, output like "Records counted: 46" is useful, but in this case, "Duplicates counted: 46" is more effective. Therefore, we have added two new built-in processes.

Now we have at least six different processes. Now, management problems should be obvious. We look for some simple but robust processes. There are at least two ways to simplify the required features. One way is to enable the warning message process to receive two input parameters. Another method I want to introduce is to package these processes.

Increase the number of input parameters

Again, if the previous simple method can meet your requirements, you do not need to go further. There are two input parameters. The first parameter is the text or basis of the message, and the second parameter can be the output, position, status, or value. If you focus on data type conversion, the combination of the two input parameters text/text and text/number can be unified into the text/text type.

Do you really have to do this conversion? No, but to be consistent with what you already have, if you do type ing elsewhere, type ing will also be conducted here. Regardless of this, 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 compilation, the following is an example.

 
 

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

Well, this output may look better (note that there are no spaces in the output msg1 and msg2 ). At this point, we must format one or all two message inputs to make the output look better. However, if you do not need to care about the appearance, it is very simple to create a message based on input such as ('dupes ', 46), although you need to process spaces or formatting.

Is this method flawed? This depends on the situation. What if you only need msg1 instead of msg2? When this process is created, msg2 must be allowed to be null. Obviously, this is not required for msg1, right?

 
 

  
  
CREATE OR REPLACE procedure am   
  
  
(msg1 varchar2, msg2 varchar2 default null) as   
  
  
begin   
  
  
dbms_output.put_line(msg1||msg2);   
  
  
end;   
  
  
/   
  
  
Procedure created.   
  
  
SQL> exec am('Where am I?');   
  
  
Where am I?   
  
  
PL/SQL procedure successfully completed.  
 
 

The above content is the multi-purpose code analysis in Oracle Forms, hoping to help you in this aspect.