1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

procedure calling procedure using object type reg

Discussion in 'SQL PL/SQL' started by laxman, Jan 12, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    dear all,
    i need to test one procedure by passing only one value but how do i pass single value. i am showing the details of few section on which i am working on. here is few details about the package.

    Description: package pkj_emp contains two procedure pkj_emp and procedure proc_rem.

    purpose:based on passing dname values to procedure pkj_emp, cursor cur_emp will fetch empid from emp table and then we are passing 4 empid records to procedure proc_rem using empid object type.Inside the procedure proc_emp it will delete all 4 records of table A,B,C an D at one short.

    Requirement:i need to test for only one value that means is it possible i can pass only one value using the cursor cur_emp.

    Code (SQL):
    CREATE OR REPLACE package pkj_emp
    (
       TYPE obj_emp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
       PROCEDURE proc_emp(empid obj_emp);
    );

    CREATE OR REPLACE package body pkj_emp
    AS
    PROCEDURE(
                    dname varchar2;
                 )
    AS
              cursor cur_emp IS SELECT emp_id FROM emp a,dept d
                          WHERE a.deptid=d.deptid
                           AND d.deptname=dname;
    BEGIN
       
                             COUNT:=0;
                 FOR cur_emp_rec IN cur_emp LOOP
                 empid(COUNT) := cur_emp_rec.emp_id;
           
                    IF (COUNT = 4) THEN
                proc_rem(empid); // calling another PROCEDURE
                commit;
                END IF;
                 COUNT := COUNT + 1;
                 END LOOP;
    END;
                   
       
    )

    proc_rem(
                  empid obj_emp;
                 )
    IS
          BEGIN
         DELETE FROM A WHERE emp_id IN (empid(0),empid(1),empid(2),empid(3));
         DELETE FROM B WHERE emp_id IN (empid(0),empid(1),empid(2),empid(3));
         DELETE FROM c WHERE emp_id IN (empid(0),empid(1),empid(2),empid(3));
         DELETE FROM d WHERE emp_id IN (empid(0),empid(1),empid(2),empid(3));
        END;
    regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot test one record because you've coded to expect four and you'll never get to call your procedure. Why you're not bulk loading and using forall to process the list is a mystery:

    Code (SQL):
    CREATE OR REPLACE package pkj_emp IS

       TYPE obj_emp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
       PROCEDURE proc_emp(dname IN varchar2);
       PROCEDURE proc_rem(empid IN obj_emp);

    END pkj_emp;
    /


    CREATE OR REPLACE package body pkj_emp
    AS
    PROCEDURE proc_emp(
                    dname IN varchar2
                 )
    AS
              cursor cur_emp IS SELECT emp_id FROM emp a,dept d
                          WHERE a.deptid=d.deptid
                           AND d.deptname=dname;

        empid obj_emp;
    BEGIN

        OPEN cur_emp_rec;
        fetch cur_emp_rec bulk collect INTO empid;
        close cur_emp_rec;
     
        pkj_emp.proc_rem(empid); // calling another PROCEDURE
        commit;
    END;
               
       
    PROCEDURE proc_rem(
                  empid obj_emp;
                 )
    IS
       BEGIN
        forall i IN empid.FIRST..empid.LAST
             DELETE FROM A WHERE emp_id = empid(i);
        forall i IN empid.FIRST..empid.LAST
             DELETE FROM B WHERE emp_id = empid(i);
        forall i IN empid.FIRST..empid.LAST
             DELETE FROM c WHERE emp_id = empid(i);
        forall i IN empid.FIRST..empid.LAST
             DELETE FROM d WHERE emp_id = empid(i);
       END;
    END pkj_emp;
    /
    Aside from correcting the syntax errors this also should be more efficient and should process 1 or more records without issue.

    The above code is untested so there may still be a syntax error lurking within.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    It seems a well defined and better approach than previous one.
    Heartfully thanks to you sir,let me try it out and get back to you with the desired result.


    Thanks n regards
    Laxman:)