4 Replies Latest reply on Nov 1, 2012 4:09 PM by ChakravarthyDBA

    How to call a procedure from another procedure with IN and OUT parameters

    ChakravarthyDBA
      Hi Here is the my procedure with parameters

      create or replace
      PROCEDURE AUTOALLOTSHIFTS_PROC
      (
      P_SHIFTPATTERNCODE IN VARCHAR2,
      P_EMPNO IN VARCHAR2,
      P_STARTDATE IN DATE,
      P_CREATEDBY IN VARCHAR2,
      P_INSORUPD IN NUMBER,
      CUR_OUT OUT SYS_REFCURSOR
      )

      how can call this procedure with my parameters, I want to call the procedure with 5 IN parameters and 1 cursor out parameters how can I get the out variable result.
        • 1. Re: How to call a procedure from another procedure with IN and OUT parameters
          Osama_Mustafa
          Check
          http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_pc.htm
          http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_app_dbms_aw026.htm
          1 person found this helpful
          • 2. Re: How to call a procedure from another procedure with IN and OUT parameters
            rp0428
            >
            Hi Here is the my procedure with parameters

            create or replace
            PROCEDURE AUTOALLOTSHIFTS_PROC
            (
            P_SHIFTPATTERNCODE IN VARCHAR2,
            P_EMPNO IN VARCHAR2,
            P_STARTDATE IN DATE,
            P_CREATEDBY IN VARCHAR2,
            P_INSORUPD IN NUMBER,
            CUR_OUT OUT SYS_REFCURSOR
            )

            how can call this procedure with my parameters, I want to call the procedure with 5 IN parameters and 1 cursor out parameters how can I get the out variable result.
            >
            Just define the variables locally, call the procedure and loop thru the results.
            SET SERVEROUTPUT ON SIZE 1000000
            DECLARE
              v_SHIFTPATTERNCODE VARCHAR2(4000);
              v_EMPNO VARCHAR2(4000);
              v_STARTDATE DATE;
              v_CREATEDBY VARCHAR2(4000);
              v_INSORUPD NUMBER;
              vCUR_OUT SYS_REFCURSOR;
            BEGIN
               AUTOALLOTSHIFTS_PROC (v_SHIFTPATTERNCODE, v_EMPNO IN VARCHAR2,
                                                      v_STARTDATE IN DATE, v_CREATEDBY IN VARCHAR2,
                                                      v_INSORUPD IN NUMBER, vCUR_OUT OUT SYS_REFCURSOR
            )
                        
              LOOP 
                FETCH l_cursor
                EXIT WHEN l_cursor%NOTFOUND;
                -- process cursor here
              END LOOP;
              CLOSE l_cursor;
            END;
            /
            • 3. Re: How to call a procedure from another procedure with IN and OUT parameters
              Gaurav Bhide
              Hi,

              Below code is working in HR schema. You can try this one.
              create or replace procedure test_a
              (
                p_dept_id      in  varchar2,
                p_man_id       in  varchar2,
                p_job_id       in  varchar2,
                p_sal          in  varchar2,
                p_hire_date    in  varchar2,
                p_cur_ptr      out sys_refcursor
              )
              as
              
              begin
                
                  open p_cur_ptr for select last_name , job_id , salary 
                                     from   employees
                                     where  department_id = p_dept_id
                                     and    manager_id    = p_man_id
                                     and    job_id        = p_job_id
                                     and    p_sal         > 1000
                                     and    to_date(p_hire_date,'dd-mon-yyyy')   < sysdate;
              end;
              /
              show errors
              
              
              create or replace procedure test_b
              as
                v_last_name     employees.last_name%type;
                v_job_id        employees.job_id%type;
                v_sal           employees.salary%type;
                
                cur_ptr         sys_refcursor;
              
              begin
              
                test_a('50','103','SA_REP','30000','01-JAN-1982',cur_ptr);
                
                loop
                fetch cur_ptr into v_last_name , v_job_id , v_sal;
                exit when cur_ptr%notfound;
                
                    dbms_output.put_line(v_last_name ||'     '|| v_job_id ||'       '|| v_sal);
                
                end loop;
                
              end test_b;
              /
              show errors