|
| |
NOTE - Use your Browsers BACK Button to return to prior page or
CLICK here.
| Creating Custom Oracle Functions |
 |
This page demonstrates the use of user-defined custom
functions in Oracle (which can be subsequently used in SQR programs). Notice the two
frogs on the left... my 2 "models" are from different nature photos which I
arranged in a combined picture... As my picture progressed my wife asked why I was drawing
two frogs mating! I quickly added the leaf between them... not sure if it helps much...
aside from that particular blunder I like the results... now... moving on... |
| Suppose you're loading an interface table with data from a flat file. Many of the
fields loaded must be validated and flagged for errors. A common method of tracking the
errors is using a numeric error code which, thru binary representation, designates all
fields that failed validation. Consider the list of fields below and the error value
assigned to each: |
| FIELD |
BINARY-BASED VALUE |
| Business_Unit |
1 |
| Deptid |
2 |
| Account |
4 |
| Product |
8 |
| Affiliate |
16 |
| Location |
32 |
| Project_ID |
64 |
| Resource_Type |
128 |
| Resource_Category |
256 |
| Resource_Sub_Cat |
512 |
| The error code is an accumulator of each binary-based value of the fields in error. If
Business_Unit, Deptid and Project_ID were invalid the numeric error code would be set to
67 (1 + 2 + 64)... We're assuming the data is still loaded into the table along with the
resulting ERROR_CODE value. |
| How can you utilize the
ERROR_CODE values stored in the table? |
| If ERROR_CODE = ZERO you know that all fields on the row have passed validation. The
rows that failed may have a unique combination of errors. How would you Query all the rows
that had ACCOUNT errors? Or Project_Id errors? The ERROR_CODE is a combination of all
errors - not just one. Business_Unit could be easily tested with MOD(ERROR_CODE,2) = 1
(because it has a value of 1)... the other fields require special handling... So here
comes our new custom function... I'll call it BIN... because it converts a numeric value,
in this case our ERROR_CODE, into a binary string... The example I mentioned earlier with
the error code value of 67 (1 + 2 + 64) would be represented as '1000011' in binary... I'd
like the BIN function to also pad the binary string with zeroes for a length I specify (as
a parameter)... let's use 10 as the maximum length to pad (since there are 10 possible
errors in the list above)... that means '0001000011' will be returned by our function...
Also note the Project_ID (64) is represented ahead of the Account (2) and Business_Unit
(1) fields... I want the BIN function to accept a reversal indicator to flip the binary
string if we wish... so our resulting binary string will equal '1100001000'... Now the
string is converted to a visual representation of the field list... I can see the first
field (Business_Unit) is invalid, the second field (Account) is invalid and the seventh
field (Project_ID) is invalid... let's code the BIN function now... |
|
Create the custom BIN function (PL/SQL) |
/* ***************************************************************** */
/* * * */
/* * MODULE: BIN.SQL * */
/* * AUTHOR: TONY DELIA. * */
/* * DATE: 02/09/2000. * */
/* * DESC: BINARY STRING CONVERSION FUNCTION. * */
/* * * */
/* ***************************************************************** */
/* * * */
/* * USAGE: BIN(input value, output length, reverse flag) * */
/* * * */
/* * RETURNS: Binary Text String * */
/* * * */
/* ***************************************************************** */
/* * * */
/* * SAMPLE #1: select BIN(21, 8, '') from dual; * */
/* * 21 converted to '00010101' * */
/* * * */
/* * SAMPLE #2: select BIN(21, 8, 'R') from dual; * */
/* * 21 converted to '10101000' (reversed bits) * */
/* * * */
/* ***************************************************************** */
CREATE FUNCTION BIN(I_num IN NUMBER, I_len IN NUMBER, I_rev IN VARCHAR2)
RETURN VARCHAR2
IS
W_num NUMBER;
W_pow NUMBER;
W_bin NUMBER;
W_base NUMBER;
W_rev VARCHAR2(1);
W_char VARCHAR2(1);
O_string VARCHAR2(32);
BEGIN
O_string := '';
W_num := I_num;
W_rev := rpad(I_rev,1,' ');
W_pow := 0;
W_bin := 0;
while W_num >= power(2, W_bin)
loop
W_pow := W_bin;
W_bin := W_bin + 1;
end loop;
W_base := power(2, W_pow);
while W_base >= 1
loop
W_char := '0';
if W_num >= W_base then
W_char := '1';
W_num := W_num - W_base;
end if;
if W_rev = 'R' then
O_string := W_char || O_string;
else
O_string := O_string || W_char;
end if;
W_base := W_base / 2;
end loop;
if I_len > length(O_string) then
if W_rev = 'R' then
O_string := rpad(O_string, I_len, '0');
else
O_string := lpad(O_string, I_len, '0');
end if;
end if;
RETURN(O_string);
END;
|
| Conclusion - Utilizing the New
BIN Function |
| You can utilize the BIN function in SQR or other tools such as PS/Query or SQL*Plus
(Make sure the Synonym/Grants have been assigned - sounds like a job for SQL*Engine)... To
search for a specific error (such as Project_ID) you can set your WHERE clause as: WHERE
SUBSTR(BIN(error_code,'10,'R'),7,1) = '1'
This interrogates the seventh position returned (in reverse order) by the BIN function.
The seventh field is Project_ID... if it equals '1' it is returned as a result of the
WHERE clause... this is just an example... The converted values could also be stored and
processed later... |
 |
I would appreciate any feedback you may have on this site.
Send mail to tdelia@erols.com or click on the
Octopus. |
 |
Please report any technical difficulties you may encounter to the address
above OR click on the Octopus. Thanks. |
NOTE - Use your Browsers BACK Button to return to prior page.
Tony DeLia - Updated February 27, 2000
|
