## Jan 13, 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.

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