Solutions to Puzzle of the Week #7

Puzzle of the Week #7

For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.

Expected Result:

EMPNO ENAME       SUM_ASCII
----- ---------- ----------
 7788 SCOTT             397
 7876 ADAMS             358
 7566 JONES             383
 7499 ALLEN             364
 7521 WARD              302
 7934 MILLER            453
 7902 FORD              299
 7369 SMITH             389
 7844 TURNER            480
 7698 BLAKE             351
 7782 CLARK             365
 7654 MARTIN            459
 7839 KING              297
 7900 JAMES             368

Solutions:

Solution/Workaround #1: Oracle 12c and up Only (submitted by Zohar Elkayam)

WITH
    FUNCTION sumascii(p_str in varchar2)  RETURN NUMBER 
    IS 
       x NUMBER:= 0;
    BEGIN
      FOR i IN 1..LENGTH(p_str) LOOP
          x := x + ASCII(SUBSTR(p_str, i, 1)) ;
      END LOOP;
      RETURN x;
    END;
SELECT empno, ename, sumascii(ename) AS sum_ascii
FROM emp
/

Variation of Solution #1 (Recursive function):

WITH
    FUNCTION sumascii(p_str in varchar2)  RETURN NUMBER 
    IS 
    BEGIN
      IF p_str IS NULL THEN 
	RETURN 0;
      END IF;
      RETURN ASCII(p_str) + sumascii(SUBSTR(p_str,2));      
    END;
SELECT empno, ename, sumascii(ename) AS sum_ascii
FROM emp
/

Solution/Workaround #2: Cartesian Product with Generated Numeric Range (by Zohar Elkayam)

SELECT empno, ename, SUM(ASCII(ename_char)) sum_ascii
FROM (SELECT empno, ename, SUBSTR(ename, i, 1) ename_char
      FROM emp, (SELECT LEVEL i
                 FROM dual
                 CONNECT BY LEVEL<=(SELECT MAX(LENGTH(ename))                                      FROM emp)                  )       WHERE LENGTH(ename)>=i
      )
GROUP BY empno, ename
/

Simplified variation of Workaround #2:

SELECT empno, ename, 
       SUM(ASCII(SUBSTR(ename, i, 1))) sum_ascii      
FROM emp, (SELECT LEVEL i
           FROM dual
           CONNECT BY LEVEL<=(SELECT MAX(LENGTH(ename))                                FROM emp)            )  WHERE LENGTH(ename)>=i
GROUP BY empno, ename 
/

Solution/Workaround #3: In-Line Scalar Subquery

SELECT empno, ename, 
      (SELECT SUM(ASCII(SUBSTR(a.ename, LEVEL, 1)))
       FROM dual
       CONNECT BY LEVEL<=LENGTH(a.ename)) AS sum_ascii
FROM emp a
/

Solution #4/Workaround : Recursive WITH clause

WITH x(n, empno, ename, letter) AS (
SELECT 1 AS n, empno, ename, SUBSTR(ename, 1, 1)
FROM emp
UNION ALL
SELECT x.n+1, empno, ename, SUBSTR(ename, n+1, 1)
FROM x
WHERE LENGTH(ename)>=n+1
)
SELECT empno, ename, SUM(ASCII(letter)) sum_ascii
FROM x
GROUP BY empno, ename
/

Solution/Workaround #5: Use DUMP function and Regular Expressions (submitted by Sunitha)

SELECT empno, ename, SUM(REGEXP_SUBSTR(nm, '\d+', 1, occ)) AS sum_ascii
FROM (SELECT empno, ename, REGEXP_REPLACE(DUMP(ename), '.*: (\d.*)$', '\1') nm
      FROM emp), 
     (SELECT LEVEL occ FROM dual CONNECT BY LEVEL <=ANY(SELECT LENGTH(ename) FROM emp))
GROUP BY empno, ename
/

Solution/Workaround #6: Use LATERAL View (Oracle 12c and up)

SELECT empno, ename, sum_ascii
FROM emp e, LATERAL (SELECT SUM(ASCII(SUBSTR(e.ename,LEVEL,1)) ) sum_ascii
                     FROM dual
                     CONNECT BY LEVEL<=LENGTH(e.ename) ) x

Solution/Workaround #7: Use TABLE/CAST/MULTISET function composition

SELECT empno, ename, x.column_value AS sum_ascii
FROM emp e, 
     TABLE(CAST(MULTISET(SELECT SUM(ASCII(SUBSTR(e.ename,LEVEL,1)) ) sum_ascii
                         FROM dual
                         CONNECT BY LEVEL<=LENGTH(e.ename) 
                         ) AS sys.odcinumberlist
                )
          ) x

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

 

Advertisements

Format dbms_output.put_line for text strings over 255 characters long

Every experienced PL/SQL developer knows the importance of dbms_output.put_line procedure which happened to have a limitation that makes it difficult to print large text strings (over 255 bytes). It only allows to print up to 255 characters on a single line.

In his blog post, Tom Kyte suggests using the following simple procedure:

create or replace procedure p( p_string in varchar2 )
is
   l_string long default p_string;
begin
   loop
     exit when l_string is null;
     dbms_output.put_line( substr( l_string, 1, 250 ) );
     l_string := substr( l_string, 251 );
   end loop;
end;
/

The obvious drawback of this method is the lines wrapped up (i.e. broken up) at every 250th random character.

