pl/sql query which takes deptno from emp as input and return ename, sal all the columns from dept table

Question

Can anyone please help me in solving this.

I need to write a pl/sql query which takes deptno from emp as input and returns ename, sal plus all the columns from dept table.(I don't want to declare the columns individually, instead I want to use %rowtype).

I have tried it in several ways. one of them is mentioned below. Creation--

    CREATE OR REPLACE PROCEDURE PROC01(
       DNO EMP.DEPTNO%TYPE, 
       REC OUT DEPT%ROWTYPE, 
       NAME OUT EMP.ENAME%TYPE, 
       SAL OUT EMP.SAL%TYPE
       , MY_CURSOR OUT SYS_REFCURSOR)
    IS 
    BEGIN
        OPEN MY_CURSOR FOR 
             SELECT D.*, ENAME, SAL  
             FROM EMP, DEPT D 
             WHERE EMP.DEPTNO = D.DEPTNO 
            AND D.DEPTNO = DNO;
    END PROC01;
    /

--Declare & Execution
    DECLARE
      REC DEPT%ROWTYPE;
      NAME EMP.ENAME%TYPE;
      SAL EMP.SAL%TYPE;
      MY_CURSOR SYS_REFCURSOR;
    BEGIN
      PROC01(&DNO,REC, NAME, SAL, MY_CURSOR);
      LOOP
        FETCH MY_CURSOR INTO REC, NAME, SAL;
        EXIT WHEN MY_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(REC.DNAME||'  '||NAME||'  '||SAL);
      END LOOP;
      CLOSE MY_CURSOR;
    END;
    /

ERROR :-- expression 'REC' in the INTO list is of wrong type Thanks in advance...

Answer 1

The error is because of oracle can't fetch cursor in both rec type and normal type together. I changed your query and it's working NOW!

Procedure :

CREATE OR REPLACE PROCEDURE PROC01(DNO IN emp.deptno%TYPE,
                                         /*REC OUT DEPT%ROWTYPE,
                                         NAME OUT EMP.ENAME%TYPE,
                                         SAL OUT EMP.SAL%TYPE,*/
                                         MY_CURSOR OUT SYS_REFCURSOR) IS
BEGIN
  OPEN MY_CURSOR FOR
    SELECT D.*, ENAME, SAL
      FROM EMP, DEPT D
     WHERE EMP.DEPTNO = D.DEPTNO
       AND D.DEPTNO = DNO;
END PROC01;

Test code :

DECLARE
  REC       DEPT%ROWTYPE;
  DEPTNO    DEPT.deptno%TYPE;
  DNAME     DEPT.dname%TYPE;
  LOC       DEPT.loc%TYPE;
  NAME      EMP.ENAME%TYPE;
  SAL       EMP.SAL%TYPE;
  MY_CURSOR SYS_REFCURSOR;
BEGIN
  PROC01(&DNO, /*REC, NAME, SAL,*/ MY_CURSOR);
  LOOP
    FETCH MY_CURSOR
      INTO /*REC*/ DEPTNO, DNAME, LOC, NAME, SAL;
    EXIT WHEN MY_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( /*REC.*/ DNAME || '  ' || NAME || '  ' || SAL);
  END LOOP;
  CLOSE MY_CURSOR;
END;