Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL Database Code Library and Resources Jump to:  

Sudoku

The Challenge
I obviously have far too much free time on my hands, or perhaps too little, because last night for the first time I looked at a Sudoku puzzle. Three hours later I had three functions and a stored procedure sufficiently mature to solve simple puzzles (and no the code has not been cleaned up or optimized in any way).

Solve them, that is, as long as the puzzle could be analyzed such that at least one empty cell's contents could be unambiguously deduced. Get into a more complicated puzzle and it seemed a brute-force attack strategy would be required.

Anyway I thought I would post my code, to date, here for others and perhaps by collaboration some fun can be had by all.
 
Block layout for coding
Block 1 Block 2 Block 3
Block 4 Block 5 Block 6
Block 7 Block 8 Block 9
Simple Puzzle 2 Demo
  1 2 3 4 5 6 7 8 9
1   6   5   8   2  
2 3       7       8
3                  
4 1   2       3   4
5   5           9  
6 4   8       7   5
7       4   1      
8 6       5       3
9   4   3   9   8  
Simple Puzzle 2 Demo
  1 2 3 4 5 6 7 8 9
1   4 6       7 8  
2 9     8   2     1
3 3     1   6     5
4   3 4   8   5 9  
5       7   9      
6   9 2   5   1 6  
7 2     4   8     9
8 4     5   3     6
9   8 3       2 1  
More Complex Puzzle Demo
  1 2 3 4 5 6 7 8 9
1 2       4       8
2   8           7  
3 3       2       6
4     2       7   1
5         3        
6 9   5       4    
7 8     1   6     9
8   2           6  
9 1         7     2
More Complex Puzzle Demo

Level 4
  1 2 3 4 5 6 7 8 9
1 3 7           5 6
2 6 9           1 2
3       2   3      
4     3 5   7 8    
5                  
6     6 4   2 7    
7       9   1      
8 1 2           9 7
9 4 3           8 1
More Complex Puzzle Demo

Level 4
  1 2 3 4 5 6 7 8 9
1       1   5      
2       9   6      
3 5 6           8 3
4 1 2     5     7 4
5       3   1      
6 7 5     4     9 6
7 3 4           5 2
8       4   3      
9       5   2      
Tables -- the original puzzle and the solution when processing is completed
CREATE TABLE SUDOKU (
ROWNO NUMBER(1,0),
COL1  NUMBER(1,0),
COL2  NUMBER(1,0),
COL3  NUMBER(1,0),
COL4  NUMBER(1,0),
COL5  NUMBER(1,0),
COL6  NUMBER(1,0),
COL7  NUMBER(1,0),
COL8  NUMBER(1,0),
COL9  NUMBER(1,0));

-- a table, temporarily, for holding possible solutions
CREATE TABLE POSSIBLES (
COLNO  NUMBER(1,0),
ROWNO  NUMBER(1,0),
VALS   VARCHAR2(9));

Get string of values in a block		 CREATE OR REPLACE FUNCTION get_block_string (
blockno PLS_INTEGER) RETURN VARCHAR2 IS

 astr VARCHAR2(99);
 bstr VARCHAR2(99);
 cstr VARCHAR2(99);
 sstr VARCHAR2(99);
BEGIN
  IF blockno IN (1, 4, 7) THEN
    sstr := 'SELECT COL1 || COL2 || COL3 FROM sudoku WHERE rowno = ';
  ELSIF blockno IN (2, 5, 8) THEN
    sstr := 'SELECT COL4 || COL5 || COL6 FROM sudoku WHERE rowno = ';
  ELSIF blockno IN (3, 6, 9) THEN
    sstr := 'SELECT COL7 || COL8 || COL9 FROM sudoku WHERE rowno = ';
  END IF;

  IF blockno IN (1, 2, 3) THEN
    astr := sstr || '1';
    bstr := sstr || '2';
    cstr := sstr || '3';
  ELSIF blockno IN (4, 5, 6) THEN
    astr := sstr || '4';
    bstr := sstr || '5';
    cstr := sstr || '6';
  ELSIF blockno IN (7, 8, 9) THEN
    astr := sstr || '7';
    bstr := sstr || '8';
    cstr := sstr || '9';
  END IF;

  EXECUTE IMMEDIATE astr INTO astr;
  EXECUTE IMMEDIATE bstr INTO bstr;
  EXECUTE IMMEDIATE cstr INTO cstr;

  astr := astr || bstr || cstr;

  RETURN astr;
END get_block_string;
/
Get a string of values in a column CREATE OR REPLACE FUNCTION get_col_string (colno PLS_INTEGER, rowno PLS_INTEGER) RETURN VARCHAR2 IS

 cstr VARCHAR2(99);
 rset VARCHAR2(9);
 rstr VARCHAR2(1);
 sstr VARCHAR2(99);
BEGIN
  cstr := 'COL' || TO_CHAR(colno);

  FOR i IN 1..9 LOOP
    IF rowno <> i THEN
      sstr := 'SELECT '||cstr||' FROM sudoku WHERE rowno='||TO_CHAR(i);
      EXECUTE IMMEDIATE sstr INTO rstr;

      rset := rset || rstr;
    END IF;
  END LOOP;
  RETURN rset;
END get_col_string;
/

Get a string of values within a row		 CREATE OR REPLACE FUNCTION get_row_string (colno PLS_INTEGER, rowno PLS_INTEGER) RETURN VARCHAR2 IS

 col1 VARCHAR2(4);
 col2 VARCHAR2(4);
 col3 VARCHAR2(4);
 col4 VARCHAR2(4);
 col5 VARCHAR2(4);
 col6 VARCHAR2(4);
 col7 VARCHAR2(4);
 col8 VARCHAR2(4);
 fclause VARCHAR2(100) := ' FROM sudoku WHERE rowno = ';
 rset VARCHAR2(9);
 sstr VARCHAR2(99);
BEGIN
  IF colno = 1 THEN
    sstr := 'SELECT col2 || col3 || col4 || col5 || col6 || col7 || col8 || col9 ' || fclause || TO_CHAR(rowno);
  ELSIF colno = 2 THEN
    sstr := 'SELECT col1 || col3 || col4 || col5 || col6 || col7 || col8 || col9 ' || fclause || TO_CHAR(rowno);
  ELSIF colno = 3 THEN
    sstr := 'SELECT col1 || col2 || col4 || col5 || col6 || col7 || col8 || col9 ' || fclause || TO_CHAR(rowno);
  ELSIF colno = 4 THEN
    sstr := 'SELECT col1 || col2 || col3 || col5 || col6 || col7 || col8 || col9 ' || fclause || TO_CHAR(rowno);
  ELSIF colno = 5 THEN
    sstr := 'SELECT col1 || col2 || col3 || col4 || col6 || col7 || col8 || col9 ' || fclause || TO_CHAR(rowno);
  ELSIF colno = 6 THEN
    sstr := 'SELECT col1 || col2 || col3 || col4 || col5 || col7 || col8 || col9 ' || fclause || TO_CHAR(rowno);
  ELSIF colno = 7 THEN
    sstr := 'SELECT col1 || col2 || col3 || col4 || col5 || col6 || col8 || col9 ' || fclause || TO_CHAR(rowno);
  ELSIF colno = 8 THEN
    sstr := 'SELECT col1 || col2 || col3 || col4 || col5 || col6 || col7 || col9 ' || fclause || TO_CHAR(rowno);
  ELSIF colno = 9 THEN
    sstr := 'SELECT col1 || col2 || col3 || col4 || col5 || col6 || col7 || col8 ' || fclause || TO_CHAR(rowno);
  END IF;

  EXECUTE IMMEDIATE sstr INTO rset;

  RETURN rset;
END get_row_string;
/

Stored procedure		 CREATE OR REPLACE PROCEDURE solve IS
 bstr VARCHAR2(9);
 c VARCHAR2(4);
 colno VARCHAR2(4);
 cstr VARCHAR2(9);
 d PLS_INTEGER;
 e PLS_INTEGER;
 f PLS_INTEGER;
 rset VARCHAR2(9);
 rstr VARCHAR2(9);
 sstr VARCHAR2(150);
 tstr VARCHAR2(27);
 x VARCHAR2(1);

 curblk PLS_INTEGER := 0;
 lstblk PLS_INTEGER := 9;
 curcnt PLS_INTEGER := 0;
 lstcnt PLS_INTEGER := 9;
