Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> SQL Question

SQL Question

From: Madhavan Amruthur <>
Date: Thu, 03 Apr 2003 10:14:47 -0800
Message-ID: <>

Sorry for reposting.
Just wanted to put in a subject...

I have been grappling with this for sometime and thought it will be best for others to take a look at it.

I have a table a_user_groups


---------- ----------------- ----------
      1005              1012       1010
      1005              1012       1011
      1006              1013       1010
      1007              1017       1016
      1008              1018       1010
      1008              1018       1011

The security_group_id currently is uniquely generated every time a user is added and a group_id is associated with the user_id. For eg: user_id 1005 is associated with groups 1010 and 1011. User 1008 has the same combination but the security_group_id is generated differently.
The generation happens for C code and there is an option to correct the problem in the C code but I am trying to see if I can prevent that

The requirement is that user_id 1008 and any other users with the same group_id combination should have the same security_group_id 1012, basically the first occurrence for the combination. In the case of user_id 1006 the value for security_group_id is 1013 and the group_id the user_id belongs to is 1016. So all the user_ids with a group_id association
of 1016 (1016 in a combination does not count) down the line will have to be updated to 1013.

I found a solution for the case where I associate a group to a user_id in this existing table by creating another table that converted the above table into a hierarchy

Table b_hier_user_groups

   USER_ID CGID PARENT_VALUE CHILD_VALUE ---------- ---------- ------------ -----------

      1005       1012                     1010
      1005       1012         1010        1011
      1006       1013                     1010
      1007       1017                     1016
      1008       1018                     1010
      1008       1018         1010        1011

Then using a PL/SQL script I generated the tree using sys_connect_by_path I determined if the user had a path that already. For eg: in the above case if the user 1006 was being associated with group_id 1011, then I would check the exsiting trees to see if there was a path already as in 1010,1011.
In this case it does exist and the cgid (equivalent to security_group_id in the above table) is 1012 and update the user_id 1006 to cgid 1012. But I am not for some reason able
to apply this solution to the existing rows. I have a feeling that I am missing something simple....

The requirement that I am grappling with is to update the values in the existing table. I can get the table b_hier_user_groups created from the a_user_groups.

Please let me know if you need more information The table structures are as below


Name                                      Null?    Type
 ----------------------------------------- --------
 USER_ID                                   NOT NULL NUMBER
 SECURITY_GROUP_ID                         NOT NULL NUMBER
 GROUP_ID                                  NOT NULL NUMBER

Table b_hier_user_groups

Name                                      Null?    Type
 ----------------------------------------- --------
 USER_ID                                            NUMBER
 CGID  ---> same as security_Group_id from above)   NUMBER
 PARENT_VALUE                                       NUMBER
 CHILD_VALUE                                        NUMBER

Thanks for your time and help in advance. Regards,


Madhavan Amruthur
DecisionPoint Applications

-- - I mean, what is it about a decent email service?

Please see the official ORACLE-L FAQ:

Author: Madhavan Amruthur

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Apr 03 2003 - 12:14:47 CST

Original text of this message