Handling Multiple Child Records - Part 1

I'm currently working on a data feed project for downstream process. Using this application, the end user can create his/her own template (Type of fields, length, order etc) and then use that template to download the data for a given time period in 3 different formats; pipe delimited, tab delimited or fixed width. The table structure is as follows:

Base Table (BT) {BT_ID, col2, col3, ... , coln}
Child Table 1 (CT1) {BT_ID, Seq_No, col3, col4, ... , coln}
Child Table 2 (CT2) {BT_ID, Seq_No, col3, col4, ... , coln}
Child Table 3 (CT3) {BT_ID, Seq_No, col3, col4, ... , coln}
Child Table 4 (CT4) {BT_ID, Seq_No, col3, col4, ... , coln}
Child Table 5 (CT5) {BT_ID, Seq_No, col3, col4, ... , coln}
Child Table 6 (CT6) {BT_ID, Seq_No, col3, col4, ... , coln}

where BT_ID is the PK of the parent table and FK in child tables. BT_ID and Seq_No combination is  PK in child tables. My query is something like this:

SELECT BT.COL2, BT.COL3, ..., BT.COLN,
             CT1.COL3, CT1.COL4, ..., CT1.COLN,
             CT2.COL3, CT2.COL4, ..., CT2.COLN,

             CT3.COL3, CT3.COL4, ..., CT3.COLN,
             CT4.COL3, CT4.COL4, ..., CT4.COLN,
             CT5.COL3, CT5.COL4, ..., CT5.COLN,
             CT6.COL3, CT6.COL4, ..., CT6.COLN
   FROM BT, CT1, CT2, CT3, CT4, CT5, CT6
 WHERE BT.BT_ID = CT1.BT_ID (+)
      AND BT.BT_ID = CT2.BT_ID (+)
      AND BT.BT_ID = CT3.BT_ID (+)
      AND BT.BT_ID = CT4.BT_ID (+)
      AND BT.BT_ID = CT5.BT_ID (+)
      AND BT.BT_ID = CT6.BT_ID (+)

Now, there are two cases. First, one base record has only one type of child record i.e. A record in BT has child records only in CT1. Second, one base record has more than one type of child record i.e. A record in BT has child records in CT1 and CT2. The latter case in my application is very rare, say less than 0.01% of cases will fall under second category but still we have to handle those type of records. I'll discuss that solution in my second post. For now, let's assume that each base records can have only one type of child record.

To explain the ensuing scenario better, let me show a sample data fetched by the above query:

BT    CT1      CT2    CT3    CT4    CT5    CT6
b1     b1-1     null     null     null     null     null
b1     b1-2     null     null     null     null     null
b1     b1-3     null     null     null     null     null

b2     null       b2-1   null     null     null     null
b2     null       b2-2   null     null     null     null

In the above scenario, b1 record has 3 child records in CT1. b2 record has 2 child records in BT2. Also, "b1-1" represents two columns from CT1,BT_ID and Seq_No respectively.

The problem is that when user creates a template, (s)he can select fields from any of the 7 tables. So, there are 4 different cases:

Case 1: Template has fields from BT only.


Actual Output: Result set will have 5 rows:


b1
b1
b1
b2
b2

Expected Output: Result should have 2 rows:

b1
b2

Case 2: Template has fields from BT and CT1 only.

Actual Output: Result set will have 5 rows:

b1     b1-1
b1     b1-2
b1     b1-3
b2     null
b2     null

Expected Output: Result set should have 4 rows:

b1     b1-1
b1     b1-2
b1     b1-3
b2     null

Case 3: Template has fields from BT and CT2 only.

Actual Output: Result set will have 5 rows:

b1     null
b1     null
b1     null
b2     b2-1
b2     b2-2

Expected Output: Result set should have 3 rows:

b1     null
b2     b2-1
b2     b2-2

Case 4: Template has fields from BT, CT1 and CT2.

Actual Output: Result set will have 5 rows:


b1     b1-1     null
b1     b1-2     null
b1     b1-3     null
b2     null       b2-1
b2     null       b2-2

