Home » SQL & PL/SQL » SQL & PL/SQL » Please help for resolving error for PIPELINED table function call (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Please help for resolving error for PIPELINED table function call [message #669332] Mon, 16 April 2018 05:15 Go to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi,

Please help me to resolve the below error.


//Package spec
create or replace package perf_imp
as
   TYPE t_row IS RECORD
   ( 
     col1 number(9,0),
     col2 number(12,0)
   );
   TYPE t_tab IS TABLE OF t_row;
   
   FUNCTION get_tab(p_col_1 number)
   RETURN t_tab PIPELINED;
end;
/


//Package Body

create or replace package body perf_imp
as
  FUNCTION get_tab(p_col_1 number)
  RETURN t_tab PIPELINED
  IS
    l_row t_row;
    CURSOR C1
    IS
      SELECT   col1,col2
      FROM     tab1
      where    col1 = p_col_1;
      
   BEGIN
     
      FOR r1 IN c1
      LOOP
      
         l_row.col1 := r1.col1;
         l_row.col2 := r1.col2;
      
      END LOOP;
  RETURN;
 END;  
END;

Executing query:

select  *
from    table(perf_imp.get_tab(10));

Error:

ORA-04063: package body "DNA_OWNER.PERF_IMP" has errors
04063. 00000 -  "%s has errors"
*Cause:    Attempt to execute a stored procedure or use a view that has
           errors.  For stored procedures, the problem could be syntax errors
           or references to other, non-existent procedures.  For views,
           the problem could be a reference in the view's defining query to
           a non-existent table.
           Can also be a table which has references to non-existent or
           inaccessible types.
*Action:   Fix the errors and/or create referenced objects as necessary.

Re: Please help for resolving error for PIPELINED table function call [message #669333 is a reply to message #669332] Mon, 16 April 2018 05:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That error means that perf_imp has compilation errors.
Query user_errors to see what thwy are.

Also you're missing a call to pipe row.
Re: Please help for resolving error for PIPELINED table function call [message #669335 is a reply to message #669333] Mon, 16 April 2018 05:51 Go to previous message
msol25
Messages: 396
Registered: June 2011
Senior Member
Thanks Cookie,

After adding "PIPE ROW(l_row)" error has been resolved.

Smile
Previous Topic: Need Help In Plsql Procedure
Next Topic: modification date for package SP
Goto Forum:
  


Current Time: Thu Mar 28 04:11:09 CDT 2024