BEGIN
  -- outer loop that iterates until the problem is solved
  LOOP
    -- clean up routine
    DELETE FROM possibles;

    --===================== SECTION 1 END
    -- for each column
    FOR i IN 1..9 LOOP
      colno := 'COL' || TO_CHAR(i);
      -- for each row
        FOR j IN 1..9 LOOP
          sstr:='SELECT '||colno||' FROM sudoku WHERE rowno='||TO_CHAR(j);
          EXECUTE IMMEDIATE sstr INTO x;
          -- modify above statement to use bind variables

          -- empty the result set
          rset := NULL;

          -- if the value has not already been determined
          IF x IS NULL THEN
            -- find the block for the column and row
            IF i BETWEEN 1 AND 3 AND j BETWEEN 1 AND 3 THEN
              curblk := 1;
            ELSIF i BETWEEN 4 AND 6 AND j BETWEEN 1 AND 3 THEN
              curblk := 2;
            ELSIF i BETWEEN 7 AND 9 AND j BETWEEN 1 AND 3 THEN
              curblk := 3;
            ELSIF i BETWEEN 1 AND 3 AND j BETWEEN 4 AND 6 THEN
              curblk := 4;
            ELSIF i BETWEEN 4 AND 6 AND j BETWEEN 4 AND 6 THEN
              curblk := 5;
            ELSIF i BETWEEN 7 AND 9 AND j BETWEEN 4 AND 6 THEN
              curblk := 6;
            ELSIF i BETWEEN 1 AND 3 AND j BETWEEN 7 AND 9 THEN
              curblk := 7;
            ELSIF i BETWEEN 4 AND 6 AND j BETWEEN 7 AND 9 THEN
              curblk := 8;
            ELSIF i BETWEEN 7 AND 9 AND j BETWEEN 7 AND 9 THEN
              curblk := 9;
            END IF;

            -- if the block has changed get the block string
            IF curblk <> lstblk THEN
              bstr := get_block_string(curblk);
              lstblk := curblk;
            END IF;

            -- get the column string
            cstr := get_col_string(i,j);

            -- get the row string
            rstr := get_row_string(i,j);

            -- concatenate them together
            tstr := bstr || cstr || rstr;

            -- test for numbers not already present in any of the 3 sets
            FOR k IN 1..9 LOOP -- check for matching numbers
              IF instr(tstr, TO_CHAR(k), 1, 1) = 0 THEN
                rset := rset || TO_CHAR(k);
              END IF;
            END LOOP;

            -- insert into table the possibles
            INSERT INTO possibles
            (colno, rowno, vals)
            VALUES
            (i, j, rset);
          END IF;
        END LOOP; -- end row loop
      END LOOP; -- end column loop

      -- is the puzzle solved?
      SELECT COUNT(*)
      INTO curcnt
      FROM possibles;

      -- if the puzzle is solved exit the loop
      IF curcnt = 0 THEN
        COMMIT;
        EXIT;
      ELSIF curcnt = lstcnt THEN -- solution is ambiguous
        COMMIT;
        RAISE_APPLICATION_ERROR(-20001, 'Need Brute Force w/ SavePoints');
        EXIT;
      END IF;

      -- set the last block worked variable the same as the current block
      lstcnt := curcnt;

      -- see if any blocks have only a single solution
      SELECT COUNT(*)
      INTO curcnt
      FROM possibles
      WHERE LENGTH(vals) = 1;

      IF curcnt > 0 THEN -- there is at least one single choice
        -- pull rec from possibles where there is only 1 choice
        FOR rec IN (SELECT * FROM possibles WHERE LENGTH(vals) = 1) LOOP
          c := 'COL' || TO_CHAR(rec.colno);
          sstr := 'UPDATE sudoku SET ' || c || ' = :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING rec.vals, rec.rowno;
        END LOOP;

        -- remove all solved values from possibles
        DELETE FROM possibles WHERE LENGTH(vals) = 1;
      ELSE -- no single choices ... work by elimination
      --===================== SECTION 1 END
      --===================== SECTION 2 BEG
        FOR y IN 1 .. 9 LOOP
          sstr := 'SELECT COUNT(*) FROM possibles WHERE rowno BETWEEN 1 AND 3 
          AND vals LIKE ''%'' || :b || ''%''';

          BEGIN
            EXECUTE IMMEDIATE sstr INTO d USING y;
            -- only a single solution is possible
            IF d = 1 THEN
            SELECT colno, rowno
            INTO e, f
            FROM possibles
            WHERE rowno BETWEEN 1 AND 3
            AND INSTR(vals, y, 1, 1) <> 0;

            colno := 'COL' || TO_CHAR(e);
            sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
            EXECUTE IMMEDIATE sstr USING y, f;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
        END;
      END LOOP;

      FOR y IN 1 .. 9 LOOP
        sstr := 'SELECT COUNT(*) FROM possibles WHERE rowno BETWEEN 4 AND 6 
        AND vals LIKE ''%'' || :b || ''%''';

        BEGIN
          EXECUTE IMMEDIATE sstr INTO d USING y;
          -- only a single solution is possible
          IF d = 1 THEN
            SELECT colno, rowno
            INTO e, f
            FROM possibles
            WHERE rowno BETWEEN 4 AND 6
            AND INSTR(vals, y, 1, 1) <> 0;

            colno := 'COL' || TO_CHAR(e);
            sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
            EXECUTE IMMEDIATE sstr USING y, f;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
        END;
      END LOOP;

      FOR y IN 1 .. 9 LOOP
        sstr := 'SELECT COUNT(*) FROM possibles WHERE rowno BETWEEN 7 AND 9 
        AND vals LIKE ''%'' || :b || ''%''';

        BEGIN
          EXECUTE IMMEDIATE sstr INTO d USING y;
          -- only a single solution is possible
          IF d = 1 THEN
            SELECT colno, rowno
            INTO e, f
            FROM possibles
            WHERE rowno BETWEEN 7 AND 9
            AND INSTR(vals, y, 1, 1) <> 0;

            colno := 'COL' || TO_CHAR(e);
            sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
            EXECUTE IMMEDIATE sstr USING y, f;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
        END;
      END LOOP;
      --===================== SECTION 2 END
      --===================== SECTION 3 BEG
      FOR y IN 1 .. 9 LOOP
        sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 1 AND 3 
        AND vals LIKE ''%'' || :b || ''%''';

        BEGIN
          EXECUTE IMMEDIATE sstr INTO d USING y;
          -- only a single solution is possible
          IF d = 1 THEN
            SELECT colno, rowno
            INTO e, f
            FROM possibles
            WHERE colno BETWEEN 1 AND 3
            AND INSTR(vals, y, 1, 1) <> 0;

            colno := 'COL' || TO_CHAR(e);
            sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
            EXECUTE IMMEDIATE sstr USING y, f;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
        END;
      END LOOP;

      FOR y IN 1 .. 9 LOOP
        sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 4 AND 6 
        AND vals LIKE ''%'' || :b || ''%''';

        BEGIN
          EXECUTE IMMEDIATE sstr INTO d USING y;
          -- only a single solution is possible
          IF d = 1 THEN
            SELECT colno, rowno
            INTO e, f
            FROM possibles
            WHERE colno BETWEEN 4 AND 6
            AND INSTR(vals, y, 1, 1) <> 0;

            colno := 'COL' || TO_CHAR(e);
            sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
            EXECUTE IMMEDIATE sstr USING y, f;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
        END;
      END LOOP;

      FOR y IN 1 .. 9 LOOP
        sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 7 AND 9 
        AND vals LIKE ''%'' || :b || ''%''';

        BEGIN
          EXECUTE IMMEDIATE sstr INTO d USING y;
          -- only a single solution is possible
          IF d = 1 THEN
            SELECT colno, rowno
            INTO e, f
            FROM possibles
            WHERE colno BETWEEN 7 AND 9
            AND INSTR(vals, y, 1, 1) <> 0;

            colno := 'COL' || TO_CHAR(e);
            sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
            EXECUTE IMMEDIATE sstr USING y, f;
          END IF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
        END;
      END LOOP;
      --===================== SECTION 3 END
      --===================== SECTION 4 BEG
      FOR m IN 1 .. 9 LOOP
      sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 1 AND 3
      AND rowno BETWEEN 1 AND 3 AND vals LIKE ''%'' || :b || ''%''';

      BEGIN
        EXECUTE IMMEDIATE sstr INTO d USING m;
        IF d = 1 THEN
          SELECT colno, rowno
          INTO e, f
          FROM possibles
          WHERE colno BETWEEN 1 AND 3
          AND rowno BETWEEN 1 AND 3
          AND INSTR(vals, m, 1, 1) <> 0;

          colno := 'COL' || TO_CHAR(e);
          sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING m, f;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;

    FOR m IN 1 .. 9 LOOP
      sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 4 AND 6
      AND rowno BETWEEN 1 AND 3 AND vals LIKE ''%'' || :b || ''%''';

      BEGIN
        EXECUTE IMMEDIATE sstr INTO d USING m;
        IF d = 1 THEN
          SELECT colno, rowno
          INTO e, f
          FROM possibles
          WHERE colno BETWEEN 4 AND 6
          AND rowno BETWEEN 1 AND 3
          AND INSTR(vals, m, 1, 1) <> 0;

          colno := 'COL' || TO_CHAR(e);
          sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING m, f;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;

    FOR m IN 1 .. 9 LOOP
      sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 7 AND 9 
      AND rowno BETWEEN 1 AND 3 AND vals LIKE ''%'' || :b || ''%''';

      BEGIN
        EXECUTE IMMEDIATE sstr INTO d USING m;
        IF d = 1 THEN
          SELECT colno, rowno
          INTO e, f
          FROM possibles
          WHERE colno BETWEEN 7 AND 9
          AND rowno BETWEEN 1 AND 3
          AND INSTR(vals, m, 1, 1) <> 0;

          colno := 'COL' || TO_CHAR(e);
          sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING m, f;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;

    FOR m IN 1 .. 9 LOOP
      sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 1 AND 3 
      AND rowno BETWEEN 4 AND 6 AND vals LIKE ''%'' || :b || ''%''';

      BEGIN
        EXECUTE IMMEDIATE sstr INTO d USING m;
        IF d = 1 THEN
          SELECT colno, rowno
          INTO e, f
          FROM possibles
          WHERE colno BETWEEN 1 AND 3
          AND rowno BETWEEN 4 AND 6
          AND INSTR(vals, m, 1, 1) <> 0;

          colno := 'COL' || TO_CHAR(e);
          sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING m, f;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;

    FOR m IN 1 .. 9 LOOP
      sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 4 AND 6 
      AND rowno BETWEEN 4 AND 6 AND vals LIKE ''%'' || :b || ''%''';

      BEGIN
        EXECUTE IMMEDIATE sstr INTO d USING m;
        IF d = 1 THEN
          SELECT colno, rowno
          INTO e, f
          FROM possibles
          WHERE colno BETWEEN 4 AND 6
          AND rowno BETWEEN 4 AND 6
          AND INSTR(vals, m, 1, 1) <> 0;

          colno := 'COL' || TO_CHAR(e);
          sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING m, f;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;

    FOR m IN 1 .. 9 LOOP
      sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 7 AND 9 
      AND rowno BETWEEN 4 AND 6 AND vals LIKE ''%'' || :b || ''%''';

      BEGIN
        EXECUTE IMMEDIATE sstr INTO d USING m;
        IF d = 1 THEN
          SELECT colno, rowno
          INTO e, f
          FROM possibles
          WHERE colno BETWEEN 7 AND 9
          AND rowno BETWEEN 4 AND 6
          AND INSTR(vals, m, 1, 1) <> 0;

          colno := 'COL' || TO_CHAR(e);
          sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING m, f;
        END IF;
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
      END;
    END LOOP;

    FOR m IN 1 .. 9 LOOP
      sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 1 AND 3 
      AND rowno BETWEEN 7 AND 9 AND vals LIKE ''%'' || :b || ''%''';

      BEGIN
        EXECUTE IMMEDIATE sstr INTO d USING m;
        IF d = 1 THEN
          SELECT colno, rowno
          INTO e, f
          FROM possibles
          WHERE colno BETWEEN 1 AND 3
          AND rowno BETWEEN 7 AND 9
          AND INSTR(vals, m, 1, 1) <> 0;

          colno := 'COL' || TO_CHAR(e);
          sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING m, f;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;

    FOR m IN 1 .. 9 LOOP
      sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 4 AND 6 
      AND rowno BETWEEN 7 AND 9 AND vals LIKE ''%'' || :b || ''%''';

      BEGIN
        EXECUTE IMMEDIATE sstr INTO d USING m;
        IF d = 1 THEN
          SELECT colno, rowno
          INTO e, f
          FROM possibles
          WHERE colno BETWEEN 4 AND 6
          AND rowno BETWEEN 7 AND 9
          AND INSTR(vals, m, 1, 1) <> 0;

          colno := 'COL' || TO_CHAR(e);
          sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING m, f;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;

    FOR m IN 1 .. 9 LOOP
      sstr := 'SELECT COUNT(*) FROM possibles WHERE colno BETWEEN 7 AND 9 
      AND rowno BETWEEN 7 AND 9 AND vals LIKE ''%'' || :b || ''%''';

      BEGIN
        EXECUTE IMMEDIATE sstr INTO d USING m;
        IF d = 1 THEN
          SELECT colno, rowno
          INTO e, f
          FROM possibles
          WHERE colno BETWEEN 7 AND 9
          AND rowno BETWEEN 7 AND 9
          AND INSTR(vals, m, 1, 1) <> 0;

          colno := 'COL' || TO_CHAR(e);
          sstr := ' UPDATE sudoku SET ' || colno || '= :b WHERE rowno = :c';
          EXECUTE IMMEDIATE sstr USING m, f;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END LOOP;
    --===================== SECTION 4 END
    END IF;
  END LOOP;
END solve;
/
 
Anyone wishing to take part in the search for a PL/SQL solution is encouraged to join the fun.

At some point indexes, global temporary tables, and/or arrays will likely speed it all up and the code could undoubtedly use a large dose of efficiency. But what I'm looking for now, more than anything else, is a way to avoid the brute force approach noted at the bottom of the proc.
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [120] visitors online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?