I want to write two plsql procedures. Get data in one procedure and print it from the second procedure

Question

I need a list of values to be fetched from a table in one procedure and then the values to be passed to a second procedure. For ex. In A.prc I need to fetch data from a table and in B.prc I need to print the data that I fetched in A.prc.

Thanks in Advance

P.S. : Using Oracle 11g as DB with sys priv and Toad to write the prc's

    CREATE OR REPLACE PROCEDURE P1(
       EMPNO OUT EMP.EMPNO%type, 
       ENAME OUT EMP.ENAME%type, 
       DEPTNO OUT EMP.DEPTNO%type)
    AS
    C_EMP SYS_REFCURSOR;  
    C_EM VARCHAR2(200);
    BEGIN 
    C_EM:='SELECT EMPNO,ENAME,DEPTNO FROM EMP';
    OPEN C_EMP FOR C_EM; 
       LOOP 
       FETCH C_EMP into EMPNO,ENAME,DEPTNO; 
          EXIT WHEN C_EMP%notfound; 
        END LOOP; 
       P2(C_EMP);
       CLOSE C_EMP; 
    END; 
    /

    CREATE OR REPLACE PROCEDURE P2(e_EMP SYS_REFCURSOR) AS
    BEGIN 
    LOOP 
    FETCH e_EMP INTO E_EMPNO,E_ENAME,E_DEPTNO;
    EXIT WHEN e_EMP%NOTFOUND; 
    END LOOP; 
    CLOSE e_EMP; 
    END; 
    /

Error : [Error] PLS-00306 (17: 4): PLS-00306: wrong number or types of arguments in call to 'P2'

Update 1: Also need to do this without a cursor, with an associative array. This is a part of assignment/homework. Tried this with array:

    CREATE OR REPLACE PROCEDURE P1
    AS
    TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
    emp_tab EmpTabTyp;
    BEGIN
    SELECT * INTO emp_tab FROM emp;
    END;
    /

[Error] PLS-00597 (6: 15): PLS-00597: expression 'EMP_TAB' in the INTO list is >of wrong type [Error] ORA-00904 (6: 23): PL/SQL: ORA-00904: : invalid identifier

Answer 1

You on the right track, however I think it could be done a little bit more simple.
I hope my example would give you an idea of how to solve it.
For example:

CREATE OR REPLACE PROCEDURE P2 (nId IN NUMBER, vName IN VARCHAR2) 
AS
BEGIN
        DBMS_OUTPUT.PUT_LINE('Output nId: ' || nId || ' vName: ' || vName);
END;
/

CREATE OR REPLACE PROCEDURE P1
AS
       CURSOR c1 AS
              SELECT Id, Name FROM TableA;
BEGIN
       FOR r1 IN c1 LOOP
               P2(nId => r1.Id, vName => r1.Name);
       END LOOP;
END;
/

I also would suggest to have a another look on how IN and OUT parameters work, becasue you are using them in a wrong way. But that would would be a whole different topic. :-)

To pass a cursor line to a procedure you could send the record:
For example:

CREATE OR REPLACE PROCEDURE P2 (r1 IN TableA%ROWTYPE) 
AS
BEGIN
        DBMS_OUTPUT.PUT_LINE('Output nId: ' || r1.nId || ' vName: ' || r1.vName);
END;
/

CREATE OR REPLACE PROCEDURE P1
AS
       CURSOR c1 AS
              SELECT Id, Name FROM TableA;
BEGIN
       FOR r1 IN c1 LOOP
               P2(r1 => r1);
       END LOOP;
END;
/