Expected Output: Result should have 5 rows:

b1     b1-1     null
b1     b1-2     null
b1     b1-3     null
b2     null       b2-1
b2     null       b2-2

So basically, the problem is, I should only repeat the records if the fields are selected in the template.All the cases, except 4, fail. Unfortunately, I can not apply DISTINCT as it'll give the wrong result set in many other scenarios. For example, when the child data is repeating itself or is NULL, it'll only show one row whereas I should show as many number of rows as present in child table for that record.  To overcome this problem, we used the below approach.

1. Generate template key. This key was a 6 character string made up of 1s and 0s, much like a binary number. Each character representing child table. If the char is 1, that means the fields for that child table is present in template. So mapping is as follows:

CT1 -> 1st Digit
CT2 -> 2nd Digit
CT3 -> 3rd Digit
CT4 -> 4th Digit
CT5 -> 5th Digit
CT6 -> 6th Digit

If the key generated is 111111, then at least one field from all the child tables is present in template. If the key generated is 000000, then none of the fields from the child tables is present. If the key is 100000, then at least one field from CT1 is present in template and so on and so forth.

2. Generate record key. This key is similar to template key except instead of 1s and 0s, it has digits from 1-6 in the same order. So, for a record with 0 child records, it'll be 000000. For a record with child records in CT1, it'll be 100000. For a record with child records in CT2, it'll be 020000. For a record with child records in CT3, it'll be 003000. For a record with child records in CT4, it'll be 000400. For a record with child records in CT5, it'll be 000050. For a record with child records in CT6, it'll be 000006.

3. Map record key to template key using base key, '123456', and generate child_type. So for example, if the template key is 100000, record key is 003000, then translate function, TRANSLATE('003000','123456','100000'), will return 000000. If the record key is 100000, then the translate function, TRANSLATE('100000','123456','100000'), will return 100000. Similarly, whenever the record has child data and template has at least a field from the corresponding child table, the child_type will always be more than zero.

4. Assign an order to each record based on seq no. Also, a record with no child records will have order as 1. So, order (in first column) would be something like this:

1     b1     b1-1     null     null     null     null     null
2     b1     b1-2     null     null     null     null     null
3     b1     b1-3     null     null     null     null     null
1     b2     null       b2-1   null     null     null     null
2     b2     null       b2-2   null     null     null     null

1     b3     null       null     null     null     null     null

 5. Filter out all the records which doesn't meet either one of the following two conditions; order = 1 or child_type > 0.

That's it. The records will always be generated per template. So, the final query after implementing above steps will look something like this:


SELECT BT.COL2, BT.COL3, ..., BT.COLN,
             CT1.COL3, CT1.COL4, ..., CT1.COLN,
             CT2.COL3, CT2.COL4, ..., CT2.COLN,
             CT3.COL3, CT3.COL4, ..., CT3.COLN,              CT4.COL3, CT4.COL4, ..., CT4.COLN,
             CT5.COL3, CT5.COL4, ..., CT5.COLN,
             CT6.COL3, CT6.COL4, ..., CT6.COLN
