You Asked
Hello Experts,
We have created below procedure and need to get result sets for that. Please find below the stored procedure,
Stored Procedure:
Create or replace procedure test_fetch_details (p_emp_id in employee.emp_id%type, p_emp_cur out sys_refcursor)
as
l_emp_id employee_details.emp_id%type;
l_select_part varchar2(1000);
l_from_part clob;
l_where_part varchar2(1000);
l_sql_query clob;
l_count number := 0;
l_alias varchar2(5);
cursor cur_emp_list is
select emp_id from employee_details where emp_id = p_emp_id
order by emp_id desc;
BEGIN
execute immediate 'truncate table emp_details_temp';
insert_emp_details_temp(p_emp_id);
l_select_part := 'select $FIELDS' from';
l_where_part := 'where 1=1 $CONDS$';
l_inner_query := ' (select fieldvalue,fieldname from (select * from insert_emp_details_temp where emp_id = ''$APP$'') unpivot include nulls
(fieldvalue for fieldname IN (project_number,customer_name,gt25,program_type,deal_type,contract_term,total_product_cost_loc, total_product_resale_loc,product_gross_margin,rebate,project_net_margin,bitem_liability,payment_terms,incoterms,customer_doc,total_parts_requested,tototal_parts_quoted,product_detail) ))';
open cur_emp_list;
loop
fetch cur_emp_list into l_emp_id;
exit when cur_emp_list%notfound;
if l_count = 0 then
l_alias := 'A';
l_select_part := replace (l_select_part,'$FIELDS',l_alias || '.fieldname as "EMPLOYEE"$FIELDS');
l_from_part := replace (l_inner_query,'$APP',l_emp_id)||l_alias;
else
l_alias := 'A' ||l_count;
l_where_part := replace (l_where_part,'$CONDS','AND a.fieldname = ' ||l_alias|| '.fieldname $CONDS$' );
l_from_part := l_from_part || ','|| replace (l_inner_query,'$APP',l_emp_id)||l_alias;
end if;
l_select_part := replace (l_select_part,'$FIELDS$',','||l_alias||'.fieldvalue as "APP#' || l_emp_id || '"$FIELDS$' );
l_count := l_count + 1;
end loop;
close cur_emp_list;
if l_count = 0 then
l_sql_query := null;
else
l_where_part := replace (l_where_part, '$CONDS','');
l_select_part := replace (l_select_part, '$FIELDS$','');
l_sql_query := l_select_part || l_from_part || l_where_part;
End if;
open p_emp_cur for l_sql_query;
exception
when others then
dbms_output.put_line (sqlcode||sqlerrm);
end test_fetch_detils;
Please suggest how to get result sets for above procedure.
Thanks.
and Connor said...
You just need a refcursor to bind into the call, eg
SQL> Create or replace
2 procedure test_fetch_details (p_emp_id int, p_emp_cur out sys_refcursor) as
3 BEGIN
4 open p_emp_cur for select * from scott.emp where empno > p_emp_id;
5 end ;
6 /
Procedure created.
SQL> sho err
No errors.
SQL>
SQL>
SQL> variable rc refcursor
SQL> exec test_fetch_details(7000,:rc);
PL/SQL procedure successfully completed.
SQL>
SQL> print rc
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
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 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL> exec test_fetch_details(7899,:rc)
PL/SQL procedure successfully completed.
SQL> print rc
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
7934 MILLER CLERK 7782 23-JAN-82 1300 10
3 rows selected.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment