tag:blogger.com,1999:blog-65415952320717686242024-02-19T04:40:14.912-08:00Researching Digital DataAnonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.comBlogger53125tag:blogger.com,1999:blog-6541595232071768624.post-76126404327916149832016-01-22T01:53:00.003-08:002016-01-22T01:58:14.910-08:00Trying to understand the RSA algorithm with a Perl scriptI tried to understand the <a href='https://en.wikipedia.org/wiki/RSA_%28cryptosystem%29' class='tq84'>RSA encryption algorithm</a> and wrote two Perl scripts. Maybe someone else finds this useful, so I share it.
<p>The first script (<a href='https://github.com/ReneNyffenegger/renenyffenegger.blogspot.com/blob/master/2016/01-January/22-RSA-with-perl/generate_keys.pl' class='tq84'>generate_keys.pl</a>) creates the public and the private key. It takes two prime numbers as arguments, in this context commonly referred to as <code>p</code> and <code>n</code>:
<p>
<div class='tq84-code'>U:\> generate_keys.pl 643 947
public key
e = 65537
n = 608921
private key
d = 412697
n = 608921</div>
<p>
Now, I can encrypt a message with the second script (<a href='https://github.com/ReneNyffenegger/renenyffenegger.blogspot.com/blob/master/2016/01-January/22-RSA-with-perl/encrypt_decrypt.pl' class='tq84'>encrypt_decrypt.pl</a>) and the public key: the first two parameters are the public key, the third parameter the message to be encrypted.
<p>The message I want to encrypt is the number <code>42</code>:
<div class='tq84-code'>U:\> encrypt_decrypt.pl 65537 608921 42
166097
</div>
<p>In order to decrypt <code>166097</code>, I use <a href='https://github.com/ReneNyffenegger/renenyffenegger.blogspot.com/blob/master/2016/01-January/22-RSA-with-perl/encrypt_decrypt.pl' class='tq84'>encrypt_decrypt.pl</a> again, this time with the private key:
<div class='tq84-code'>U:\> encrypt_decrypt.pl 412697 608921 166097
42
</div>
<h2 class='tq84-subtitle'>Links</h2>
<a class='tq84' href='http://southernpacificreview.com/2014/01/06/rsa-key-generation-example/'>RSA key generation example with python</a> was a very helpful page for me.
<p>
<a class='tq84' href='https://en.wikipedia.org/wiki/Modular_multiplicative_inverse'>Modular multiplicative inverse</a> on wikipedia.
<p>
<a class='tq84' href='https://en.wikibooks.org/wiki/Algorithm_Implementation/Mathematics/Extended_Euclidean_algorithm'>Extended euclidean algorithm</a> on wikipedia.
<p>
<a class='tq84' href='https://en.wikipedia.org/wiki/RSA_%28cryptosystem%29'>RSA (cryptosystem)</a> on wikipedia.
<p>
<a class='tq84' href='https://github.com/ReneNyffenegger/renenyffenegger.blogspot.com/tree/master/2016/01-January/22-RSA-with-perl'>The scripts on github</a>.
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-82906802158196756512016-01-20T05:32:00.002-08:002016-01-20T05:32:51.593-08:00Using birdy to tweet a message<a class='tq84' href='https://github.com/inueni/birdy'>birdy</a> makes it easy to tweet a message:
<p><pre style="border: 1px solid black;"><code><span style="color: #76381d;">u:\20-birdy> tweet.py "Hello World!"</span></code></pre>
<p>Here's the script (<a class='tq84' href='https://github.com/ReneNyffenegger/renenyffenegger.blogspot.com/blob/master/2016/01-January/20-birdy/tweet.py'>tweet.py</a>):
<p>
<div class='tq84-code'>import os
import sys
from birdy.twitter import UserClient
if len(sys.argv) < 2:
print "specify text to tweet"
sys.exit()
tweet_text = sys.argv[1]
tw = UserClient(os.environ['TWITTER_CONSUMER_KEY' ],
os.environ['TWITTER_CONSUMER_SECRET' ],
os.environ['TWITTER_ACCESS_TOKEN' ],
os.environ['TWITTER_ACCESS_TOKEN_SECRET'])
tw.api.statuses.update.post(status = tweet_text)</div>
<h2 class='tq84-links'>Links</h2>
<a class='tq84' href='http://renenyffenegger.blogspot.ch/2016/01/experimenting-with-twitter-api-client.html'>Experimenting with the twitter API client birdy</a>
<p><a class='tq84' href='https://github.com/ReneNyffenegger/renenyffenegger.blogspot.com/blob/master/2016/01-January/20-birdy/tweet.py'>tweet.py</a>
<p><a class='tq84' href='https://github.com/inueni/birdy'>birdy</a>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-29001133219350578692016-01-20T04:18:00.001-08:002016-01-20T04:19:29.664-08:00Experimenting with the twitter API client birdyI stumbled upon the twitter API client <a class='tq84' href='https://github.com/inueni/birdy'>birdy</a> whose description reads <i>a super awesome Twitter API client for Python</i>.
<p>Of course, inquiring minds want to know, so I wrote a little script (<a href='https://github.com/ReneNyffenegger/renenyffenegger.blogspot.com/blob/master/2016/01-January/20-birdy/account_info.py' class='tq84'>account_info.py</a>). The script takes one argument: the name of a twitter account. In the following screenshot, I read some account data
for the account <a href='https://twitter.com/twitterapi' class='tq84'>twitterapi</a>:
<p>
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5hcYEy3756TYcQ92tgJWCNfatOlFa-3f2uJNhocBr0ZgKnkf50EvvVRDs_QJjFMYVSltOPA5_EArQKaCWnrSNdu-nVR0UaAD1X6vNR5y_SIeI8vY9yyJK3W3krP6MY3Bkmqcyqpxm30s/s1600/birdy_account-info.PNG" />
<p>For example, the script reports that <code>twitterapi</code> has 5.3 million followers, but follows only 48 other accounts.
<p>The script is also able to read the current status. For a reason I don't understand, the status for <code>twitterapi</code> seems always to be "<i>@TheNiceBot aww thanks, you're lovely too! :-)</i>". The status is correct, however, for other accounts.
<p>Here's the script
<div class='tq84-code'>import os
import sys
from birdy.twitter import UserClient
if len(sys.argv) < 2:
print "specify screen name"
sys.exit()
screen_name = sys.argv[1]
tw = UserClient(os.environ['TWITTER_CONSUMER_KEY' ],
os.environ['TWITTER_CONSUMER_SECRET' ],
os.environ['TWITTER_ACCESS_TOKEN' ],
os.environ['TWITTER_ACCESS_TOKEN_SECRET'])
r = tw.api.users.show.get(screen_name=screen_name)
profile_url=r.data['profile_background_image_url_https']
# for key, value in r.data.iteritems() :
# print key
status_id=str(r.data['status']['id_str'])
print ""
print "Current Status"
print " of " + r.data['status']['created_at']
print " url=https://twiter.com/" + screen_name + "/status/" + status_id
print "------------------------------------------------------"
print r.data['status']['text']
print "--------------"
print ""
print "Name: " + r.data['name' ]
print "Description: " + r.data['description' ]
print "Followers: " + str(r.data['followers_count'])
print "Following: " + str(r.data['friends_count' ])
print "Tweets: " + str(r.data['statuses_count' ])
print "Language: " + r.data['lang' ]</div>
<div class='tq84-links'>Links</div>
<div class='tq84-github'>
<a class='tq84' href="https://github.com/ReneNyffenegger/renenyffenegger.blogspot.com/blob/master/2016/01-January/20-birdy/account_info.py">account_info</a> on github.
</div>
<div class='tq84-github'>
<a class='tq84' href="https://github.com/inueni">Inueni's</a> github repository <a class='tq84' href='https://github.com/inueni/birdy'>birdy</a>.
</div>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com2tag:blogger.com,1999:blog-6541595232071768624.post-35141919545996868312016-01-19T22:56:00.000-08:002016-01-19T22:56:27.446-08:00Oracle: Turning a select statement into an Excel fileI don't know how many times a CFO has approached me and asked <i>I know, the data for XYZ is somewhere in the database. Could you quickly get me XYZ and send it to me as Excel</i>.
<p>
Usually, getting the data out of the database was fairly easy with a SQL select statement, yet, bringing the data into an excel worksheet was sort of a recurring PITA: I would start SQL Developer, execute the select statement, copy the result set (ctrl-c), open Excel, paste the result set (ctrl-v) then I'd adjust the widths of the columns, and <i>only then</i> I'd save the resulting excel sheet.
<p>
Not that these steps are too hard, but I always felt that should be easier. So, I have written the procedure <code>xlsx_writer.sql_to_xlsx</code>. This procedure takes an SQL statement and the name of an Excel file to be written, executes the SQL statement and writes the Excel file.
<p>
In SQL*Plus, that would look like:
<br>
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkNDlgY-yDGfIzPpk2rbce-VekvugKecLtNTSd4TXuzGjYQQqp6mkIArUaVLAaTdO3O9mAWAV2mFoZeOodSy6TaOQ8wCgiOGHv7B9NQ824RKm9ZlEYUU-ktDjA5kz7Q2ln0Yf1QISHyTU/s1600/xlsx_writer.sql_to_xlsx.PNG" />
<p>Of course, this can be written in one line, I have used four lines because of the width limit in this blog.
<div class='tq84-links'>Links</div>
<div class='tq84-github'>
Source code on <a class='tq84' href="https://github.com/ReneNyffenegger/xlsx_writer-Oracle">github</a>
</div>
<p><a class='tq84' href='http://renenyffenegger.ch/Oracle/Libraries/xlsx-writer.html'>xlsx_writer on my homepage</a>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com8tag:blogger.com,1999:blog-6541595232071768624.post-74770987367382303972016-01-19T22:09:00.001-08:002016-01-19T22:24:18.918-08:00Writing a blob into a file with PL/SQL with a single line of code.I have updated my <a class='tq84' href='http://www.renenyffenegger.ch/Oracle/Libraries/blob_wrapper/index.html'>blob_wrapper</a> so that it can write a blob with a single line:
<p>
<div class='tq84-code'>blob_wrapper.to_file('c:\temp\abc.txt', my_blob);
</div>
<p>
This creates the file <code>c:\temp\abc.txt</code> and fills it with the content of <code>my_blob</code>
<p>
A varchar2 can be converted into a blob and then written into the file like so
<div class='tq84-code'>begin
blob_wrapper.to_file(
'c:\temp\two_params.txt',
utl_raw.cast_to_raw('foo bar baz')
);
end;
/
</div>
<p>Programming PL/SQL <i>almost</i> makes fun again.
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-55943261751146599492016-01-13T07:09:00.000-08:002016-01-15T05:50:50.383-08:00Creating Excel (.xlsx) files with Oracle PL/SQLThe XLSX Excel file format is actually a zipped archive of some xml files with the suffix <code>.xlsx</code> rather than <code>.zip</code>
This makes it possible to create XLSX files with (almost) any programming language that can create files and zip them. Since I am working with PL/SQL every now and then and I needed to create xlsx files for a reporting solution, I crated a small library: <b>XLSX writer for Oracle</b>.
The source code for this library is on <a class='tq84' href="https://github.com/ReneNyffenegger/xlsx_writer-Oracle">this github repository</a>, some examples are on
<a class='tq84' href="http://www.renenyffenegger.ch/Oracle/Libraries/xlsx-writer.html">my homepage</a>.
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com24tag:blogger.com,1999:blog-6541595232071768624.post-9028816334536262092015-07-17T04:38:00.001-07:002016-01-13T05:53:07.670-08:00Using Apophysis to create background imagesWith <a href="http://www.apophysis.org/">Apophysis</a>, it is possible to create wallpapers or background images such as the following image:
<br>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4tLYCCD0tNJuaDuSURYxMw9hzshbrOoxTdRkl5N307bC0JiXCuPTeTy6Qgkbv-nyX1LrwMQ38QDY4DhTfc5QjGm-vwQ-UuqRDfYwqTQyPp3X5RfafxhZsgojY7R5VwoBakoDMJBRV6SY/s1600/background.jpg" target="_blank" >
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4tLYCCD0tNJuaDuSURYxMw9hzshbrOoxTdRkl5N307bC0JiXCuPTeTy6Qgkbv-nyX1LrwMQ38QDY4DhTfc5QjGm-vwQ-UuqRDfYwqTQyPp3X5RfafxhZsgojY7R5VwoBakoDMJBRV6SY/s1600/background.jpg" width="400" height="300"/>
</a>
<p>I have produced this image with a variation of <a class='tq84' href="https://github.com/ReneNyffenegger/Graphic-Design-and-Font/blob/master/Apophysis/Background-renenyffenegger.ch-2015-07-17.flame">this script</a>. Unfortunatly, I lost the script that created the picture above, but this script sort of comes close.
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-76318695590061276062015-07-08T23:04:00.003-07:002016-01-13T05:54:54.909-08:00Whether you like it or not, no one should ever claim to be a data analyst until he or she has done string manipulation.I am reading <a class='tq84' href="http://gastonsanchez.com/">Gaston Sanchez'</a> book <a class='tq84' href="http://gastonsanchez.com/Handling_and_Processing_Strings_in_R.pdf">Handling and Processing Strings in R</a> (pdf).
<p>In the preface, I found the following quote, to which I wholeheartedly agree:
<div class="tq84-quote">
Perhaps even worse is the not so uncommon believe that string manipulation is a secondary
non-relevant task. People will be impressed and will admire you for any kind of fancy model,
sophisticated algorithms, and black-box methods that you get to apply. Everybody loves the
<i>haute cuisine</i> of data analysis and the top notch analytics. <b>But when it comes to processing
and manipulating strings, many will think of it as washing the dishes or pealing and cutting
potatos.</b> If you want to be perceived as a data chef, you may be tempted to think that you
shouldn’t waste your time in those boring tasks of manipulating strings. Yes, it is true that
you won’t get a Michelin star for processing character data. But you would hardly become
a good data cook if you don’t get your hands dirty with string manipulation. And to be
honest, it’s not always that boring. <b>Whether you like it or not, no one should ever claim
to be a data analyst until he or she has done string manipulation.</b>
</div>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-85469353455826549192015-06-20T01:17:00.003-07:002015-06-20T01:17:37.730-07:00Little things that make life easier #9: Using data.entry in rWith <code>data.entry()</code>, it's easy to visually fill (small) matrices in r.
<p>Let's see it in action. First, I create a 4x3 matrix:
<div class='tq84-code'>mx <- matrix(nrow=4, ncol=3)
show(mx)</div>
<br>
<div class='tq84-out'> [,1] [,2] [,3]
[1,] NA NA NA
[2,] NA NA NA
[3,] NA NA NA
[4,] NA NA NA</div>
<p>The matrix is created with the cells' values being <code>NA</code> Now, in order to assign values to these cells, I use
<div class='tq84-code'>data.entry(mx)</div>
<br>This opens a small window where I can enter the data.
<br>This is how the cells were filled before my editing them:<br>
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFnle1_h-vR98k21ldsUDojOrWAYl3sf-6Vhkl0IgNU02gGrQj-LE31riSYLgj4sssEEs1_pp-rihLMp6Xtg2XkOvVbpFfcAXRsFoS0oheOwB_Mf2uy0j5Z78r31ohr4SXcOQmwGWXILo/s1600/dataentry-1.png" />
<p>And here's how they looked after my editing them just before I used <i>File > Close</i>:<br>
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU5GDpmqhud5ID2FpldWokeLCuTziVi1lSMVKDf97SKhfQMuNbVISb3f7ygSW8-C3HgzYW-bRX4cgOQIWT-zeGNoiK9oAI0SxGbG3P5k0D0Av-xgyu0h0qFOQWE2uSzPI-XauVfTFr8M0/s1600/dataentry-2.png" />
<br>Back in the shell, the matrix has indeed changed its values:<br>
<div class='tq84-code'>show(mx)</div>
<br>
<div class='tq84-out'> var1 var2 var3
[1,] 2 4 2
[2,] 12345 8 42
[3,] 5 6 489
[4,] 9 22 11</div>
<p>Pretty cool, imho.
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-66699447767673949272015-03-05T23:15:00.002-08:002016-01-13T05:55:30.652-08:00Is a sequence incremented in a failed insert?Here's a sequence
<div class='tq84-code'>create sequence tq84_failed_insert_seq start with 1 increment by 1;</div>
And an insert statement:
<div class='tq84-code'>insert into tq84_failed_insert values(
tq84_failed_insert_seq.nextval,
lpad('-', i, '-')
);</div>
<p><i>If</i> the insert statement fails, is the sequence still incremented?
<p>Let's try it with a test. The table:
<div class='tq84-code'>create table tq84_failed_insert(
i number primary key,
j varchar2(20)
);</div>
Some insert statements:
<div class='tq84-code'>insert into tq84_failed_insert values (5, lpad('-', 5, '-'));
insert into tq84_failed_insert values (9, lpad('-', 9, '-'));</div>
and an anonymous block:
<div class='tq84-code'>begin
for i in 1 .. 10 loop
begin
insert into tq84_failed_insert values(
tq84_failed_insert_seq.nextval,
lpad('-', i, '-')
);
exception when dup_val_on_index then
null;
end;
end loop;
end;
/</div>
<p>After running this anonymous block, the table contains:
<div class='tq84-code'>select * from tq84_failed_insert order by i;</div>
<br>Returning:
<div class='tq84-out'> I J
---------- --------------------
1 -
2 --
3 ---
4 ----
5 -----
6 ------
7 -------
8 --------
9 ---------
10 ----------</div>
So, the value of <ocde>I</code> is ascending in steps of 1, showing that the value of <code>netxtval</code> is "wasted" if the insert statement fails.
<div class='tq84-links'>Links</div>
<div class='tq84-github'>
Source code on <a class='tq84' href="https://github.com/ReneNyffenegger/oracle-patterns/blob/master/DatabaseObjects/Sequences/failed_insert.sql">github</a>
</div>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-90416430092125284582015-02-16T22:29:00.001-08:002015-02-16T22:29:11.440-08:00The most important wget command line options (flags)Note to self: remember those wget flags and you'll be fine:
<div class='tq84-code'>-r, --recursive specify recursive download.
-H, --span-hosts go to foreign hosts when
recursive.
-l, --level=NUMBER maximum recursion depth (inf
or 0 for infinite).
-np, --no-parent don't ascend to the
parent directory.
-nd, --no-directories don't create directories.
-x, --force-directories force creation of directories.
-nc, --no-clobber skip downloads that would
download to existing files.
-k, --convert-links make links in downloaded HTML
point to local files.
-p, --page-requisites get all images, etc. needed
to display HTML page.
-A, --accept=LIST comma-separated list of
accepted extensions.
-R, --reject=LIST comma-separated list of
rejected extensions.
-w, --wait=SECONDS wait SECONDS between
retrievals.</div>Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-27862508650437829382015-02-16T02:22:00.003-08:002015-02-16T02:22:48.070-08:00Inserting and selecting CLOBs with DBD::OracleHere's a table with a <code>CLOB</code>:
<div class='tq84-code'>create table tq84_lob (
id number primary key,
c clob
)</div>
<p>With Perl and <code>DBD::Oracle</code>, the <code>CLOB</code> in the table can be filled like so:
<div class='tq84-code'>my $sth = $dbh -> prepare(q{
insert into tq84_lob values (
1,
empty_clob()
)
});
# setting ora_auto_lob to false:
# fetch the «LOB Locator» instead of the CLOB
# (or BLOB) content:
my $c = $dbh -> selectrow_array(
"select c from tq84_lob
where id = 1 for update",
{ora_auto_lob => 0}
);
$dbh -> ora_lob_write(
$c,
1, # offset, starts with 1!
join '-', (1 .. 10000)
);</div>
<p>A <code>CLOB</code> can be selected like so:
<div class='tq84-code'>my $c = $dbh -> selectrow_array(
"select c from tq84_lob
where id = 1",
{ora_auto_lob => 0});
my $count = 0;
while (my $buf = $dbh->ora_lob_read($c, 1+$count*1000, 1000)) {
print $buf;
$count++;
}</div>Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-74037603586821898502015-02-10T23:10:00.003-08:002016-01-13T05:55:44.055-08:00A Perl wrapper for Oracle's UTL_FILE packageI finally found time to write a simple wraper for Oracle's UTL_FILE package that allows to read a file on the database server with perl.
<p>Here's a simple perl script that demonstrates its use:
<div class='tq84-code'>use warnings;
use strict;
use OracleTool qw(connect_db);
use OracleTool::UtlFile;
my $dbh = connect_db('username/password@database') or die;
my $f=OracleTool::UtlFile->fopen($dbh,'DIR','file.txt','r');
my $line;
while ($f -> get_line($line)) {
print "$line\n";
}</div>
<p>The code is on github: <a class='tq84' href='https://github.com/ReneNyffenegger/OracleTool/blob/master/OracleTool.pm'>OracleTool.pm</a> and <a href='https://github.com/ReneNyffenegger/OracleTool/blob/master/OracleTool/UtlFile.pm'>UtlFile.pm</a>.Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-4316159094672302612015-02-10T01:23:00.001-08:002015-02-11T04:20:05.493-08:00Checking the value of NLS_LANG in SQL*Plus on WindowsOracle Support Note *179113.1* offers a, ahem, clever way to display the used value for <code>NLS_LANG</code> on Windows in SQL*Plus.
<p>First, it can be verified if the environment variable <code>NLS_LANG</code> is set:
<div class='tq84-code'>SQL> host echo %NLS_LANG%</div>
<p>SQL*Plus will answer with either something similar to
<div class='tq84-out'>AMERICAN_AMERICA.WE8MSWIN1252</div>
or with
<div class='tq84-out'>%NLS_LANG%</div>
<p>In the first case, the environment variable is set and its value, as displayed by the <code>echo</code> command is the value for <code>NLS_LANG</code>.
<p>If the variable is not set, that is in the second case, the following <i>trick</i> allows to determine its value none the less:
<div class='tq84-code'>SQL> @.[%NLS_LANG%].</div>
<p>There are again two possibilities how SQL*Plus will react. Either
<div class='tq84-out'>SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]..sql"</div>
or
<div class='tq84-out'>SP2-0310: unable to open file ".[%NLS_LANG%]."</div>
<p>In the first case, the value for NLS_LANG is set in the Windows registry (to the value between [ and ]). In the second case, NLS_LANG is not even set
in the Windows registry.
<p>Incidentally, this seems to be achieved much easier like so
<div class='tq84-code'>SQL> select sys_context('userenv', 'language') from dual;</div>Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-51453364379956971392015-02-07T09:32:00.000-08:002015-02-07T09:32:46.356-08:00Little things that make live easier #8: Using ghostscript to merge PDFsToday, a friend asked me if I could join multiple PDFs into one. Apparently, she tried it with one of the various online mergers, but without success.
<p>A quick google search brought me to the always helpful <a href='http://superuser.com'>superuser.com</a> site, particularly <a href='http://superuser.com/a/156201/16610'>this answer</a>.
<p>In short, multiple PDFs can be joined so
<div class='tq84-code'>c:\> gswin32c.exe -dBATCH -dNOPAUSE -sDEVICE=pdfwrite ^
-sOutputFile="final.pdf" ^
1.pdf 2.pdf 3.pdf ... n.pdf</div>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-67395095398358996112015-02-05T21:47:00.002-08:002015-02-05T21:49:08.889-08:00Creating an (import-) SQL file with DBMS_DATAPUMP<code>DBMS_DATAPUMP</code> can create SQL files from a schema so that these files can later be run to re-create the schema.
<p>This is described in Oracle Note <i>1519981.1: How to Generate A SQL File Using The DBMS_DATAPUMP_API?</i>. Unfortunately,
the note does not explicitely state that the creation of such an sql file consists of two steps, first the schema has to be dumped
ordinarly, then, the dumped file has to be turned into the desired SQL file.
<p>Here are the steps to create such an SQL file.
<h2 class='tq84-subtitle'>First step: creating the dump file</h2>
<div class='tq84-code'>declare
datapump_job number;
job_state varchar2(20);
begin
datapump_job := dbms_datapump.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => null,
job_name => 'Export dump file',
version => 'LATEST'
);
dbms_output.put_line('datapump_job: ' || datapump_job);
dbms_datapump.add_file(
handle => datapump_job,
filename => 'export.dmp',
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.start_job(
handle => datapump_job,
skip_current => 0,
abort_step => 0);
dbms_datapump.wait_for_job(datapump_job, job_state);
dbms_output.put_line('Job state: ' || job_state);
dbms_datapump.detach(datapump_job);
end;
/</div>
<h2 class='tq84-subtitle'>Second step: turning the dump file into an SQL file</h2>
<div class='tq84-code'>declare
datapump_job number;
job_state varchar2(20);
begin
datapump_job := dbms_datapump.open(
operation => 'SQL_FILE',
job_mode => 'SCHEMA',
remote_link => null,
job_name => 'Export SQL file',
version => 'LATEST'
);
dbms_output.put_line('datapump_job: ' || datapump_job);
dbms_datapump.add_file(
handle => datapump_job,
filename => 'export.dmp',
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(
handle => datapump_job,
filename => 'schema.sql',
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_sql_file);
dbms_datapump.start_job(
handle => datapump_job,
skip_current => 0,
abort_step => 0);
dbms_datapump.wait_for_job(datapump_job, job_state);
dbms_output.put_line('Job state: ' || job_state);
dbms_datapump.detach(datapump_job);
end;
/</div>
<div class='tq84-links'>Links</div>
<div class='tq84-github'>
Source code on <a href="https://github.com/ReneNyffenegger/oracle-patterns/tree/master/Installed/dbms/datapump/extract_schema_sql">github</a>
</div>
<p>My question on <a href='http://dba.stackexchange.com/questions/91149/how-to-generate-an-sql-file-with-dbms-datapump'>dba.stackexchange.com</a>
<p><a href='http://renenyffenegger.blogspot.ch/2015/02/cloning-oracle-schema-with-dbmsdatapump.html'>Cloning an Oracle schema with DBMS_DATAPUMP</a>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-64838157910400482432015-02-04T00:55:00.000-08:002015-02-05T21:50:11.278-08:00Cloning an Oracle schema with dbms_datapumpI have a schema (<code>FROM_SCHEMA_A</code>) that I need to clone <i>on the same database</i>. So, the cloned schema will go by another name, in my case by <code>TO_SCHEMA_A</code>).
<p>In order to make things a bit more complicated, <code>FROM_SCHEMA_A</code> references objects in another schema (named
<code>SCHEMA_B</code>). This other schema won't be cloned.
<h2 class='tq84-subtitle'>Schema definitions</h2>
Before starting with the object definitions in the schemas, I create the needed schemas:
<div class='tq84-code'>create user from_schema_A
identified by p
quota unlimited on users;
grant create procedure,
create session,
create table,
create trigger,
create view
to from_schema_A;
create user schema_B
identified by p
quota unlimited on users;
grant create session,
create table
to schema_B;
</div>
<p>Here's the definition for the <code>SCHEMA_B</code> schema. It consisists of one table only:
<div class='tq84-code'>create table table_b_1 (
a number,
b varchar2(10)
);</div>
<p>Since <code>FROM_SCHEMA_A</code> references this table, it needs some grants:
<div class='tq84-code'>grant insert, select on table_b_1 to from_schema_A;</div>
<p>Here's the definition for the <code>FROM_SCHEMA_A</code> schema:
<div class='tq84-code'>create table table_a_1 (
c number,
d varchar2(20)
);
insert into table_a_1 values (10, 'ten' );
insert into table_a_1 values (11, 'eleven');
create view view_a_1 as
select * from schema_b.table_b_1;
create package package_a_1 as
function count_a return number;
function count_b return number;
end package_a_1;
/
create package body package_a_1 as
function count_a return number is
ret number;
begin
select count(*) into ret
from table_a_1;
return ret;
end count_a;
function count_b return number is
ret number;
begin
select count(*) into ret
from view_a_1;
return ret;
end count_b;
end package_a_1;
/
create trigger trigger_a
before insert on table_a_1
for each row
begin
insert into schema_b.table_b_1 values (:new.c, :new.d);
end trigger_a;
/
-- There's a trigger on the table, so the
-- following insersts should fill table_b_1
-- (in schema_b):
insert into table_a_1 values (1, 'one');
insert into table_a_1 values (2, 'two');
</div>
<h2 class='tq84-subtitle'>Export/Import administrator</h2>
In order to perform the export and the import, I create a special <i>export import administrator</i>:
<div class='tq84-code'>create user exp_imp_admin
identified by p;
grant exp_full_database,
imp_full_database
to exp_imp_admin;
alter user exp_imp_admin quota unlimited on users;</div>
<h2 class='tq84-subtitle'>Performing the export and import</h2>
With that user, I am able to export the schema:
<div class='tq84-code'>connect exp_imp_admin/p
declare
datapump_job number;
job_state varchar2(20);
begin
datapump_job := dbms_datapump.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => null,
job_name => 'Clone schema A, export',
version => 'LATEST',
compression => dbms_datapump.ku$_compress_metadata
);
dbms_output.put_line('datapump_job: ' || datapump_job);
dbms_datapump.metadata_filter(
handle => datapump_job,
name =>'SCHEMA_LIST',
value =>'''FROM_SCHEMA_A'''
);
dbms_datapump.add_file(
handle => datapump_job,
filename => 'clone_schema_a_export.log',
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file);
dbms_datapump.add_file(
handle => datapump_job,
filename => 'clone_schema_a.dmp', -- Note, created will be in UPPERCASE!
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.start_job (datapump_job);
dbms_datapump.wait_for_job(datapump_job, job_state);
dbms_output.put_line('Job state: ' || job_state);
dbms_datapump.detach(datapump_job);
exception when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
if datapump_job is not null then
dbms_datapump.detach(datapump_job);
end if;
end;
/</div>
<p>And the following import actually clones the schema. Of particular insterest is the call of <code>dbms_datapump.metadata_remap</code>
<div class='tq84-code'>declare
datapump_job number;
job_state varchar2(20);
begin
datapump_job := dbms_datapump.open(
operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => null,
job_name => 'Clone schema A, import',
version => 'LATEST',
compression => dbms_datapump.ku$_compress_metadata
);
dbms_output.put_line('datapump_job: ' || datapump_job);
dbms_datapump.metadata_remap(
datapump_job,
'REMAP_SCHEMA',
'FROM_SCHEMA_A',
'TO_SCHEMA_A');
dbms_datapump.add_file(
handle => datapump_job,
filename => 'clone_schema_a_import.log',
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file);
dbms_datapump.add_file(
handle => datapump_job,
filename => 'clone_schema_a.dmp', -- Note: export has created the file with UPPERCASE letters
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.start_job (datapump_job);
dbms_datapump.wait_for_job(datapump_job, job_state);
dbms_output.put_line('Job state: ' || job_state);
dbms_datapump.detach(datapump_job);
exception when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
if datapump_job is not null then
dbms_datapump.detach(datapump_job);
end if;
end;
/</div>
<div class='tq84-links'>Links</div>
<a href='http://renenyffenegger.blogspot.ch/2015/02/creating-import-sql-file-with.html'>Creating an (import-) SQL file with DBMS_DATAPUMP</a>
<div class='tq84-github'>
Source code on <a href="https://github.com/ReneNyffenegger/oracle-patterns/tree/master/Installed/dbms/datapump/clone_schema">github</a>
</div>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-21151596451858536122015-01-12T02:18:00.001-08:002015-01-12T02:18:33.640-08:00PL/SQL: FOR r IN (SELECT ... INTO ) ... Hmm?Today, I came across a funny piece in some sort of legacy code that I think should not compile:
<div class='tq84-code'> for r in (
select i, t
into rec.i, rec.t -- Hmm???
from tq84_foo
)
loop
....
end loop;
</div>
<p>My understanding is that the <code>INTO</code> clause should not be valid because the select statement is embeded in a <code>FOR r IN (SELECT...)</code> loop. Yet, it compiles, at least in Oracle 11i.
<p>Here's a little test script for demonstration purposes:
<div class='tq84-code'>create table tq84_foo (
i number,
t varchar2(10)
);
insert into tq84_foo values (1, 'abc');
insert into tq84_foo values (2, 'def');
declare
rec tq84_foo%rowtype;
begin
for r in (
select i, t
into rec.i, rec.t -- Hmm???
from tq84_foo
)
loop
dbms_output.put_line('rec: i= ' || rec.i || ', t=' || rec.t);
end loop;
end;
/
drop table tq84_foo purge;</div>
<p>The output is
<div class='tq84-out'>rec: i= , t=
rec: i= , t=</div>
<div class='tq84-links'>Links</div>
<a href="http://stackoverflow.com/questions/27899660/for-r-in-select-into">My according question on stackoverflow</a>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-72573438435577515582015-01-06T09:02:00.001-08:002015-01-06T09:02:24.252-08:00Little things that make live easier #7: set relativenumber in vimToday, I stumbled upon an option in vim that I think will make my life easier: <code>:set relativefilenumber</code>
<p>With this option enabled, vim will show how many lines a line is apart from the line with the cursor:
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipThWI8mfqJeRRhOyMg7ef5HLgvZBz5JpgssDOKc5SDpi53HoJAeG0EgawsABYtR2AhxnhN4M_uTRf3vImEi8_1aTI_Rk51O4AevLF8iKANpihusK66HMID5KhAFpUwv7Kvul0ChjTxEs/s1600/relativefilenumber.png" />
<p>So, if I need to quickly jump to the select statement, I can type <code>9j</code> (<code>9</code> because I see that it is 9 lines down, and <code>j</code> because that moves the cursor down).Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-52732946560153903712014-12-19T23:51:00.001-08:002014-12-19T23:51:55.428-08:00Creating an a4 with rulers to test the output accuracy of a printerI recently bought a new printer and wanted to test its borderless printing capabilities. So I wrote a small VBA script that creates an A4 MS Word document with four rulers, each starting at one of the four borders:<br>
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU-TA2c1NJTr8zPlH6ZY17MB62hTlMuIYauN1By-sOvsZeyekMrBTa7mVDo_TDakaAAHKTu2bn5IrmCZkV9uZldC8Ql8KTDKLcgLnXOd6dUOwX3y4t777HE8DUkr_JEwvfosk6UeS3FLM/s1600/ruler.PNG" />.
<p>In case you're interested in the source code, I put in on <a href='https://github.com/ReneNyffenegger/printing/blob/master/a4-ruler.bas'>github</a>.
The PDF can be downloaded from <a href="http://renenyffenegger.ch/blog/files/a4-ruler.pdf">here (http://renenyffenegger.ch/blog/files/a4-ruler.pdf)</a>.Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com1tag:blogger.com,1999:blog-6541595232071768624.post-58334100515213806592014-12-16T23:58:00.002-08:002014-12-17T00:04:05.265-08:00The missing \b regular expression special character in Oracle.Oracle's regular expressions don't support the special regular expression character <code>\b</code>, at least not in Oracle 11i.
<p>
Consider the following table:
<div class='tq84-code'>create table tq84_word_boundaries (
txt varchar2(50)
);
insert into tq84_word_boundaries values ('AFooABarAndABaz' );
insert into tq84_word_boundaries values ('A FooA BarAndABaz' );
insert into tq84_word_boundaries values ('A Foo, a Bar and a Baz');
insert into tq84_word_boundaries values ('A Foo without a Baz' );
insert into tq84_word_boundaries values ('Foo Bar, Baz' );
insert into tq84_word_boundaries values ('Is it a Foo?' );
insert into tq84_word_boundaries values ('Bar-Foo-Baz' );</div>
<p>Now, I want to find all records that contain the exact word <b><code>Foo</code></b>.
That is, I want, for example <code>A Foo without a Baz</code> (the fourth record), but I don't want
<code>A FooA BarAndABaz</code> (the second record) because <code>FooA</code> is not the exact word <code>Foo</code>
<p>If Oracle supported <code>\b</code> I could use
<div class='tq84-code'>select * from tq84_word_boundaries
where
regexp_like(txt, '\bFoo\b');</div>
Yet, Oracle, does not support it and no record is returned.
<p>To improve matters, I could try
<div class='tq84-code'>select * from tq84_word_boundaries
where
regexp_like(txt, '\sFoo\s');
</div>
This approach returns
<div class='tq84-out'>TXT
--------------------------------------------------
A Foo without a Baz</div>
<p>A bit better, but far from perfect. For example, the fifth record (<code>Foo Bar, Baz</code>) is not returned, because
the <code>\s</code> doesn't match start of line. So, I improve the where condition:
<div class='tq84-code'>select * from tq84_word_boundaries
where
regexp_like(txt, '(^|\s)Foo($|\s)');</div>
The record is now returned:
<div class='tq84-out'>TXT
--------------------------------------------------
A Foo without a Baz
Foo Bar, Baz</div>
<p>Yet again, this is far from perfect. I need also record records where <code>Foo</code> is followed or lead by a non word character (such as <code>?</code> or <code>-</code>):
<div class='tq84-code'>select * from tq84_word_boundaries
where
regexp_like(txt, '(^|\s|\W)Foo($|\s|\W)');</div>
This returns
<div class='tq84-out'>TXT
--------------------------------------------------
A Foo, a Bar and a Baz
A Foo without a Baz
Foo Bar, Baz
Is it a Foo?
Bar-Foo-Baz</div>
I think I can live with it.
<div class='tq84-links'>Links</div>
<div class='tq84-github'>
Source code on <a href="https://github.com/ReneNyffenegger/oracle-patterns/blob/master/SQL/functions/regular_expressions/word_boundaries.sql">github</a>
</div>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com2tag:blogger.com,1999:blog-6541595232071768624.post-30797676624717977702014-12-15T06:09:00.000-08:002014-12-15T06:09:07.076-08:00Little things that make live easier #6: Using clip.exe in cmd.exe to copy somehting into the clipboardWindows comes with a handy little program, named <code>clip.exe</code>, that can be used to quickly copy something into the clipboard from <code>cmd.exe</code>.
<p>For example, if I wanted to copy the current working directory into the clipboard, I'd go</p>
<div class='tq84-console'>C:\some\long\and\complicated\path><span class='tq84-highlight'>echo %CD% | clip</span></div>
<p>Similarly, if I needed to copy all files within the current directory, I'd do
<div class='tq84-console'>C:\some\path><span class='tq84-highlight'>dir /b | clip</span></div>
<p>Also, I can copy the content of a file into the clipboard like so:
<div class='tq84-console'>C:\some\path><span class='tq84-highlight'>clip < someFile.txt</span></div>Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-67026440461529000102014-12-12T12:59:00.003-08:002014-12-12T12:59:27.728-08:00Paginating SQL queries with Oracle 12cOracle 12c not only allows to create <a href="http://renenyffenegger.blogspot.ch/2014/12/top-n-select-queries-with-oracle-12c.html">TOP N queries with an understandable syntax</a>, the <i>row limiting clause</i> also makes it possible to paginate a result set:
<p>
<div class='tq84-code'>create table tq84_table (
id number,
txt varchar2(10)
);
insert into tq84_table values ( 2, 'two' );
insert into tq84_table values ( 1, 'one' );
insert into tq84_table values ( 6, 'six' );
insert into tq84_table values ( 8, 'eight');
insert into tq84_table values ( 3, 'three');
insert into tq84_table values ( 7, 'seven');
insert into tq84_table values ( 4, 'four' );
insert into tq84_table values ( 9, 'nine' );
insert into tq84_table values (10, 'ten' );
insert into tq84_table values ( 5, 'five' );</div>
<p>
<div class='tq84-code'>select
id,
txt
from
tq84_table
order by
id
offset 4 rows -- skip first 4 records
fetch next 3 rows only -- fetch next 3 records
;</div>
returns
<div class='tq84-out'> ID TXT
---------- ----------
5 five
6 six
7 seven</div>
<div class='tq84-links'>Links</div>
<div class='tq84-github'>
SQL statement on <a href="https://github.com/ReneNyffenegger/oracle-patterns/blob/master/SQL/select/12c/row_limiting_clause/offset_n_rows_fetch_next_m_rows.sql">github</a>
</div>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-49943577365135321182014-12-12T12:38:00.001-08:002014-12-12T13:02:01.541-08:00TOP N select queries with Oracle 12cWith Oracle 12c, it's possible, <i>finally</i>, to do <b>TOP n</b> select queries with an easily understandable syntax.
<p>Here's a table to demonstrate it:
<div class='tq84-code'>create table tq84_table (
id number,
txt varchar2(10)
);
insert into tq84_table values ( 2, 'two' );
insert into tq84_table values ( 1, 'one' );
insert into tq84_table values ( 6, 'six' );
insert into tq84_table values ( 8, 'eight');
insert into tq84_table values ( 3, 'three');
insert into tq84_table values ( 7, 'seven');
insert into tq84_table values ( 4, 'four' );
insert into tq84_table values ( 0, 'zero' );
insert into tq84_table values ( 9, 'nine' );
insert into tq84_table values (10, 'ten' );
insert into tq84_table values ( 5, 'five' );
commit;</div>
<p>Now, a select statement with <code>fetch first row only</code>:
<div class='tq84-code'>select
id,
txt
from
tq84_table
order by
id
fetch first row only;</div>
selects
<div class='tq84-out'> ID TXT
---------- ----------
0 zero</div>
<div class='tq84-links'>Links</div>
<a href="http://renenyffenegger.blogspot.ch/2014/12/paginating-sql-queries-with-oracle-12c.html">Paginating SQL queries with Oracle 12c</a>
<p>
<div class='tq84-github'>
SQL on <a href="https://github.com/ReneNyffenegger/oracle-patterns/blob/master/SQL/select/12c/row_limiting_clause/fetch_first_n_rows_only.sql">github</a>
</div>
Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0tag:blogger.com,1999:blog-6541595232071768624.post-51161436503290115672014-12-11T23:25:00.000-08:002014-12-11T23:25:07.614-08:00Little things that make live easier #5: Navigation the Windows Registry with PowershellIt's possible to navigate the <i>Windows Registry</i> with <i>Powershell</i>
<p>
<div class='tq84-console'>PS C:\> <span class='tq84-highlight'>cd HKLM:/SOFTWARE/Microsoft/PowerShell</span>
PS HKLM:\SOFTWARE\Microsoft\PowerShell> <span class='tq84-highlight'>ls</span>
Hive: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell
SKC VC Name Property
--- -- ---- --------
4 2 1 {Install, PID}
PS HKLM:\SOFTWARE\Microsoft\PowerShell> <span class='tq84-highlight'>cd 1</span>
PS HKLM:\SOFTWARE\Microsoft\PowerShell\1> <span class='tq84-highlight'>ls</span>
Hive: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1
SKC VC Name Property
--- -- ---- --------
0 1 0407 {Install}
0 6 PowerShellEngine {ApplicationBase, PSCompatibleVersion, RuntimeVersion, ConsoleHostAssemblyName...}
1 0 PSConfigurationProviders {}
2 0 ShellIds {}</div>Anonymoushttp://www.blogger.com/profile/04244192904013186067noreply@blogger.com0