FROM
(SELECT  GREATEST(NVL(CT1.Seq_No,1), NVL(CT2.Seq_No,1),
                                    NVL(CT3.Seq_No,1), NVL(CT4.Seq_No,1),
                                    NVL(CT5.Seq_No,1), NVL(CT6.Seq_No,1)) CHILD_ORDER,
             TO_NUMBER(TRANSLATE(
             DECODE(CT1.Seq_No, NULL, 0, 1)||DECODE(CT2.Seq_No, NULL, 0, 2)||
             DECODE(CT3.Seq_No, NULL, 0, 3)||DECODE(CT4.Seq_No, NULL, 0, 4)||
             DECODE(CT5.Seq_No, NULL, 0, 5)||DECODE(CT6.Seq_No, NULL, 0, 6)
             , '123456', <template_key>)) CHILD_TYPE,
             BT.COL2, BT.COL3, ..., BT.COLN,
             CT1.COL3, CT1.COL4, ..., CT1.COLN,
             CT2.COL3, CT2.COL4, ..., CT2.COLN,
             CT3.COL3, CT3.COL4, ..., CT3.COLN,             CT4.COL3, CT4.COL4, ..., CT4.COLN,
             CT5.COL3, CT5.COL4, ..., CT5.COLN,
             CT6.COL3, CT6.COL4, ..., CT6.COLN
   FROM BT, CT1, CT2, CT3, CT4, CT5, CT6
 WHERE BT.BT_ID = CT1.BT_ID (+)
      AND BT.BT_ID = CT2.BT_ID (+)
      AND BT.BT_ID = CT3.BT_ID (+)
      AND BT.BT_ID = CT4.BT_ID (+)
      AND BT.BT_ID = CT5.BT_ID (+)
      AND BT.BT_ID = CT6.BT_ID (+)
) WHERE CHILD_TYPE > 0 OR CHILD_ORDER = 1;


This will handle all the cases except when the same record has multiple type of child records. In that case, the joining condition will fail the number of records returned will always be more. More on that in second part.

On Delete Cascade: Using DML

ORA-02292 Constraint violation - child records found.

Every developer must have encountered this error once. There is one approach that we invariably take; turn on “On Delete Cascade” option while creating FK as mentioned briliiantly by Don here. I recently faced the same issue while working on something and to my amazement I found out that there was no “On Delete Cascade” option in FK and I was not allowed to create one. The problem was compounded by the fact that  the parent table has more than 30 child tables and some of the child tables, in turn, have 5 or so grand child tables and even these grand child tables have children of their own.

That’s when I thought of implementing on delete cascade using just DML. The script that I have created is not the best approach, as it is Version 1.0, but it still does the job smoothly and, as one my colleague happily pointed out, lazily.

The script basically works like this:

• Insert the Table name and where clause into stack.
• Get all the child tables and corresponding column(s) for the parent table.
• Form query.
• Delete records from child table using the above query.
• If operation fails, insert that table name and corresponding where clause in stack and repeat from STEP 2.
• If operation doesn't fail and all child records deleted successfully for that parent, then POP that parent and where clause from the stack.
• Repeat the above steps till stack is empty.

The code is fairly simple and easy to understand and would be, in fact, better than my explaination here.

The input parameters to this program is just the table name and the where condition of delete. Complexity of where condition doesn’t really count.

For example,

• To delete an employee with emp id as 1, pass
• Emp
• Emp_id = 1
• To delete all employees of dept 10, pass
• Emp
• Dept_id = 10
• To purge employee table and all it’s child tables, pass
• Emp
• 1 = 1

Third example really shows how lethal this script can be and in fact purge the whole database if the correct where condition is not passed and hence script should be used carefully.

CREATE OR REPLACE PACKAGE PKG_ON_DELETE_CASCADE AS

/***********************************************************

   NAME:    PKG_ON_DELETE_CASCADE

   PURPOSE: This package will be used to implement ON DELETE CASACADE

                   functionality of Oracle.

   REVISIONS:

   Ver         Date          Author           Description

   ---------  ----------     ---------------  ------------------------------------

   1.0        5/25/2011      Prateek          Created this package

***********************************************************/

  /*This Procedure will call ON DELETE CASCADE program in loop

  */

  PROCEDURE SP_MAIN (

                     p_table_name          IN VARCHAR2,

                     p_where_condition     IN VARCHAR2

                    );

                                  

  /*This Procedure will implement ON DELETE CASCADE functionality of

     Oracle using DML.

  */

  PROCEDURE SP_ON_DELETE_CASCADE (

                                  p_table_name          IN OUT VARCHAR2,

                                  p_where_condition     IN OUT VARCHAR2,

                                  p_flag                   OUT BOOLEAN

                                 );

END PKG_ON_DELETE_CASCADE;

/

CREATE OR REPLACE PACKAGE BODY PKG_ON_DELETE_CASCADE AS

