Feb 8, 2016

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

Jan 14, 2016

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;

Aug 16, 2015

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';

Aug 15, 2015

Conventional & Direct-Path INSERT


•During conventional insert operations, Oracle reuses free space in the table During such operations, Oracle also maintains referential integrity constraints.

Eg : INSERT INTO TABLE T


•During direct-path INSERT operations, Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.


Eg : INSERT /*+ append */ INTO TABLE T

When we insert a rows into a data block, Oracle has the choice of re-using a data block from the freelist or grabbing a "brand new" empty data block, extending the table into the tablespace and raising the high water mark (HWM), usually in chunks of five data blocks

Direct path inserts - These are performed with blocks above the table high water mark
Append - Append tells Oracle to extend the table and grab new, empty data blocks for the insert
Existing free space is not re-used. So, if you direct load insert a bunch of rows, then delete them, and insert them again, the space made by the delete will not be re-used. •Referential Integrity (Foreign Key) constraints and triggers must be disabled before running Direct Path Insert
To make sure your SQL is using Direct Load Insert, run it through Explain Plan. You should see a line reading LOAD AS SELECT.
•Direct Load Insert cannot occur on: ◦Index Organised Tables ◦Tables with LOB Columns ◦Tables with Object Columns ◦Clustered Tables
Direct Load Insert is a faster way of running an INSERT statement. It is particularly useful for inserting large numbers of rows ----