SQL
12c New Data Mining functions
With the release of Oracle 12c we get new functions/procedures and some updated ones for Oracle Data Miner that is part of the Advanced Analytics option.
The following are the new functions/procedures and the functions/procedures that have been updated in 12c, with a link to the 12c Documentation that explains what they do.
-
CLUSTER_DETAILS is a new function that predicts cluster membership for each row. It can use a pre-defined clustering model or perform dynamic clustering. The function returns an XML string that describes the predicted cluster or a specified cluster.
-
CLUSTER_DISTANCE is a new function that predicts cluster membership for each row. It can use a pre-defined clustering model or perform dynamic clustering. The function returns the raw distance between each row and the centroid of either the predicted cluster or a specified.
-
CLUSTER_ID has been enhanced so that it can either use a pre-defined clustering model or perform dynamic clustering.
-
CLUSTER_PROBABILITY has been enhanced so that it can either use a pre-defined clustering model or perform dynamic clustering. The data type of the return value has been changed from
NUMBERtoBINARY_DOUBLE. -
CLUSTER_SET has been enhanced so that it can either use a pre-defined clustering model or perform dynamic clustering. The data type of the returned probability has been changed from
NUMBERtoBINARY_DOUBLE -
FEATURE_DETAILS is a new function that predicts feature matches for each row. It can use a pre-defined feature extraction model or perform dynamic feature extraction. The function returns an XML string that describes the predicted feature or a specified feature.
-
FEATURE_ID has been enhanced so that it can either use a pre-defined feature extraction model or perform dynamic feature extraction.
-
FEATURE_SET has been enhanced so that it can either use a pre-defined feature extraction model or perform dynamic feature extraction. The data type of the returned probability has been changed from
NUMBERtoBINARY_DOUBLE. -
FEATURE_VALUE has been enhanced so that it can either use a pre-defined feature extraction model or perform dynamic feature extraction. The data type of the return value has been changed from
NUMBERtoBINARY_DOUBLE. -
PREDICTION has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction.
-
PREDICTION_BOUNDS now returns the upper and lower bounds of the prediction as the
BINARY_DOUBLEdata type. It previously returned these values as theNUMBERdata type. -
PREDICTION_COST has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction. The data type of the returned cost has been changed from
NUMBERtoBINARY_DOUBLE. -
PREDICTION_DETAILS has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction.
-
PREDICTION_PROBABILITY has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction. The data type of the returned probability has been changed from
NUMBERtoBINARY_DOUBLE. -
PREDICTION_SET has been enhanced so that it can either use a pre-defined predictive model or perform dynamic prediction. The data type of the returned probability has been changed from
NUMBERtoBINARY_DOUBLE.
Part 3–Getting start with Statistics for Oracle Data Science projects
This is the Part 3 blog post on getting started with Statistics for Oracle Data Science projects.
- The first blog post in the series looked at the DBMS_STAT_FUNCS PL/SQL package, what it can be used for and I give some sample code on how to use it in your data science projects. I also give some sample code that I typically run to gather some additional stats.
- The second blog post will look at some of the other statistical functions that exist in SQL that you will/may use regularly in your data science projects.This is the second blog on getting started with Statistics for Oracle Data Science projects.
- The third blog post will provide a summary of the other statistical functions that exist in the database.
The table below is a collection of most of the statistical functions in Oracle 11.2. The links in the table bring you to the relevant section of the Oracle documentation where you will find a description of each function, the syntax and some examples of each.
The list about may not be complete (I’m sure it is not), but it will cover most of what you will need to use in your Oracle projects.
If you come across or know of other useful statistical functions in Oracle let me know the details and I will update the table above to include them.
Outputting your data using inbuilt SQL Dev formatting
Oracle has build a number of formatting options into SQL Developer to allow you to output your data in some standard formats. This removes the need to use other tools or to write extra code or performs various follow up steps.
All you need to do is to add a comment and use the Scrip button
SELECT /*csv*/ * FROM scott.emp;
SELECT /*xml*/ * FROM scott.emp;
SELECT /*html*/ * FROM scott.emp;
SELECT /*delimited*/ * FROM scott.emp;
SELECT /*insert*/ * FROM SCOTT.EMP;
SELECT /*loader*/ * FROM scott.emp;
SELECT /*fixed*/ * FROM scott.emp;
SELECT /*text*/ * FROM scott.emp;Hint: for some of these it is best to list the schema and table name in upper case
These are comments and not hints so they will not work in SQL*Plus.
OUG Norway April 2013 – New Year’s News
I received an email at 23:24 on the 1st January from the OUG in Norway telling me that I’ve had two presentations accepted for the Annual OUG Norway seminar event. This will be on during the 17th-19th April.
The first day of this event (17th April) will be held in a hotel in Oslo. Then on the morning of 18th April we board the Color Magic cruise for the next two days of the conference. The ferry/cruise will go from Oslo to Kiel in Germany and then back again to Oslo, returning around 10am on Saturday 20th April.
I will be giving two presentations on the Oracle Advanced Analytics Option. The first presentation, ‘Using Predictive Analytics in Oracle’, will give an overview of the Oracle Advanced Analytics Option and will then focus on the Oracle Data Miner work-flow tool. This will presentation will include a live demo of using Oracle Data Miner to create some data mining models.
The second presentation, ‘How to Deploy and Use your Oracle Data Miner Models in Production’, builds on the examples given in the first presentation and will show how you can migrate, user and update your Oracle Data Miner models using the features available in SQL and PL/SQL. Again a demo will be given.
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.
Adding numbers between two values
My son asked me the other day, what is the total number if you add all the numbers between Zero and 100.
We could have sat down to work it out with some paper and a pen, but instead I decided to introduce him to the world of Oracle, SQL and PL/SQL
The first step we took was to work out how you would do it on paper for some of the numbers. Then we translated this into some PL/SQL code. OK I did a lot this but he did seem to understand and follow what I was doing.
So the following Function is what we ended up with to add all the numbers between two numbers and return the answer.
CREATE or REPLACE function AddNumsBetween
( pStartNum IN NUMBER,
pEndNum IN NUMBER)
RETURN NUMBER
IS
vSum Number := 0;
BEGIN
FOR i IN pStartNum .. pEndNum LOOP
vSum := vSum + i;
END LOOP;
return vSum;
END;
/
The next step was to write some code to call this function. The code prompts the user to enter the Start number and End number.
set serveroutput on
DECLARE
vStartNum NUMBER := 0;
vEndNum NUMBER := 100;
vAnswer NUMBER := 0;
BEGIN
vStartNum := &Start_Number;
vEndNum := &End_Number;
vAnswer := AddNumsBetween(vStartNum, vEndNum);
dbms_output.put_line(‘The sum of numbers between ‘||vStartNum||’ and ‘||vEndNum||’ is ‘||vAnswer||’.’);
END;
/
To answer by son’s original query, we used Zero and 100 as our inputs.
The answer to the question is 5,050.
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 : 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.
- ← Previous
- 1
- …
- 4
- 5
Ora-lytics 