Examples of Ref Cursor

SYS_REFCURSOR  Example
create or replace function f return SYS_REFCURSOR as
  c SYS_REFCURSOR;
begin
  open c for select empno  from employees;
  return c;
end;
----------------------------------------------------

declare
  refCursorValue  SYS_REFCURSOR;
  v varchar2(1);
begin
 refCursorValue := f();   -- Get ref cursor from function
 loop
   fetch refCursorValue into v;
   exit when refCursorValue%notfound;
   dbms_output.put_line('Value from cursor: '||v);
 end loop;
END;
----------------------XXXXXXXXXXXXXXXXXXXXXXXXXXX-------------

  declare
      refCursorValue  SYS_REFCURSOR;
      myRecord product%rowtype;
  begin
      open refCursorValue for select * from product ;
      loop
        fetch refCursorValue into myRecord;
        exit when refCursorValue%notfound;
     end loop;
     closerefCursorValue;
  end;

REF Cursors

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.

• A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. Ref Cursors are opened with an OPEN FOR statement

There are two types of refcursor
    Strong REF Cursor and Weak REF Cursor

Normal cursor is a static cursor in which the query is assigned at design time and cant be changed at run time. REF cursors are dynamic

 Ref cursor can be returned to a client. A PL/SQL cursor cannot be returned to a client. This allows you to pass that numeric cursor handle from PL/SQL code to an external client process.

 Another difference is a cursor can be global - a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function)

 Ref cursor can be passed from subroutine to subroutine - a cursor cannot be.

 Strong REF Cursor : A REF CURSOR that specifies a specific return type
 Weak REF Cursor :  A REF CURSOR that does not specify the return type such as
      SYS_REFCURSOR. 

• A REF CURSOR involves an additional database round-trip.

•A REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REF CURSOR.

•A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the REF CURSOR to point to random records in the result set.


1) A ref cursor can not be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.

2) A ref cursor is defined at runtime and can be opened dynamically but a regular cursor is static and defined at compile time.

3) A ref cursor can be passed to another PL/SQL routine (function or procedure) or returned to a client. A regular cursor cannot be returned to a client application and must be consumed within same routine.

4) A ref cursor incurs a parsing penalty because it cannot cached but regular cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.

5) A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.

6) A regular cursor can more efficiently retrieve data than ref cursor. A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP. A ref cursor must use explicit array fetching.

Use of ref cursors should be limited to only when you have a requirement of returning result sets to clients and when there is NO other efficient/effective means of achieving the goal.Generally we use ref cursor when we need to return result set. Else it is advisable to use static cursor because they give better performance.

Different ways to declare REF CURSORS


   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  -- strong
   TYPE genericcurtyp IS REF CURSOR;  -- weak
   cursor1 empcurtyp;
   cursor2 genericcurtyp;
  
my_cursor SYS_REFCURSOR; -- didn't need to declare a new type (From 9i onwards for weak refcursor use this )

   TYPE EmpRecTyp IS RECORD ( employee_id NUMBER,
      last_name VARCHAR2(25),  salary   NUMBER(8,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;

   emp_cv EmpCurTyp;  -- declare cursor variable

 Opening a  REF CURSOR (Cursor Variable)

 OPEN  cursorname  FOR  vquery [(optional)  USING bindvariable_in_vquery ]
 -- reamining as in Cursor  loop,fetch ,exit ,end loop
 CLOSE cursorname

Solve your SUDOKU by SQL!

Oracle Database 11g Release 2 introduces a new feature called Recursive Subquery Factoring with the help of which you can solve your Sudoku.

To solve this Sudoku you first have to transform this to a single string by appending all rows together:(give exact amount of spaces)

“53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79”

Past this string into a Recursive Subquery, run it and you get a new string with your solved Sudoku:

with x( s, ind ) as

( select sud, instr( sud, ' ' )

  from ( select '53 7 6 195 98 6 8 6 34 8 3 17 2 6 6 28 419 5 8 79' sud from dual )

  union all

  select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )

  , instr( s, ' ', ind + 1 )

  from x

  , ( select to_char( rownum ) z

  from dual

  connect by rownum <= 9

       ) z

  where ind > 0

  and not exists ( select null

  from ( select rownum lp

                          from dual

  connect by rownum <= 9

                        )

  where z = substr( s, trunc( ( ind - 1 ) / 9 ) * 9 + lp, 1 )

  or z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )

  or z = substr( s, mod( trunc( ( ind - 1 ) / 3 ), 3 ) * 3

  + trunc( ( ind - 1 ) / 27 ) * 27 + lp

  + trunc( ( lp - 1 ) / 3 ) * 6

                                   , 1 )

                 )

)

select s

from x

where ind = 0

/

The output:


534678912672195348198342567859761423426853791713924856961537284287419635345286179


This string can be transformed back to a nice display of the solution.

Adopted from blog

Setting Software Port in Oracle

select dbms_xdb.gethttpport as "HTTP-Port",
dbms_xdb.getftpport as "FTP-Port" from dual;

begin
dbms_xdb.sethttpport('3036');
end;
/
commit;

Outer Join restriction

create table TAB1
( col1 VARCHAR2(5),col2 VARCHAR2(5), col3 VARCHAR2(5),  col4 VARCHAR2(5),
  col5 VARCHAR2(5)) ;

create table TAB2
(  col1 VARCHAR2(5), col2 VARCHAR2(5), col3 VARCHAR2(5),
  col4 VARCHAR2(5),  col5 VARCHAR2(5)) ;


insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('a', 'a', 'a', 'a', 'a');

insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('b', 'b', 'b', 'b', 'b');

insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', null, 'c');

insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('d', 'd', 'd', 'd', 'd');

insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('e', 'e', 'e', 'e', 'e');


insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('a', 'a', 'a', 'a', 'a');

insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('b', 'b', 'b', 'b', 'b');

insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', 'c', 'c');

insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', 'c', 'c');

commit;
---------------------------

select tab1.*,tab2.* from tab1 left join tab2
on tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
and tab1.col3 <> 'c';

select tab1.*,tab2.* from tab1 left join tab2
on tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
and tab1.col3 <> 'c';

select tab1.*,tab2.* from tab1 , tab2
where tab1.col1 = tab2.col1 (+)
and tab1.col2 = tab2.col2 (+)
and tab2.col3 (+)<> 'c';

select tab1.*,tab2.* from tab1 , tab2
where tab1.col1 = tab2.col1 (+)
and tab1.col2 = tab2.col2 (+)
and tab1.col3 (+)<> 'c';