/***********************************************************

   NAME:    PKG_ON_DELETE_CASCADE

   PURPOSE: This package will be used to implement ON DELETE CASACADE

                   functionality of Oracle.

   REVISIONS:

   Ver         Date          Author           Description

   ---------  ----------     ---------------  ------------------------------------

   1.0        5/25/2011      Prateek       Created this package

***********************************************************/

  /*This function will execute the delete query formed by SP_ON_DELETE_CASCADE.

     In case of 2292 error (Child record found), it'll return the flag as 0. For

     successful completion, it'll return the flag as 1.

  */

  FUNCTION FN_DELETE_RECORD (

                             p_delete_query       IN VARCHAR2

                            )

  RETURN NUMBER;

 

  PROCEDURE SP_MAIN (

                     p_table_name            IN VARCHAR2,

                     p_where_condition     IN VARCHAR2

                    )

  IS

  /**************************************************************************

   NAME:    SP_MAIN

   PURPOSE: This Procedure will call ON DELETE CASCADE program in loop

    

   PARAMTERES: p_table_name: Parent table name from where records needs to

                             to be deleted.

               p_where_condition: Condition on which record needs to be deleted

               For example, to delete all records from EMP where

               FIRST_NAME is John, the parameters will be

               p_table_name: EMP

               p_where_condition: FIRST_NAME = 'JOHN'

  

   REVISIONS:

   Ver     Date        Author     Description

   -----   ----------  ---------  ----------------------------------

   1.0     5/25/2011   Prateek    Initial Coding                          

 

  **************************************************************************/

  --Local Variable Declaration

  TYPE tab_table_name   IS TABLE OF VARCHAR2(30)    INDEX BY BINARY_INTEGER;

  TYPE tab_where_clause IS TABLE OF VARCHAR2(10000) INDEX BY BINARY_INTEGER;

  rec_table_name     tab_table_name;

  rec_where_clause   tab_where_clause;

 

  ln_row_index       NUMBER := 0;

  lv_table_name      VARCHAR2(30);

  lv_where_condition VARCHAR2(10000);

  lb_flag            BOOLEAN;

  lv_delete_query    VARCHAR2(10000);

 

  BEGIN

   

    --Form the final delete query

    lv_delete_query := 'DELETE FROM ' || p_table_name || ' WHERE ' || p_where_condition;

   

    --Insert the parameters into stack

    ln_row_index := ln_row_index + 1;

    rec_table_name(ln_row_index) := p_table_name;

    rec_where_clause(ln_row_index) := p_where_condition;

   

    --Loop until the ln_row_index is 0.

    WHILE ln_row_index > 0

    LOOP

     

      DBMS_OUTPUT.PUT_LINE('Number of records in stack: ' || ln_row_index);

     

      --Get the table name and where clause from array

      lv_table_name := rec_table_name(ln_row_index);

      lv_where_condition := rec_where_clause(ln_row_index);

     

      DBMS_OUTPUT.PUT_LINE('Deleting child records for ' || lv_table_name);

      DBMS_OUTPUT.PUT_LINE('Using where clause: ' || lv_where_condition);

     

      --Call SP_ON_DELETE_CASCADE

      SP_ON_DELETE_CASCADE (

                            lv_table_name,

                            lv_where_condition,

                            lb_flag

                           );

     

      --If flag is TRUE, then decrement ln_row_index

      IF (lb_flag) THEN

       

        --All child records deleted successfully. Delete the entry from stack.

        rec_table_name.DELETE(ln_row_index);

        rec_where_clause.DELETE(ln_row_index);

       

        --Decrement row index

        ln_row_index := ln_row_index - 1;

       

      ELSE

       

        --There are grandchild records for the child records. Insert an entry into stack.

        ln_row_index := ln_row_index + 1;

       

        rec_table_name(ln_row_index) := lv_table_name;

        rec_where_clause(ln_row_index) := lv_where_condition;

       

      END IF;

     

    END LOOP;

   

    DBMS_OUTPUT.PUT_LINE('Deleting final parent record using query: '|| lv_delete_query);

   

    --Delete parent record.

    EXECUTE IMMEDIATE lv_delete_query;

   

    COMMIT;

   

  EXCEPTION

   

    WHEN OTHERS THEN

       ROLLBACK;

      DBMS_OUTPUT.PUT_LINE('Exception encountered in SP_MAIN: ');

      DBMS_OUTPUT.PUT_LINE(SQLCODE || '  ' || SQLERRM);

      RAISE;

         

  END SP_MAIN;

                                                

  PROCEDURE SP_ON_DELETE_CASCADE (

                                  p_table_name      IN OUT VARCHAR2,

                                  p_where_condition IN OUT VARCHAR2,

                                  p_flag               OUT BOOLEAN

                                 )

  IS

  /**************************************************************************

   NAME:    SP_MAIN

   PURPOSE: This Procedure will call ON DELETE CASCADE program in loop

    

   PARAMTERES: p_table_name: Parent table name from where records needs to

                             to be deleted.

               p_where_condition: Condition on which record needs to be deleted

               For example, to delete all records from EMP where

               FIRST_NAME is John, the parameters will be

               p_table_name: EMP

               p_where_condition: FIRST_NAME = 'JOHN'

  

   REVISIONS:

   Ver     Date        Author           Description

   -----   ----------  ---------------  ----------------------------------

   1.0     5/25/2011   Prateek          Initial Coding                          

 

  **************************************************************************/

  --Local Variable Declaration

  lv_delete_query       VARCHAR2(10000);

  ln_return_code        NUMBER;

  function_error        EXCEPTION;

 

  BEGIN

   

    --Initialize out parameter

    p_flag := TRUE;

   

    --Get all child tables for parent table

    FOR ln_child IN (

                     SELECT PARENT_TABLE,

                            REPLACE(RTRIM (XMLAGG (XMLELEMENT (E, PARENT_COL || ',')).EXTRACT ('//text()'),','), ',', '||''-''||') PARENT_COL,

                            CHILD_TABLE,

                            REPLACE(RTRIM (XMLAGG (XMLELEMENT (E, CHILD_COL || ',')).EXTRACT ('//text()'),','), ',', '||''-''||') CHILD_COL,

                            CONSTRAINT_NAME

                       FROM (SELECT AC.TABLE_NAME CHILD_TABLE,

                                    ACC2.COLUMN_NAME CHILD_COL,

                                    ACC1.TABLE_NAME PARENT_TABLE,

                                    ACC1.COLUMN_NAME PARENT_COL,

                                    AC.CONSTRAINT_NAME

                               FROM ALL_CONSTRAINTS AC,

                                    ALL_CONS_COLUMNS ACC1,

                                    ALL_CONS_COLUMNS ACC2

                              WHERE AC.OWNER = ACC1.OWNER

                                AND AC.OWNER = ACC2.OWNER

                                AND AC.R_CONSTRAINT_NAME = ACC1.CONSTRAINT_NAME

                                AND AC.CONSTRAINT_NAME = ACC2.CONSTRAINT_NAME

                                AND ACC1.POSITION = ACC2.POSITION

                                AND AC.CONSTRAINT_TYPE = 'R'

                                AND AC.OWNER = ‘SCOTT’

                                AND ACC1.TABLE_NAME = p_table_name)

                              GROUP BY PARENT_TABLE, CHILD_TABLE, CONSTRAINT_NAME

                            )

    LOOP

      --Form delete query

      lv_delete_query := 'DELETE FROM '|| ln_child.CHILD_TABLE ||

                         ' WHERE ' || ln_child.CHILD_COL || ' IN ' ||

                         ' ( SELECT ' || ln_child.PARENT_COL || ' FROM ' || ln_child.PARENT_TABLE

                         || ' WHERE ' || p_where_condition || ' ) ';

                                   

      dbms_output.put_line('Query : ' || lv_delete_query);

     

      --Call the function to delete the query

      ln_return_code := FN_DELETE_RECORD(lv_delete_query);

     

      --Check the return code for errors

      IF ln_return_code = -1 THEN

        --Exception in deleting rows

        RAISE function_error;

      ELSIF ln_return_code = 0 THEN

        --Child record found. Exit the loop.

        --Get the table name for which child records found.

        p_table_name := ln_child.CHILD_TABLE;

        p_where_condition := ln_child.CHILD_COL || ' IN ' || ' ( SELECT ' ||

                             ln_child.PARENT_COL || ' FROM ' ||

                             ln_child.PARENT_TABLE ||  ' WHERE ' ||

                             p_where_condition || ' ) ';

       

        --Set flag to FALSE

        p_flag := FALSE;

       

        DBMS_OUTPUT.PUT_LINE('Child records found for table: ' || p_table_name);

        DBMS_OUTPUT.PUT_LINE('New WHERE Condition: ' || p_where_condition);

       

        EXIT;

       

      END IF;

     

    END LOOP;

   

  EXCEPTION

    WHEN FUNCTION_ERROR THEN

      ROLLBACK;

      DBMS_OUTPUT.PUT_LINE('Exception encountered in SP_ON_DELETE_CASCADE: ');

      DBMS_OUTPUT.PUT_LINE(SQLCODE || '  ' || SQLERRM);

      RAISE;

   

    WHEN OTHERS THEN

       ROLLBACK;

      DBMS_OUTPUT.PUT_LINE('Exception encountered in SP_ON_DELETE_CASCADE: ');

      DBMS_OUTPUT.PUT_LINE(SQLCODE || '  ' || SQLERRM);

      RAISE;

         

  END SP_ON_DELETE_CASCADE;

 

  FUNCTION FN_DELETE_RECORD (

                             p_delete_query       IN VARCHAR2

                            )

  RETURN NUMBER

  IS

  /**************************************************************************

   NAME:    FN_DELETE_RECORD

   PURPOSE: This function will execute the delete query formed by

            SP_ON_DELETE_CASCADE. In case of 2292 error (Child record found),

            it'll return the flag as 0. For successful completion, it'll return

            the flag as 1. In other cases, it'll return the flag as -1.

    

   PARAMTERES: p_delete_query: Query to be executed, which will delete the record.

  

   REVISIONS:

   Ver     Date        Author           Description

   -----   ----------  ---------------  ----------------------------------

   1.0     5/25/2011   Prateek          Initial Coding                          

 

  **************************************************************************/

  --Local Variable Declaration

  BEGIN

    --Execute the statement

    EXECUTE IMMEDIATE p_delete_query;

   

    DBMS_OUTPUT.PUT_LINE('Number of rows deleted: ' || SQL%ROWCOUNT );

   

    --For successful completion, return 1

    RETURN 1;

   

  EXCEPTION

    WHEN OTHERS THEN

      IF SQLCODE = -2292 THEN

        DBMS_OUTPUT.PUT_LINE('Child record found.');

        RETURN 0;  

      ELSE

        DBMS_OUTPUT.PUT_LINE('Exception encountered in FN_DELETE_RECORD: ');

        DBMS_OUTPUT.PUT_LINE(SQLCODE || '  ' || SQLERRM);

        RETURN -1;  

        RAISE;     

      END IF;

     

  END FN_DELETE_RECORD;

  

END PKG_ON_DELETE_CASCADE;

So enjoy deleting!!!

Finding Numbers and Special Characters in Alphanumeric Column

Very often than not we need to fetch records on some very common scenarios. Like ensuring the data is alpha or alphanumeric, whether there is any special characters or not.

I use these two queries usually. They come in handy whenever I need to get the job done.

Are column values alphanumeric?

SELECT *

  FROM table_name

WHERE LOWER(column_name) = UPPER(column_name)

Are there any special characters?

SELECT *

  FROM table_name

WHERE LENGTH(REPLACE(TRANSLATE(column_name,'asdfghjklqwertyuiopzxcvbnm1234567890ASDFGHJKLQWERTYUIOPZXCVBNM','                                   '),' ',''))>0

Just re-visiting SQL…