Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> (no subject)

(no subject)

From: Madhavan Amruthur <>
Date: Thu, 03 Apr 2003 09:39:19 -0800
Message-ID: <>


I have been grappling with this for sometime....

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 from 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

-- - Same, same, but different…

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 - 11:39:19 CST

Original text of this message