Home » SQL & PL/SQL » SQL & PL/SQL » Pagination (Oracle 12 C)
Pagination [message #669413] Sun, 22 April 2018 13:32 Go to next message
satya_ora
Messages: 4
Registered: March 2018
Junior Member
I am trying to do pagination in a function in Oracle 12C but getting ambiguous column name error but same sql runs successfully.
Please suggest how can i implement pagination.

Below are the sample code

create or replace TYPE  EMP_DTL_TYPE as Object
(
	EMP_ID			VARCHAR2(255)	,
	EMP_NAME		VARCHAR2(255)	,
	DEPT_ID			VARCHAR2(255)
);
/

CREATE OR REPLACE TYPE emp_dtl_TBL as table of EMP_DTL_TYPE;
/

create or replace PACKAGE pkg_emp_dtl AS
    
      function get_emp_dtl(p_emp_id  IN    VARCHAR2 default NULL
                           ,p_dept_id IN    VARCHAR2 default NULL
			   , p_pg_num IN NUMBER default 0
                           )                                 
                          return emp_dtl_TBL pipelined;
          
END pkg_emp_dtl;
/

create or replace PACKAGE BODY pkg_emp_dtl  AS

       -------------------- Main Function to call all other functions to get transactions ----------------------------------------------------
       
          function get_emp_dtl(p_emp_id  IN    VARCHAR2 default NULL
                           ,p_dept_id IN    VARCHAR2 default NULL
			   , p_pg_num IN NUMBER default 0
                           )                                 
                          return emp_dtl_TBL pipelined
                IS                  
                 v_query_str VARCHAR2(4000);
                 v_rc sys_refcursor;           
                 v_offset_num Number; 
                 v_nxt_num Number; 
                 v_offset_str VARCHAR2(100);
               
                 out_recordset EMP_DTL_TYPE := EMP_DTL_TYPE (NULL, NULL, NULL);                                      
                      
                BEGIN
                     
                     v_offset_num := 5 * (p_pg_num -1);
                     v_nxt_num := 5;
                     v_offset_str := ' OFFSET :v_offset_num ROWS FETCH NEXT :v_nxt_num ROWS ONLY';
                    
                                            
                          -------------------- calling function to get ACH transactions from ACH source  ------------------
                          v_query_str := 'SELECT EMP_ID, EMP_NAME, EMP_DEPT FROM EMP WHERE 1=1 ';

			  IF p_emp_id IS NOT NULL THEN
				
                          END IF; 

			IF p_emp_id IS NOT NULL  THEN
                  		v_query_str := v_query_str  || ' AND EMP_ID = :EID';
               		ELSE 
                  		v_query_str := v_query_str || ' AND (1=1 or :EID IS NULL) ';
               		END IF;  

			IF p_dept_id IS NOT NULL  THEN
                  		v_query_str := v_query_str  || ' AND DEPT_ID = :DID';
               		ELSE 
                  		v_query_str := v_query_str || ' AND (1=1 or :DID IS NULL) ';
               		END IF;  	
                          
 			
                          open v_rc for v_query_str || v_offset_str USING p_emp_id,p_dept_id,v_offset_num , v_nxt_num ; 
                                    
                          LOOP
                            FETCH v_rc INTO   out_recordset.EMP_ID
                                              ,out_recordset.EMP_NAME
                                              ,out_recordset.DEPT_ID;
                            EXIT WHEN v_rc%NOTFOUND;
                            PIPE ROW(out_recordset);
                          END LOOP;
                          CLOSE v_rc;                                                      
          
END pkg_emp_dtl;
/
Re: Pagination [message #669418 is a reply to message #669413] Sun, 22 April 2018 16:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
Registered: January 2010
Location: Connecticut, USA
Senior Member
IF p_emp_id IS NOT NULL THEN

END IF; 

The above is missing statment folllowing then. Is should be:

IF p_emp_id IS NOT NULL
  THEN
    NULL;
END IF; 

Pipelined function is table function that pipes one row at a time. So even though function return type is table type (emp_dtl_tbl in your case) pipe row should return scalar type (emp_dtl_type in your case). So you have to change:

out_recordset emp_dtl_tbl := emp_dtl_tbl(null,null,null);

to:

out_recordset emp_dtl_type := emp_dtl_type(null,null,null);

Also, function code is missing end statement.

SY.
Re: Pagination [message #669432 is a reply to message #669418] Mon, 23 April 2018 00:10 Go to previous messageGo to next message
satya_ora
Messages: 4
Registered: March 2018
Junior Member
sorry for some typo as I was trying to write code in this editor. Please find below correct code
also I did not understand your comments about Pipelined function as I am using out_recordset emp_dtl_type := emp_dtl_type(null,null,null); only

create or replace TYPE EMP_DTL_TYPE as Object
(
EMP_ID VARCHAR2(255) ,
EMP_NAME VARCHAR2(255) ,
DEPT_ID VARCHAR2(255)
);
/

CREATE OR REPLACE TYPE emp_dtl_TBL as table of EMP_DTL_TYPE;
/

create or replace PACKAGE pkg_emp_dtl AS

function get_emp_dtl(p_emp_id IN VARCHAR2 default NULL
,p_dept_id IN VARCHAR2 default NULL
, p_pg_num IN NUMBER default 0
) 
return emp_dtl_TBL pipelined;

END pkg_emp_dtl;
/





create or replace PACKAGE BODY pkg_emp_dtl AS

-------------------- Main Function to call all other functions to get transactions ----------------------------------------------------

function get_emp_dtl(p_emp_id IN VARCHAR2 default NULL
,p_dept_id IN VARCHAR2 default NULL
, p_pg_num IN NUMBER default 0
) 
return emp_dtl_TBL pipelined
IS 
v_query_str VARCHAR2(4000);
v_rc sys_refcursor; 
v_offset_num Number; 
v_nxt_num Number; 
v_offset_str VARCHAR2(100);

out_recordset EMP_DTL_TYPE := EMP_DTL_TYPE (NULL, NULL, NULL); 

BEGIN

v_offset_num := 5 * (p_pg_num -1);
v_nxt_num := 5;
v_offset_str := ' OFFSET :v_offset_num ROWS FETCH NEXT :v_nxt_num ROWS ONLY';


-------------------- calling function to get ACH transactions from ACH source ------------------
v_query_str := 'SELECT EMP_ID, EMP_NAME, EMP_DEPT FROM EMP WHERE 1=1 ';


IF p_emp_id IS NOT NULL THEN
v_query_str := v_query_str || ' AND EMP_ID = :EID';
ELSE 
v_query_str := v_query_str || ' AND (1=1 or :EID IS NULL) ';
END IF; 

IF p_dept_id IS NOT NULL THEN
v_query_str := v_query_str || ' AND DEPT_ID = :DID';
ELSE 
v_query_str := v_query_str || ' AND (1=1 or :DID IS NULL) ';
END IF; 


open v_rc for v_query_str || v_offset_str USING p_emp_id,p_dept_id,v_offset_num , v_nxt_num ; 

LOOP
FETCH v_rc INTO out_recordset.EMP_ID
,out_recordset.EMP_NAME
,out_recordset.DEPT_ID;
EXIT WHEN v_rc%NOTFOUND;
PIPE ROW(out_recordset);
END LOOP;
CLOSE v_rc; 

return;

end get_emp_dtl;

END pkg_emp_dtl;
/
Re: Pagination [message #669450 is a reply to message #669432] Mon, 23 April 2018 06:42 Go to previous message
Solomon Yakobson
Messages: 2908
Registered: January 2010
Location: Connecticut, USA
Senior Member
Did it compile? If not, what was the error?

SY.
Previous Topic: Handle return empty cursor in SP output parameter
Next Topic: Table select show blank
Goto Forum:
  


Current Time: Sun Jan 19 22:08:59 CST 2020