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

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.

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

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.

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.