Advertisement

07.03.2008 at 11:16AM PDT, ID: 23537747
[x]
Attachment Details

Performance Issue

Asked by mkmunir70 in Oracle 10.x

Tags: ,

I need to improve the performance of this procedure. I will appreciate any suggestions.

    Procedure GET_USER_ACCESS_TOOBJECT(p_user_name_i             IN   xxccs_caam_object_access_info.target_user_id%TYPE,
                                       p_obj_access_info_tab_i   IN   xxccs_caam_obj_access_tab_type) IS    

        l_flag                        BOOLEAN := FALSE;
        l_user_id                     fnd_user.user_id%TYPE;
                       
    BEGIN
         IF p_user_name_i IS NOT NULL THEN
          l_user_id := xxccs_caam_appl_pub.get_valid_user_id(p_user_name_i);
            IF l_flag = TRUE THEN
            xxccs_fea_utl_log.error('Unknow User exception while accessing GET_LIST_OBJ_ACCESSIBLE API: ('
             ||SQLCODE || '): (' || SQLERRM || ')');
            xxccs_fea_utl_exception.throw ('XXCCS_CAAM_UNKNOWN_USER','XXCCS_CAAM');
            ELSIF l_flag = FALSE THEN
                IF p_obj_access_info_tab_i.EXISTS(1) THEN
                    FOR i IN p_obj_access_info_tab_i.FIRST .. p_obj_access_info_tab_i.LAST
                    LOOP
                        FOR j IN (SELECT xcssam.apllication_short_name,
                                         xcssam.object_number,
                                         xcssam.object_type,
                                         xcssam.target_user_id,
                                         xcssam.grantor_user_id,
                                         xcssam.creator_user_id,
                                         xcssam.owner_user_id,
                                         xcssam.object_privilege,
                                         NULL action,
                                         xcssam.attribute_n1,
                                         xcssam.attribute_n2,
                                         xcssam.attribute_n3,
                                         xcssam.attribute_n4,
                                         xcssam.attribute_n5,
                                         xcssam.attribute_c1,
                                         xcssam.attribute_c2,
                                         xcssam.attribute_c3,
                                         xcssam.attribute_c4,
                                         xcssam.attribute_c5,
                                         xcssam.status,
                                         xcssam.notes,
                                         xcssam.access_start_date,
                                         xcssam.access_end_date
                                    FROM xxccs_caam_object_access_info xcssam
                                   WHERE xcssam.object_number = p_obj_access_info_tab_i(i).object_number
                                     AND xcssam.object_type = p_obj_access_info_tab_i(i).object_type
                                     AND xcssam.target_user_id = l_user_id
                                     AND NVL(xcssam.access_end_date,
                                             SYSDATE) >= SYSDATE)
                        LOOP
                        dbms_output.put_line('In the Loop for Insert.');
                               INSERT INTO get_user_access_toobject
                                  VALUES (j.apllication_short_name,
                                          j.object_number,
                                          j.object_type,
                                          j.target_user_id,
                                          j.grantor_user_id,
                                          j.creator_user_id,
                                          j.owner_user_id,
                                          j.object_privilege,
                                          j.action,
                                          j.attribute_n1,
                                          j.attribute_n2,
                                          j.attribute_n3,
                                          j.attribute_n4,
                                          j.attribute_n5,
                                          j.attribute_c1,
                                          j.attribute_c2,
                                          j.attribute_c3,
                                          j.attribute_c4,
                                          j.attribute_c5,
                                          j.status,
                                          j.notes,
                                          j.access_start_date,
                                          j.access_end_date);
                        END LOOP;
                    END LOOP;
                END IF;
            END IF;
         ELSEStart Free Trial
[+][-]07.03.2008 at 11:50AM PDT, ID: 21928103

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 12:24PM PDT, ID: 21928346

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 12:31PM PDT, ID: 21928382

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 01:42PM PDT, ID: 21928857

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 01:57PM PDT, ID: 21928990

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 02:08PM PDT, ID: 21929076

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 02:24PM PDT, ID: 21929242

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 03:35PM PDT, ID: 21929910

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 03:38PM PDT, ID: 21929927

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.08.2008 at 12:09AM PDT, ID: 21951539

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Oracle 10.x
Tags: PL/SQL, Explorer
Sign Up Now!
Solution Provided By: gajmp
Participating Experts: 5
Solution Grade: B
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628