Forum Stats

  • 3,759,476 Users
  • 2,251,549 Discussions
  • 7,870,670 Comments

Discussions

error in executing the function

Smile
Smile Member Posts: 762 Bronze Badge
edited Jan 2, 2009 1:21AM in SQL & PL/SQL
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

Answers

  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    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.
  • OrionNet
    OrionNet Member Posts: 4,542
    edited Jan 2, 2009 1:16AM
    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
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    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.
    Satyaki_De
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    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.
This discussion has been closed.