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.

Data Science Is Multidisciplinary

[Update :October 2016.  There appears to be some discussion about the Venn diagram I've proposed below. The central part of this diagram is not anything I can up with. It was a commonly used Venn diagram for Data Mining. Thanks to Polly Michell-Guthrie for providing the original reference for the Venn. I just added the outer ring of additional skills needed for the new area of Data Science. This was just my view of things back in 2012. Things have moved on a bit since then]

A few weeks ago I had a blog post called Domain Knowledge + Data Skills = Data Miner.
In that blog post I was saying that to be a Data Scientist all you needed was Domain Knowledge and some Data Skills, which included Data Mining.
The reality is that the skill set of a Data Scientist will be much larger. There is a saying ‘A jack of all trades and a master of none’. When it comes to being a data scientist you need to be a bit like this but perhaps a better saying would be ‘A jack of all trades and a master of some’.
I’ve put together the following diagram, which includes most of the skills with an out circle of more fundamental skills. It is this outer ring of skills that are fundamental in becoming a data scientist. The skills in the inner part of the diagram are skills that most people will have some experience in one or more of them. The other skills can be developed and learned over time, all depending on the type of person you are.

Can we train someone to become a data scientist or are they born to be a data scientist. It is a little bit of both really but you need to have some of the fundamental skills and the right type of personality. The learning of the other skills should be easy(ish)
What do you think?  Are their Skill that I’m missing?

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

OBIEE VirtualBox VM Available

I have a few Updates since I posted the blog on 14th Sept
1. The OBIEE VMS are now available on the Virtural Box Pre-Built VM webpage, as of 15th Sept. Thanks to @oraclenerd for requesting this
http://www.oracle.com/technetwork/community/developer-vms-192663.html
2. Thanks to @rnm1978 for pointing out that the OBIEE VM is very resource intensive. The min. spec. given is not really enough. You really need more than 4GB for this VM to run.
3. The SampleApp that the OBIEE VM contains also has some examples of how Oracle Data Miner can be used in an application

I’ve recently come across an VM of Oracle Business Intelligence Enterprise Edition, which has the sample application already build and ready for you to use.

The VM files are available at the following link for download.

http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html

There are 2 files that you need to download to create the VM in VirtualBox. These are the VB Image key File (.ovf) and the VB Image Disk Files (.vmdk). The second file is ~25G, so it a while to download!

There is also a VB Image-Deployment Guide.

If you already have OBIEE installed and you don’t want to setup the VM, the setup files and data files are also available.

Installing Oracle Apex 4.0 on 11.2g

Over the past few days I’ve been trying to install Oracle Apex 4 on my 11.2g database.  I say trying as I’ve made a number of attempts with no success. I started with the install instructions that come with Apex 4. Generally Oracle installs and install instructions have improved greatly since the 6, 7 and 8i versions.

I had high hopes of an easy install (as indicated by the various Oracle Apex books), but no matter what version of the install instructions I found there always seemed to be a step missing.

I finally came across one set of instructions that worked for me. The following steps are what I performed to get Apex 4 working.

1. Download Apex 4 from OTN to the directory

c:\apex_download

2. Unzip the Apex 4 download file. It will create the directory

c:\apex_download\Apex

3. Login into SQL*Plus as SYS with SYSDBA

4. Run the Apex 4 install script

c:\apex_download\Apex\apexins.sql SYSAUX SYSAUX TEMP /i/

where SYSAUX is the tablespace for Apex, TEMP is the temporary tablespace and /i/ is needed for possible upward compatability

This steps can take up to 30 minutes to run

5. Load the Apex images into the database.

    - Got to the c:\apex_download\Apex directory.

    - Log into SQL*Plus as SYS with SYSDBA

    - run   @apxldimg.sql

    - You will be asked to enter the directory for the images. Make sure that you enter the correct directory, otherwise it will not work. In my case it is

              c:\apex_download

6. Run the Configuration script. This will set up the XDB HTTP connection details.

        @apxconf.sql

   - enter the port number : 8080

   - password

7. Unlock the required schema

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SQL > ALTER USER XDB ACCOUNT UNLOCK;
SQL > ALTER USER APEX_040000 ACCOUNT UNLOCK;
SQL > ALTER USER FLOWS_FILES ACCOUNT UNLOCK;
SQL > ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

8. Open Apex. Open your browser and enter

     http://localhost:8080/apex

     there is a default workspace created

      Workspace = internal

      Username = admin

       Password = admin

6. Change the password. The fist time you login you will be prompted to change the password. The new password needs to have a number, upper and lower case characters and one special character

7. To get the the Apex Admin page

     http://localhost:8080/apex/apex_admin

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/