Below is an improved version of this procedure which I use all the time in my work:

CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
                                p_chunk_size NUMBER:=255)
AS
    v_len           NUMBER:=LENGTH(p_text);
    v_cnt           NUMBER:=0;
    v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
    v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
BEGIN
    IF p_text IS NULL THEN
        RETURN;
    END IF;
    
    IF v_len<=v_chunk_size THEN
        DBMS_OUTPUT.PUT_LINE(p_text);
        RETURN;
    END IF;
    
    IF v_LF_postion<=v_chunk_size THEN
        v_chunk_size:=v_LF_postion-1;
    ELSE
        FOR i IN REVERSE 1..v_chunk_size LOOP
            IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma
                v_chunk_size:=i;
                EXIT;
            END IF;
        END LOOP;    
    END IF;
    
    DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));
    pl(p_text=> SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
END;
/

This procedure uses recursive calls to itself and splits the line at every line feed character (ASCII CODE 10) if the line is under 255 characters long, otherwise, it splits it at the most recent space or comma characters that precede 255th symbol.

For the demonstration purpose, let’s see how it will print out its own text:

SQL> DECLARE
  2    v_sql VARCHAR2(4000):=
  3    'CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
  4                                  p_chunk_size NUMBER:=255)
  5      AS
  6          v_len           NUMBER:=LENGTH(p_text);
  7          v_cnt           NUMBER:=0;
  8          v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
  9          v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
 10      BEGIN
 11          IF p_text IS NULL THEN
 12              RETURN;
 13          END IF;
 14
 15          IF v_len<=v_chunk_size THEN
 16              DBMS_OUTPUT.PUT_LINE(p_text);
 17              RETURN;
 18          END IF;
 19
 20          IF v_LF_postion<=v_chunk_size THEN  21              v_chunk_size:=v_LF_postion-1;  22          ELSE  23              FOR i IN REVERSE 1..v_chunk_size LOOP  24                  IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma  25                      v_chunk_size:=i;  26                      EXIT;  27                  END IF;  28              END LOOP;  29  30          END IF;  31  32          DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));  33          pl(p_text=> SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
 34      END;';
 35  BEGIN
 36      pl(v_sql);
 37  END;
 38  /

CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
p_chunk_size NUMBER:=255)
AS
v_len           NUMBER:=LENGTH(p_text);
v_cnt           NUMBER:=0;
v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
BEGIN
IF p_text IS NULL THEN
RETURN;
END IF;
IF v_len<=v_chunk_size THEN
DBMS_OUTPUT.PUT_LINE(p_text);
RETURN;
END IF;
IF v_LF_postion<=v_chunk_size THEN
v_chunk_size:=v_LF_postion-1;
ELSE
FOR i IN REVERSE 1..v_chunk_size LOOP
IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma
v_chunk_size:=i;
EXIT;
                END IF;
            END LOOP;

        END IF;


DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));
        pl(p_text=>
SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
    END;

PL/SQL procedure successfully completed.

The output is not nicely formatted but it is compilable.
Let’s compare it to the output of the p procedure:

SQL> DECLARE
  2    v_sql VARCHAR2(4000):=
  3    'CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
  4                                  p_chunk_size NUMBER:=255)
  5      AS
  6          v_len           NUMBER:=LENGTH(p_text);
  7          v_cnt           NUMBER:=0;
  8          v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
  9          v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
 10      BEGIN
 11          IF p_text IS NULL THEN
 12              RETURN;
 13          END IF;
 14
 15          IF v_len<=v_chunk_size THEN
 16              DBMS_OUTPUT.PUT_LINE(p_text);
 17              RETURN;
 18          END IF;
 19
 20          IF v_LF_postion<=v_chunk_size THEN  21              v_chunk_size:=v_LF_postion-1;  22          ELSE  23              FOR i IN REVERSE 1..v_chunk_size LOOP  24                  IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma  25                      v_chunk_size:=i;  26                      EXIT;  27                  END IF;  28              END LOOP;  29  30          END IF;  31  32          DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));  33          pl(p_text=> SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
 34      END;';
 35  BEGIN
 36      p(v_sql);
 37  END;
 38  /

CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,

p_chunk_size NUMBER:=255)
    AS
        v_len
NUMBER:=LENGTH(p_text);
        v_cnt           NUMBER:=0;
        v_chunk_size
NUMBER:=LEAST(p_chunk_s
ize, 255);
        v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
    BEGIN

IF p_text IS NULL THEN
            RETURN;
        END IF;

        IF
v_len<=v_chunk_size THEN
            DBMS_OUTPUT.PUT_LINE(p_text);

RETURN;

END IF;

        IF v_LF_postion<=v_chunk_size THEN

v_chunk_size:=v_LF_postion-1;
        ELSE
            FOR i IN REVERSE
1..v_chunk_size LOOP
                IF SUBSTR(p_text, i, 1) IN (CHR(32),
CHR(44)) THEN --space or comma

v_chunk_size:=i;
                    EXIT;
                END IF;

END LOOP;

        END IF;

        DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1,
v_chunk_size));
        pl(p_text=> SUBSTR(p_text, v_chunk_size+2),
p_chunk_size=>p_
chunk_size);
    END;

PL/SQL procedure successfully completed.

It’s not so bad, but you cannot simply copy the output and compile it.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.