PeopleCode | Searching the entire database for a specific piece of data.
A. Needle in a Haystack
Our next requirement might sound like a case of searching for a needle in the haystack. You’d like to search the database for a particular piece of text, but you don’t know exactly where that text is stored. Or perhaps some other data has been entered into the system by mistake (such as a duplicate Employee ID), and now you want to search the entire database to determine where that text has been used.
This question of database-wide search comes up a lot on Oracle forums. While there are some neat solutions floating about, they tend to rely on some complex SQL – such as writing a PL/SQL program – or they might need the assistance of a DBA to implement. An example of one SQL-based solution is included in the ‘See Also’ section below, but be warned, this uses some fairly advanced constructs.
As an alternative, the approach below is PeopleSoft specific and does not contain any unusual or advanced constructs.
B. SearchDatabase Function
Below is the database search coded as a function. This accepts a field name and search text as parameters, and returns an array containing all the matching records.
Function SearchDatabase(&field_name As string,
&search_text As string)
Returns array of string;
Local string &rec_select, &field_select, &match;
Local SQL &RecSQL, &FieldSQL;
Local array of string &results = CreateArray("");
Local integer &count = 0;
/* Select all records containing the database field */
&rec_select = "select a.recname from psrecfield a, psrecdefn b" |
" where a.recname = b.recname" |
" and b.rectype = 0" |
" and a.fieldname = :1";
&RecSQL = CreateSQL(&rec_select, &field_name);
While &RecSQL.Fetch(&recname)
/* Check to see if search text exists in current record */
&field_select = "select 'x' from %table(" | &recname | ") " |
"where " | &field_name | " = '" |
&search_text | "'";
&FieldSQL = CreateSQL(&field_select);
If &FieldSQL.Fetch(&match) Then
/* Match exists – add item to array */
&count = &count + 1;
&results [&count] = &recname;
End-If;
End-While;
Return &results;
End-Function;
C. Calling the Function
With the function defined, you can now go ahead and call it with code similar to this:
&field_name = "SSR_ITEM_ID";
&search_text = "00000010301";
&results = SearchDatabase(&field_name, &search_text);
If &results.Len = 0 Then
MessageBox(0, "", 0, 0, "No matches found");
Else
&message_string = String(&results.Len) | " match(es) found in: ";
For &i = 1 To &results.Len
&message_string = &message_string | &results [&i] | ", ";
End-For;
MessageBox(0, "", 0, 0, &message_string);
End-If;
D. Extending the Function
There are a number of ways in which the function could be expanded.
Firstly, if you wished to search for an alternative data type, such as a number, you would have to make a couple of amendments. When defining the function, the ‘&search_text’ variable would have to be defined as a number:
Function SearchDatabaseNumber(&field_name As string,
&search_text As number)
Returns array of string;
Then, as part of constructing the SQL statement, the single quote characters enclosing the search text would need to be removed:
&field_select = "select 'x' from %table(" | &recname | ")" |
" where " | &field_name | " = " | &search_text;
Secondly, another extension would be to allow string ‘like’ searches, as opposed to doing an exact field match. The ‘&field_select’ line of code would again have to change to cater for ‘like’ searches (using regular expressions – refer to Tip 047 for further details):
&field_select = "select 'x' from %table(" | &recname | ")" |
" where regexp_like(" | &field_name | ", '" |
&search_text | "')";
Finally, to make the function even more powerful, you could provide an additional parameter to the function, specifying the field type (‘string’, ‘number’, ‘date’). The code could then include the appropriate functionality to handle each field type.
See Also:
Tip 047: Regular Expressions in SQL