Oracle Functions

 

 

NOTE - Use your Browsers BACK Button to return to prior page or CLICK here.

   Creating Custom Oracle Functions
wcfrog02.jpg (18230 bytes) 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...

   A Practical Example
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...

   Feedback
ftoct01.jpg (12389 bytes) I would appreciate any feedback you may have on this site. Send mail to tdelia@erols.com or click on the Octopus.
   Technical difficulties?
pg011.jpg (38716 bytes) 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