2 Replies Latest reply: Jun 13, 2013 9:23 AM by rp0428 RSS

    Stored procedure with use of Object datatype.

    1013527

      I created

      CREATE OR REPLACE TYPE empres_OBJ AS OBJECT( i_x_emp_res_id       NUMBER,

                                 i_person_cand_id        NUMBER,

                                 i_person_cand_flag      NUMBER,

                                 i_custom_resource_flag  NUMBER,

                                 i_loc_id                NUMBER,

                                 i_group_id              NUMBER,

                                 i_resource_id           NUMBER,

                                 i_assigned_jira         VARCHAR2(250),

                                 i_revoked_jira          VARCHAR2(250),

                                 i_comments              VARCHAR2(250),

                                 i_requested_by          NUMBER,

                                 i_resource_status_id    NUMBER,

                                 i_action                VARCHAR2(1));

      And table of that object

      CREATE OR REPLACE TYPE empres_OBJ_ARRAY IS TABLE OF empres_OBJ;

      like this.

      Now i want to create stored procedure and bulk insert or update based on action.

      Can you please suggest me what is good way for that?

        • 1. Re: Stored procedure with use of Object datatype.
          1013527

          I created store procedure as below but i am getting an error - IACTION must be defined.

          Can you please suggest me how to use object in SP?

           

          PROCEDURE ins_emp_resources(p_obj_array in empres_OBJ_ARRAY)

           

          IS

          l_resource_group_id NUMBER;

          l_group_loc_id NUMBER;

          l_emp_start_date DATE;

          BEGIN

           

          FOR i in 1 .. p_obj_array.COUNT

          LOOP

           

              IF p_obj_array.i_action(i) = 'I' THEN

                 

                  SELECT resource_group_id into l_resource_group_id

                    FROM ems.resources_group rsg

                   WHERE rsg.resource_id = p_obj_array.i_resource_id(i)

                     AND rsg.group_id = p_obj_array.i_group_id(i);

                    

                  SELECT group_loc_id into l_group_loc_id

                    FROM ems.groups_loc gl

                   WHERE gl.loc_id = p_obj_array.i_loc_id(i)

                     AND gl.group_id = p_obj_array.i_group_id(i);  

                     

                   IF i_person_cand_flag(i) = 0 THEN--Person_id from from employee table

                     

                      SELECT  emp.hire_date INTO l_emp_start_date

                        FROM  ems.employee emp

                       WHERE  emp.person_id = p_obj_array.i_person_cand_id(i)

                         AND  emp.actual_termination_date IS NULL;

                       

                        INSERT INTO ems.employee_resources (person_id,

                                                            emp_start_date,

                                                            custom_resource_flag,

                                                            assigned_jira,

                                                            resource_status_id,

                                                            resource_group_id,

                                                            group_loc_id,

                                                            comments)

                                                     VALUES(p_obj_array.i_person_cand_id(i),

                                                            l_emp_start_date,

                                                            p_obj_array.i_custom_resource_flag(i),

                                                            p_obj_array.i_assigned_jira(i),

                                                            p_obj_array.i_resource_status_id(i),

                                                            l_resource_group_id,

                                                            l_group_loc_id,

                                                            p_obj_array.i_comments(i));

                                                                       

                   ELSE --candidate_id from ems_candidate

                      

                      SELECT  DISTINCT NVL(cand.start_date,cand.contact_date) INTO l_emp_start_date

                        FROM  ems.ems_candidate cand

                       WHERE  cand.ems_candidate_id = i_person_cand_id;

           

                        INSERT INTO ems.employee_resources (candidate_id,

                                                            emp_start_date,

                                                            custom_resource_flag,

                                                            assigned_jira,

                                                            resource_status_id,

                                                            resource_group_id,

                                                            group_loc_id,

                                                            comments)

                                                     VALUES(p_obj_array.i_person_cand_id(i),

                                                            l_emp_start_date,

                                                            p_obj_array.i_custom_resource_flag(i),

                                                            p_obj_array.i_assigned_jira(i),

                                                            p_obj_array.i_resource_status_id(i),

                                                            l_resource_group_id(i),

                                                            l_group_loc_id(i),

                                                            p_obj_array.i_comments(i));

                   END IF;   

              

            ELSIF p_obj_array.i_action(i) = 'U' THEN  --Status = Completed OR Revoked

            

                   IF p_obj_array.i_person_cand_flag(i) = 0 THEN--Person_id from from employee table

                       

                        UPDATE ems.employee_resources

                           SET assigned_jira = p_obj_array.i_assigned_jira(i),

                               completed_by = decode(p_obj_array.i_resource_status_id(i),3,p_obj_array.i_requested_by(i),NULL),

                               completed_on = decode(p_obj_array.i_resource_status_id(i),3,sysdate,NULL),

                               comments     = p_obj_array.i_comments(i),

                               revoked_jira = decode(p_obj_array.i_resource_status_id(i),2,p_obj_array.i_revoked_jira(i),NULL),

                               revoked_on = decode(p_obj_array.i_resource_status_id(i),2,sysdate,NULL),

                               updated_date = sysdate

                         WHERE emp_res_id = p_obj_array.i_x_emp_res_id(i)

                           AND person_id = p_obj_array.i_person_cand_id(i); 

                          

                   ELSE --candidate_id from ems_candidate

                      

                        UPDATE ems.employee_resources

                           SET assigned_jira = p_obj_array.i_assigned_jira(i),

                               completed_by = decode(p_obj_array.i_resource_status_id(i),3,p_obj_array.i_requested_by(i),NULL),

                               completed_on = decode(p_obj_array.i_resource_status_id(i),3,sysdate,NULL),

                               comments     = p_obj_array.i_comments(i),

                               revoked_jira = decode(p_obj_array.i_resource_status_id(i),2,p_obj_array.i_revoked_jira(i),NULL),

                               revoked_on = decode(p_obj_array.i_resource_status_id(i),2,sysdate,NULL),

                               updated_date = sysdate

                         WHERE emp_res_id = p_obj_array.i_x_emp_res_id(i)

                           AND candidate_id = p_obj_array.i_person_cand_id(i);

                        

                   END IF; 

              

            END IF;  

           

           

          END LOOP;

           

          END ins_emp_resources; 

          • 2. Re: Stored procedure with use of Object datatype.
            rp0428

            Now i want to create stored procedure and bulk insert or update based on action.

            Can you please suggest me what is good way for that?

             

            Why? PL/SQL will ALWAYS be slower than using SQL.

             

            Your code is NOT using BULK anything. You are using slow-by-slow (row by row) processing and have made things just about as slow as they can be.

             

            I suggest you abandon this approach and just use plain SQL to do your DML.

             

            See the FORALL statement in the SQL Language doc

            http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/forall_statement.htm

             

            And see how to use collections in the 'Using PL/SQL Collections' section of the PL/SQL Language doc

            http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm

             

            Most every collection reference in your code is wrong.

             

            IF p_obj_array.i_action(i) = 'I' THEN

             

            'i_action' is NOT the collection but you are trying to reference it as if it was.

             

            'p_obj_array' IS the collection but you are NOT referencing it by using an index.

             

            The index needs to be used on the collection, not on the attribute like this:

             

            IF p_obj_array(i).i_action = 'I' THEN