Sudoku Solver (Easy Level) in Oracle PL SQL

Data structures used

Sudoku grid consist of this 9x9 cells. Each cell in grid has actual value(decided value) and a list of possible values.

Cell is represented by PL/SQL Object, consisting of a

• Number type which holds actual value, and


• Nested table which holds possible values for cell.

[sourcecode language="sql"]
--Cell
CREATE TYPE cell AS OBJECT (
actual_value number,
poss_values possible_values
)

--create nested table of possible values
CREATE TYPE possible_values AS TABLE OF number;
[/sourcecode]
[sourcecode language="sql"]
--Sudoku grid of 9 columns
CREATE TABLE xx_test (C1 cell,C2 cell,C3 cell,C4 cell,C5 cell,C6 cell,C7 cell,C8 cell,C9 cell,id number)
NESTED TABLE C1.poss_values STORE AS possible_val_tab1
NESTED TABLE C2.poss_values STORE AS possible_val_tab2
NESTED TABLE C3.poss_values STORE AS possible_val_tab3
NESTED TABLE C4.poss_values STORE AS possible_val_tab4
NESTED TABLE C5.poss_values STORE AS possible_val_tab5
NESTED TABLE C6.poss_values STORE AS possible_val_tab6
NESTED TABLE C7.poss_values STORE AS possible_val_tab7
NESTED TABLE C8.poss_values STORE AS possible_val_tab8
NESTED TABLE C9.poss_values STORE AS possible_val_tab9

--set row number for
update xx_test set id = rownum

[/sourcecode]

A block is defined as group of 9 cells like
Block B1 consists of cells
11 12 13
21 22 23
31 32 33

Update Rule for Possible values of cell

Possible values for a cell is calculated by removing actual value that is present in other cells in that particular row, column and block from list of possible values of the cell.

If a value is present in a row/column/block then it should not appear in any other cell in the same row/column/block.

For example, for cell 22, possible value is calculated by removing actual values of all cells in row R2, column C2 and Block B1 from Varray 1..9(all possible values)

Update Rule for Actual Value of a cell
1.Cell containing only one possible value.
2.Value possible only in one cell in a row, column and block

Pseudo Code

For all cells
Check If actual value is null then
check list of possible value for cell. If only one value possible, then update cell with this value.
For all values 1..9 check if the value is not possible in other cells of that particular row column or block then update cell with that value.

Create .CSV file in Oralce PL SQL

Requirement: To generate delimited output of SQL query using Oracle PL SQL

Solution :

Please find below complete package code.

This package can be used to write delimited output to DBMS output, Oracle Apps Log/Output or File Directory.

Usage

Call the procedure xxtk_generate_csv with mandatory parameter values.

PROCEDURE xxtk_generate_csv (

p_query VARCHAR2,

p_delimiter varchar2,

p_file_handle number,

p_dir varchar2 DEFAULT NULL,

p_filename varchar2 DEFAULT NULL,

p_cur_qry VARCHAR2 DEFAULT 'Q'

) ;

First parameter is SQL query as Varchar2 data type, second is delimiter(tab or comma or other) and third is file handle i.e where to write output.

Example 1

Consider below query

select a "col1", b from (select 1 a, level b from dual connect by level < 4)

Comma separated Output of this query is required

[sourcecode language="sql"]
DECLARE
--q operator is available in 11i to escape quotes withing string
l_qry varchar2(1000):=q'[select a "col1", b from (select 1 a, level b from dual connect by level < 4)]';
BEGIN
--write result of l_qry as comma separated values to dbms output
xxtk_generate_csv_pkg.xxtk_generate_csv
(l_qry,',',xxtk_generate_csv_pkg.write_to_db_out);
END;
[/sourcecode]

Q operator is used to escape quote pairs appearing in data. Otherwise, we need to escape each quote separately.

DBMS Output

"col1","B"

1,1

1,2

1,3

Example 2

4 and 5 parameter are used to specify directory location and file name where to write output.

6 parameter p_cur_qry is used when passing cursor handle.

When input parameters are required for query either we can

