SYS_REFCURSOR Vs REF CURSOR

How to Use the SYS_REFCURSOR and REF CURSOR in Oracle Packages and Procedures

SYS_REFCURSOR is a built-in REF CURSOR type that allows any result set to be associated with it and can be used in 9i or higher. SYS_REFCURSOR can be used to:

• Delcare a cursor variable in an Oracle stored procedure/function;
• Pass cursors from and to an Oracle stored procedure/function.

Exampel 1: Getting a cursor out from an Oracle Package/ProcedureThe following Oracle stored procedure demonstrates how to get a REFCURSOR cursor variable out from a package/procedure:

Package Specification Definition:

CREATE OR REPLACE PACKAGE XX_BI_PKG AS
PROCEDURE xx_ar_inv_proc(p_recordset OUT SYS_REFCURSOR);
END;
Package body definition:CREATE OR REPLACE PACKAGE BODY XX_BI_PKG IS
PROCEDURE xxnsn_ar_inv_proc(p_recordset OUT sys_refcursor)
AS
BEGIN
OPEN p_recordset FOR SELECT RCT.trx_date
,COUNT(RCT.customer_trx_id)
,SUM(APS.amount_due_original)
FROM ra_customer_trx_all RCT
,ar_payment_schedules_all APS
WHERE RCT.customer_trx_id = APS.customer_trx_id
AND APS.class = 'INV'
AND APS.status = 'OP'
GROUP BY RCT.trx_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END xx_ar_inv_proc;
END;

The following PL/SQL statement is used to harness the above Oracle stored procedure:

DECLARE
v_cursor sys_refcursor;
v_date varchar2(100);
v_count number(12);
v_amount number(12);
BEGIN
XX_BI_PKG.xx_ar_inv_proc(p_recordset =>v_cursor);
LOOP
FETCH v_cursor
INTO v_date, v_count, v_amount;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_date ' ' v_count ' ' v_amount);
END LOOP;
CLOSE v_cursor;
END;

STORED PROCEDURE & USER DEFINE FUNCTION

Stored Procedure                                          User define Function

1.Can return zero or n value                           1.Can return one value which is mandatory.
2.Can have input output parameter                  2.Can have only input parameter.
3.We can't call procedure in select             3.We can call functions in select statement.
    statement.
4.Procedures are normally used for           4.Functions are normally used for computations.
   executing business logic.
5.We can call Stored Procedure within      5.We can not call function within stored procedure. 
   function


                                   

EXISTS & IN in SQL

• When a subquery returns a NULL value then the whole statement become NULL. In that cases we are using the EXITS keyword. If we want to compare particular values in subqueries then we are using the IN keyword.

    Ex:-

   select *
   from emp
   where empno not in(select mgr from emp);   /*it through no rows selected because the inner query
                                                                          return NULL value*/

• If you are using the IN operator, the SQL engine will scan all records fetched from the inner query. On the other hand if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match.

•   When your inner query fetching thousand of rows then EXIST would be better choice.When your inner query fetching few rows, then IN will be faster
• EXIST evaluate on true or false but IN compare multiple value. When you don't know the record is exist or not, your should choose EXIST.

FORMAT_ERROR_BACKTRACE

FORMAT_ERROR_BACKTRACE in the DBMS_UTILITY package has been introduced in the Oracle version 10g. Format error backtrace is use find the exact position where the exception has occurred.
 When an exception is raised, one of the most important piece of information that a developer would like to know is the line of code that raised that exception  for that we are using .

FORMAT_ERROR_BACKTRACE
FORMAT_ERROR_STACK
FORMAT_CALL_STACK

DECLARE
BEGIN
   RAISE NO_DATA_FOUND;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('Error occured at...');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
      RAISE;
END;

Error occured at...
ORA-06512: at line 3
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8