4 Replies Latest reply on Jan 2, 2009 6:21 AM by Satyaki_De

    error in executing the function

    Smile
      hi I tried the following function , FUNCTION is creating fine but getting the error while executing...
      SQL> create or replace FUNCTION getPmnOrgRow(empId emp.EMPNO%TYPE)
        2    RETURN EMP%ROWTYPE
        3    IS
        4    result EMP%ROWTYPE;
        5   BEGIN
        6    SELECT *
        7    INTO result
        8    FROM EMP
        9    WHERE EMPNO = empId;
       10    RETURN result;
       11   EXCEPTION
       12    WHEN NO_DATA_FOUND THEN
       13     raise_application_error(-20100,'error');
       14     RETURN NULL;
       15   END;
       16  /
      
      Function created.
      
      SQL> select getPmnOrgRow(7566) from dual;
      select getPmnOrgRow(7566) from dual
             *
      ERROR at line 1:
      ORA-00902: invalid datatype
        • 1. Re: error in executing the function
          Satyaki_De
          Problem lies here ->
          RETURN EMP%ROWTYPE
          You cannot return more than one value through a function in a SQL statement. But, you can get those values in PL/SQL.

          So, it is generating the error - i guess.

          Try to return one column instead of the entire record and see what happens.

          Regards.

          Satyaki De.
          • 2. Re: error in executing the function
            OrionNet
            Hello,

            You can also execute your function like this
            DECLARE
               retval   emp.name%ROWTYPE;    empid    NUMBER;
            BEGIN
               empid    := 1;
            
               retval   := getpmnorgrow (empid);
               DBMS_OUTPUT.put_line (retval.id);
               
               DBMS_OUTPUT.PUT_LINE(retval.NAME);
               COMMIT;
            END;
            Regards

            Edited by: OrionNet on Jan 2, 2009 1:16 AM
            • 3. Re: error in executing the function
              Satyaki_De
              Pipelined function will work for you ->
              satyaki>
              satyaki>select * from v$version;
              
              BANNER
              ----------------------------------------------------------------
              Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
              PL/SQL Release 10.2.0.3.0 - Production
              CORE    10.2.0.3.0      Production
              TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
              NLSRTL Version 10.2.0.3.0 - Production
              
              Elapsed: 00:00:00.18
              satyaki>
              satyaki>
              satyaki>create or replace type pipe_sl as object
                2    (
                3       empno   number(5),
                4       ename   varchar2(30),
                5       sal     number(7,2),
                6       hr_dt   date
                7    );
                8  /
              
              Type created.
              
              Elapsed: 00:00:05.08
              satyaki>
              satyaki>create or replace type pipe_sl_rec as table of pipe_sl;
                2  /
              
              Type created.
              
              Elapsed: 00:00:00.23
              satyaki>
              satyaki>create or replace function pi_pe_sel(empId emp.EMPNO%TYPE)
                2  return pipe_sl_rec
                3  pipelined 
                4  is
                5    cursor c1
                6    is
                7      select pipe_sl(
                8                       empno,
                9                       ename,
               10                       sal,
               11                       hiredate
               12                    ) data
               13      from emp
               14      WHERE EMPNO = empId;
               15      
               16    r1 c1%rowtype;
               17  begin
               18    for r1 in c1
               19    loop
               20      pipe row(r1.data);
               21    end loop;
               22    
               23    return;
               24  end;
               25  /
              
              Function created.
              
              Elapsed: 00:00:00.63
              satyaki>
              satyaki>
              satyaki>select * from table(cast(pi_pe_sel(7369) as pipe_sl_rec));
              
              no rows selected
              
              Elapsed: 00:00:00.77
              satyaki>
              satyaki>
              satyaki>select * from emp;
              
                   EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
              ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                    9999 SATYAKI    SLS             7698 02-NOV-08      55000       3455         10
                    7777 SOURAV     SLS                  14-SEP-08      45000       3400         10
                    7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
                    7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                    7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
                    7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
                    7782 CLARK      MANAGER         7839 09-JUN-81       4450                    10
                    7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
                    7839 KING       PRESIDENT            17-NOV-81       7000                    10
                    7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
                    7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
              
                   EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
              ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                    7900 JAMES      CLERK           7698 03-DEC-81        950                    30
                    7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
              
              13 rows selected.
              
              Elapsed: 00:00:00.31
              satyaki>select * from table(cast(pi_pe_sel(9999) as pipe_sl_rec));
              
                   EMPNO ENAME                                 SAL HR_DT
              ---------- ------------------------------ ---------- ---------
                    9999 SATYAKI                             55000 02-NOV-08
              
              Elapsed: 00:00:00.15
              satyaki>
              Regards.

              Satyaki De.
              1 person found this helpful
              • 4. Re: error in executing the function
                Satyaki_De
                I've already told that.
                But, you can get those values in PL/SQL.
                Here, i've mentioned that in that reply.

                Regards.

                Satyaki De.