Update on : Adding numbers between

Over the past few days I’ve had a number of emails and comments based on my previous post.  My previous post was called ‘Adding numbers between two values’. I included some PL/SQL code that can be used to add up the numbers between two values. I mentioned that this was a question that my pre-teen son (a few year pre-teen) had asked me.

There are two main solutions to the same problem. One involves just using a SELECT and the other involves using recursion. I will come back the these alternative solutions below.

But let me start off with a bit more detail and background to why I approached the problem the way that I did. The main reason is that my son is a pre-teen. Over the past couple of years he as expressed an interest in what his daddy does. We even have matching ORACLENERD t-shirts

When I was working through the problem with my son I wanted to show him how to take a problem and by breaking it down into its different parts we can work out an overall solution. We can then take each of these parts and translate them into code. In this case some PL/SQL, yes it is a bit nerdy and we do have the t-shirt. The code that I gave illustrates many different parts of the language and hopefully he will use some of these features as we continue on our learning experience.

It is good sometimes to break a problem down into smaller parts. That way we can understand it better, what works and what does not work, if something does not work then we will know what bit and also leads to easier maintenance. At a later point as you develop an in-depth knowledge of certain features of a language you can then rewrite what you have to be more efficient.

All part of the learning experience.

Ok lets take a look at the other ways to answer this problem. The first approach is to just use a single SELECT statement.

SELECT sum(rownum + &&Start_Number - 1)
FROM    dual
CONNECT by level <= &End_Number - &&Start_Number + 1;

An even simpler way is

SELECT sum(level)
FROM    dual
CONNECT BY level between &Start_Number and &End_Number;

These queries create a hierarchical query that produce all the numbers between the Start_Number parameter and the End_Number parameter. The SUM is needed to all all the numbers/rows produced.  This is nice and simple (but not that easy for by son at this point).

Thank you to everyone who contacted me about this. I really appreciated your feedback and please keep your comments coming for all my posts.

ASCII to character conversion in Oracle

Here is code code that will produce formatted output of the characters and their ascii values. The formatting is broken into lower case letters, uppercase letters, characters with an ascii value less than an ‘a’ and characters whose ascii characters are greater than a ‘z.

Code

set serveroutput on FORMAT WRAPPED
DECLARE
    vTab           VARCHAR2(5) := CHR(9);
    vNum          NUMBER := 0;
    vString       VARCHAR2(80) := '';
BEGIN  
   --
   -- Formatted lower case letter to ASCII values
   --
   dbms_output.put_line('Formatted Lower Case Letters to ASCII values');
   dbms_output.put_line('-------------------------------------------------------');
   FOR i IN ASCII('a') .. ASCII('z') LOOP
      IF vNum < 6 THEN
         vString := vString||CHR(i)||' : '||i||vTab;
         vNum := vNum + 1;
      ELSIF vNum = 6 then
         dbms_output.put_line(vString||CHR(i)||' : '||i);
         vNum := 0;
         vString := '';
      ELSE
         dbms_output.put_line('ERROR');
      END IF;
   END LOOP;
   dbms_output.put_line(vString);

   --
   -- Formatted upper case letter to ASCII values
   --
   vString := '';
   vNum := 0;

   dbms_output.new_line;
   dbms_output.new_line;
   dbms_output.put_line('Formatted Upper Case Letters to ASCII values');
   dbms_output.put_line('-------------------------------------------------------');
   FOR i IN ASCII('A') .. ASCII('Z') LOOP
      IF vNum < 6 THEN
         vString := vString||CHR(i)||' : '||i||vTab;
         vNum := vNum + 1;
      ELSIF vNum = 6 then
         dbms_output.put_line(vString||CHR(i)||' : '||i);
         vNum := 0;
         vString := '';
      ELSE
         dbms_output.put_line('ERROR');
      END IF;
   END LOOP;
   dbms_output.put_line(vString);

   --
   -- Formatted chars less than 'a' to ASCII values
   --
   vString := '';
   vNum := 0;

   dbms_output.new_line;
   dbms_output.new_line;
   dbms_output.put_line('Formatted Letters, less than a  to ASCII values');
   dbms_output.put_line('-------------------------------------------------------');
   FOR i in 0 .. ASCII('a')-1 LOOP
      IF vNum < 6 THEN
         vString := vString||CHR(i)||' : '||i||vTab;
         vNum := vNum + 1;
      ELSIF vNum = 6 then
         dbms_output.put_line(vString||CHR(i)||' : '||i);
         vNum := 0;
         vString := '';
      ELSE
         dbms_output.put_line('ERROR');
      END IF;
   END LOOP;
   dbms_output.put_line(vString);

   --
   -- Formatted chars greater than 'Z' to ASCII values
   --
   vNum := 0;
   vString := '';

   dbms_output.new_line;
   dbms_output.new_line;
   dbms_output.put_line('Formatted Letters, greater than z  to ASCII values');
   dbms_output.put_line('-------------------------------------------------------');
   FOR i IN ASCII('z') .. ASCII('z')+133 LOOP
      IF vNum < 6 THEN
         vString := vString||CHR(i)||' : '||i||vTab;
         vNum := vNum + 1;
      ELSIF vNum = 6 then
         dbms_output.put_line(vString||CHR(i)||' : '||i);
         vNum := 0;
         vString := '';
      ELSE
         dbms_output.put_line('ERROR');
      END IF;
   END LOOP;
   dbms_output.put_line(vString);

