Pagination [message #669413] |
Sun, 22 April 2018 13:32 |
|
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 |
Solomon Yakobson
Messages: 3275 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 |
|
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;
/
|
|
|
|