• pass query text(first parameter)  with concatenated variable values (like where select ....where dept_no='||p_dept_no||'..' or


• pass parsed query with bind variable and send the parsed query handle (following example)

Second option is better because using bind variable is always preferable instead of concatenating variables in SQL query.

In case when SQL query is already parsed  with bind variable values.

[sourcecode language="sql"]

DECLARE
cursor_name   INTEGER         DEFAULT DBMS_SQL.open_cursor;
l_qry varchar2(1000):=q'[select *  from scott.emp where deptno =:p_dept_no and hiredate > :p_hire_date]';
BEGIN
--get parsed query handle in cursor_name
DBMS_SQL.parse (cursor_name, l_qry, DBMS_SQL.native);
--set value of dept_no to 20
DBMS_SQL.bind_variable (cursor_name, ':p_dept_no',20 );
--set hire date
DBMS_SQL.bind_variable (cursor_name, ':p_hire_date','01-JAN-1981' );

xxtk_generate_csv_pkg.xxtk_generate_csv(p_query=>cursor_name,p_delimiter=>',',

p_file_handle=>xxtk_generate_csv_pkg.write_to_db_out,p_cur_qry=>'C' );
END;
[/sourcecode]

Output

[sourcecode]

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7788,SCOTT,ANALYST,7566,09-DEC-82,3000,,20
7876,ADAMS,CLERK,7788,12-JAN-83,1100,,20
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
[/sourcecode]

2 GB File limit on writing file using Oracle SQLPlus in Unix

Requirement

Dump of huge data is required from oracle database. Say, dump of GL data (specific query output and not complete table dump) is required for particular month.

I simply spool the output of query and what I observe is - it always produces 2GB output file, for few months I tested, without giving any error message or warning.

So, I google for "2GB Unix oracle SQLPlus" and I find number of links mentioning this problem

unix 2GB oracle export

One of the solution involves using named pipe.

A named pipe doesn't write data to disk, but instead write to buffer in memory. Writer writes at one end and Reader reads from other end.

Named Pipe

• named pipe is actually file in file-system


• Like unnamed pipe it is  used for Inter process Communication


• Unlike unnamed pipe it is system-persistent, that is, it exists beyond process life and has to be created and deleted explicitly.


• Process that reads or writes to pipe blocks until the other end of pipes performs read or write operation on same

Create a named pipe, redirect data written to it to sed command.

Removes extra tabs coming in spool file(spool usually gives extra tabs when colsep is used), and then pipe output to compress command which writes compressed data to disk.

Sqlplus will write data at one end to named pipe and following command will write compressed data to disk.

cat /tmp/aug11_pipe | compress  > gl_aug11_pipe.csv

Oracle Correlated Query Scope. Invalid Identifier Error

Consider query

[sourcecode language="sql"]
SELECT (SELECT tab.col  FROM DUAL) a
FROM (SELECT 1 col  FROM DUAL) tab;
[/sourcecode]

Output is

A
---
1

Here I am using tab table value inside subquery that is one level down and it's recognized by system.

Now, If I take it one more level down, that is, using tab value inside subquery's subquery, something like

[sourcecode language="sql"]
SELECT (SELECT * FROM (SELECT tab.col  FROM DUAL)) A
FROM (SELECT 1 col  FROM DUAL) tab
[/sourcecode]

It throws "invalid identifier TAB.COL" error

At Second level its not recognized by system.

Oracle NVL function always evaluates second parameter

NVL function doesn't do short circuit evaluation  i.e, all parameters are evaluated irrespective of condition but, value is returned only when condition returns true.

Lets test this

Consider below query to generate test data

[sourcecode language="sql"]
SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5
[/sourcecode]

it returns 5 rows of which one row is always null

Now, Lets write one simple function which returns some value. We will call this function in case column value is null. Like

[sourcecode language="sql"]
SELECT NVL (tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]

The above statement should ideally call function temp_func(defined below) only for one row.

--takes input value and prints it in output
--always returns '***'

[sourcecode language="sql"]
CREATE OR REPLACE FUNCTION temp_func (p_text VARCHAR2)
RETURN VARCHAR2
AS
i   VARCHAR2(30) := 'who ';
BEGIN
i := i || p_text;
DBMS_OUTPUT.put_line (i);
RETURN '***';
END;
[/sourcecode]

But above SELECT statement returns five lines of dbms output, one for each row. That means function temp_func got called for each row irrespective of condition.

Unlike NVL, COALESCE function uses short circuit evaluation and so, prints only one line of dbms output

[sourcecode language="sql"]
SELECT coalesce(tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]

Resource Allocation System: Secret Revealed

Two senior persons, SP1, working since long time in the Organization, along with SP2, recently joined, organizes freshers induction program.

After praising number of process, practices and policies SP1 now talks about resource allocation system.

SP1 addressing freshers: Our Resource allocation system is one of the best. It is very advanced, and well proven system. The allocation of resources to particular project is done on the basis of number of critetias like their skills, experience, aspirations, personal constraints and, many others.It tries to find the best match for particular project.

After the session finished, SP2 with suprise and amazement says to SP1 ‘I didn’t know we have such a powerful resource allocation system’

SP1:’Yes, It employs one of the best algorithm. Any guess?’

SP1: ‘Okay. Its called Random Number generator’

Oracle Table NULL column validation. Update all error message at once

Requirement:

Consider table stg_table with columns c1 c2 c3 and c4(no index or not null constraints) and values as mentioned below

id    c1    c2    c3    c4
-----------------------------------
#1    1    2              5
#2    3            3
#3                         6

We wish to do NULL validation on all columns and update the error in error_msg column .For some reason NOT NULL constraint is not put on these columns.

Required output

id    c1    c2    c3    c4    error_msg
----------------------------------------------------
#1    1    2               5    C3 NULL
#2    3             3            C2, C4 NULL
#3                           6    C1, C2, C3 NULL

Solution

One simple method is to write as many number of sql UPDATE statement as number of columns that needs to be validated

UPDATE stg_table set error_message = error_msg||'C1, ' where c1 is null
UPDATE stg_table set error_message = error_msg||'C2, ' where c2 is null
.
.
and finally if error message was updated by any of above row then append ' NULL' to error_msg
UPDATE stg_table set error_message = decode(error_message,null,null,error_msg||' NULL')

Other method to perform same thing with single update statement is

[sourcecode language="sql"]
SELECT   id, DECODE (c1, NULL, 'C1,', NULL )
|| DECODE (c2, NULL, 'C2,', NULL)
|| DECODE (C3, NULL, 'C3,',NULL)
|| DECODE (c3, NULL, 'C3,', NULL) err_msg
FROM stg_table
[/sourcecode]

Above statement gives output like

id    err_msg
----------------------
1    C3,
2    C2,C4,
3    C1,C2,C3,

Now we need to remove the extra comma from end and append 'NULL' string

[sourcecode language="sql"]
SELECT id, DECODE (t2.err_msg,
NULL, NULL,
SUBSTR (t2.err_msg, 1, LENGTH (t2.err_msg) - 1)
|| ' NULL'
) error_msg
FROM (SELECT    DECODE (emp_no, NULL, 'EMP NO,', NULL )
|| DECODE (invoice_no, NULL, 'INVOICE NO,', NULL)
|| DECODE (invoice_date, NULL, 'INVOICE DATE,',NULL)
|| DECODE (vendor_no, NULL, 'VENDOR NO,', NULL) err_msg,
ID
FROM xx_invoices_stg) t2
[/sourcecode]

id    err_msg
----------------------
1    C3 NULL
2    C2,C4 NULL
3    C1,C2,C3 NULL

Now simply update stg table correlating above query id with table id

[sourcecode language="sql"]
UPDATE stg_table t1
SET        error_msg =
(SELECT DECODE (t2.err_msg,
NULL, NULL,
SUBSTR (t2.err_msg, 1, LENGTH (t2.err_msg) - 1)
|| ' should Not be NULL'
) error_msg
FROM (SELECT    DECODE (emp_no, NULL, 'EMP NO,', NULL )
|| DECODE (invoice_no, NULL, 'INVOICE NO,', NULL)
|| DECODE (invoice_date, NULL, 'INVOICE DATE,',NULL)
|| DECODE (vendor_no, NULL, 'VENDOR NO,', NULL) err_msg,
ID
FROM xx_invoices_stg) t2
WHERE t2.ID = t1.ID)
[/sourcecode]

DECODE inside IN clause Oracle SQL

Requirement : Consider Emplyoee table emp which has Emplyee number and Deparment number columns.

Table : emp

empno  deptno
---------------------
1       10
2       20
3       20
4       10
5       30

Based on some input parameter var that isnot part of any table we wish to select employees of specific Departments.

If value of var = 1 then
select dept 10 employees
empno 1 and 4

If value of var = 2 then
select dept 20 and 30 employees.
empno 2, 3 and 5

var variable has no significance with respect to Employee data

Solution :

[sourcecode language="sql"]
SELECT empno,deptno
FROM emp
WHERE deptno IN (DECODE(:var,1,10),decode(:var,2,20),decode(:var,2,30))
[/sourcecode]

when var value is 1 then
both second and third decode returns null, and evaluates to false for all rows, and first decode returns 10. The where clause translates into something like
where deptno = 10 or deptno = null or deptno = null
So, it returns deptno 10 employees only.

Similarly, for var value of 2 the where clause translates into
where deptno = null or deptno = 20 or deptno = 30
and returns deptno 20 and 30 employees.