END;
/

Output

Formatted Lower Case Letters to ASCII values
-------------------------------------------------------
a : 97  b : 98  c : 99  d : 100 e : 101 f : 102 g : 103
h : 104 i : 105 j : 106 k : 107 l : 108 m : 109 n : 110
o : 111 p : 112 q : 113 r : 114 s : 115 t : 116 u : 117
v : 118 w : 119 x : 120 y : 121 z : 122

Formatted Upper Case Letters to ASCII values
-------------------------------------------------------
A : 65  B : 66  C : 67  D : 68  E : 69  F : 70  G : 71
H : 72  I : 73  J : 74  K : 75  L : 76  M : 77  N : 78
O : 79  P : 80  Q : 81  R : 82  S : 83  T : 84  U : 85
V : 86  W : 87  X : 88  Y : 89  Z : 90

Formatted Letters, less than a  to ASCII values
-------------------------------------------------------
  : 0   ☺ : 1   ☻ : 2   ♥ : 3   ♦ : 4   ♣ : 5   ♠ : 6
: 7 : 8                 : 9
: 13   ♂ : 11  ♀ : 12
♫ : 14  ☼ : 15  ► : 16  ◄ : 17  ↕ : 18  ‼ : 19  ¶ : 20
§ : 21  ▬ : 22  ↨ : 23  ↑ : 24  ↓ : 25  → : 26  ← : 27
∟ : 28  ↔ : 29  ▲ : 30  ▼ : 31    : 32  ! : 33  " : 34
# : 35  $ : 36  % : 37  & : 38  ' : 39  ( : 40  ) : 41
* : 42  + : 43  , : 44  - : 45  . : 46  / : 47  0 : 48
1 : 49  2 : 50  3 : 51  4 : 52  5 : 53  6 : 54  7 : 55
8 : 56  9 : 57  : : 58  ; : 59  < : 60  = : 61  > : 62
? : 63  @ : 64  A : 65  B : 66  C : 67  D : 68  E : 69
F : 70  G : 71  H : 72  I : 73  J : 74  K : 75  L : 76
M : 77  N : 78  O : 79  P : 80  Q : 81  R : 82  S : 83
T : 84  U : 85  V : 86  W : 87  X : 88  Y : 89  Z : 90
[ : 91  \ : 92  ] : 93  ^ : 94  _ : 95  ` : 96

Formatted Letters, greater than z  to ASCII values
-------------------------------------------------------
z : 122 { : 123 | : 124 } : 125 ~ : 126 ⌂ : 127 Ç : 128
ü : 129 é : 130 â : 131 ä : 132 à : 133 å : 134 ç : 135
ê : 136 ë : 137 è : 138 ï : 139 î : 140 ì : 141 Ä : 142
Å : 143 É : 144 æ : 145 Æ : 146 ô : 147 ö : 148 ò : 149
û : 150 ù : 151 ÿ : 152 Ö : 153 Ü : 154 ø : 155 £ : 156
Ø : 157 × : 158 ƒ : 159 á : 160 í : 161 ó : 162 ú : 163
ñ : 164 Ñ : 165 ª : 166 º : 167 ¿ : 168 ® : 169 ¬ : 170
½ : 171 ¼ : 172 ¡ : 173 « : 174 » : 175 ░ : 176 ▒ : 177
▓ : 178 │ : 179 ┤ : 180 Á : 181 Â : 182 À : 183 © : 184
╣ : 185 ║ : 186 ╗ : 187 ╝ : 188 ¢ : 189 ¥ : 190 ┐ : 191
└ : 192 ┴ : 193 ┬ : 194 ├ : 195 ─ : 196 ┼ : 197 ã : 198
à : 199 ╚ : 200 ╔ : 201 ╩ : 202 ╦ : 203 ╠ : 204 ═ : 205
╬ : 206 ¤ : 207 ð : 208 Ð : 209 Ê : 210 Ë : 211 È : 212
ı : 213 Í : 214 Î : 215 Ï : 216 ┘ : 217 ┌ : 218 █ : 219
▄ : 220 ¦ : 221 Ì : 222 ▀ : 223 Ó : 224 ß : 225 Ô : 226
Ò : 227 õ : 228 Õ : 229 µ : 230 þ : 231 Þ : 232 Ú : 233
Û : 234 Ù : 235 ý : 236 Ý : 237 ¯ : 238 ´ : 239 ­ : 240
± : 241 ‗ : 242 ¾ : 243 ¶ : 244 § : 245 ÷ : 246 ¸ : 247
° : 248 ¨ : 249 · : 250 ¹ : 251 ³ : 252 ² : 253 ■ : 254
  : 255

PL/SQL procedure successfully completed.

Observations

There are two things that stand out in this. The first is there is sound produced. This is because one of the characters is defined this way. It is ASCII number 7. This can be repeated using the following:

select chr(7) from dual.

The second is the formatting of the lines for ascii codes 8 to 12. We can see that one of the ascii codes does not get displayed and the ordering of this is not as expected. This is due to ascii 10 being a line feed.

Scott/Tiger in Oracle

Almost everyone starting out with Oracle have come across the schema SCOTT.

So who was SCOTT?

Bruce Scott was employee number 4 at Oracle back in the 1970s and was part of the team responsible for architecting and writing version 1, 2 and 3 of the Oracle Database.

So he set up the first schema for testing and demo-ing how to use SQL in the Oracle Database. The EMP and DEPT tables and the data that we still see in this schema is still based on the original data that he created.

How did he come up with the TIGER password ?  Tiger was the name of his cat!!!

Sten Vesterli of Scott/Tiger Group

Bruce co-founded Gupta Technology (now known as Centura Software) in 1984 with Umang Gupta, and later became CEO and founder of PointBase, Inc.

Data Visualization Videos & Resources

Here is a selection of videos and websites on Data Visualisations.

Hans Rosling videos of his TED talks

• World Population Growth
• Global Population Growth (TED) 
• Asia’s Rise – How and When
• HIV: New facts and stunning data visuals
• Video for the BBC

Other videos

• Journalism in the Age of Data
• Make Data More Human
• The Beauty of Data Visualization

Useful Websites

• Visual Analytics
• Solving Problems with Visual Analytics book
• IBM Many Eyes
• Top 15 Data Visualization Tips
• Visualizing.org

What version of the Oracle Database do I need ?

Oracle has recently made available a very useful webpage that that lists the functionality available for each version of the 11g Database. So before you decide which version of the database to purchase, check out this webpage.

http://www.oracle.com/us/products/database/product-editions-066501.html

Great set of Data Design Articles

For anyone starting out on data and database design there are lots and lots of books and articles to help get them started.

But for those people who have been doing database design for a while, it is always good to reflect on your approaches and techniques.

I recently attended a presentation by Steve Hoberman. If you ever get a chance to attend a data design presentation by him, I would highly recommend it.

He addition to his presentations and database design courses, he also writes for the website Information Management.

His series of articles can be found at

http://www.information-management.com/authors/32163.html

and his company website is

http://www.stevehoberman.com/