Monthly Archives: April 2011

Temporary tables in Oracle

Oracle has a feature it is possible to create tamporary tables. The idea is that a table can be created, can be populated with data, but these data can only be seen during the session. Other sessions do see the table but they see no rows. Even if someone uses the same userid as the user that created the temporary table, no rows are returned.
First the syntax of this temporary table:


create global temporary table onzin
(i number(5,0),
naam varchar2(30))
on commit preserve rows;

The working can be checked by adding a few rows. It can be verified that the rows exist: even after a commit, the rows are returned.
However if another session is opened, the rows are NOT returned.
What is the use of this feature?
Burleson (http://www.dba-oracle.com/t_temporary_tables_sql.htm) claims that the performance can be improved by such temporary tables. I understand that some in-between results are kept in thic temporary table. They are later re-used by a second query.
This is disputed by Tom Kyle. He writes: “I’m of the mindset that they are almost never needed”. He then proceeds by asking the readers to give him a situation where such temporary tables might be needed. He got some replies but they did not look very convincing.
Whatever: Oracle does know the concept of a temporary table, but their usage seems somewhat limited.

Creating a cookie

Cookies are small items that can be written on the hard disk of a client computer. In Google Chrome, these cookies can be found in a binary file. I found this file in “C:\Users\tmaanen.CORP\AppData\Local\Google\Chrome\User Data\Default\Cookies”.
These files can be written on request of the webserver. I wrote a small PHP programme that writes these cookies on the hard disk of the client. The programme reads as:

?php
setcookie("cookie[three]", "cookiethree", time() + 1000);
setcookie("cookie[two]", "cookietwo", time() + 1000);
setcookie("cookie[one]", "cookieone", time() + 1000);
echo "Cookie is set - disappears in 1000secs\n";    
?

When this programme was executes on the webserver, a cookie is sent to client computer. There, it can be seen in the options screen that displays cookies:
Chrome display of cookies

Once these cookies are stored on the hard disk, we could read them with a programme that is stored on the webserver. An example is given here:

if (isset($_COOKIE['cookie'])) {
    foreach ($_COOKIE['cookie'] as $name => $value) {
        $name = htmlspecialchars($name);
        $value = htmlspecialchars($value);
        echo "$name : $value 
\n"; } };

This PHP programme is executed on request from the client computer. This gives a screen like:

Return Cookies

How to run a SAS job in a batch mode

I hope you recognise the situation. You have developed some SAS code (in Enterprise Guide, SAS Base Editor or DI Studio) and you would like to create a batch job from this. Huh? SAS seems to offer you only the possibility to run a job interactively.
However, it is possible to run a job in a batch mode. The first step to do is to create a file that contains the SAS code. Let us assume that this file is called “D:\tmaanen\Documents\prive\datavault\Link_Invoice_Order.sas”. From the SAS helpfile we know that the command line to run this is: “”D:\Program Files\SAS\SASFoundation\9.2\sas.exe” -SYSIN D:\tmaanen\Documents\prive\datavault\Link_Invoice_Order.sas -NOSPLASH -ICON -PRINT D:\tmaanen\Documents\prive\datavault\Link_Invoice_Order.lst -log D:\tmaanen\Documents\prive\datavault\Link_Invoice_Order.log”. We could create a batch file that contains this line and submit this form the commandline.

OLE DB

Today, I tried to link a ODBC compliant database to a SAS programme. SAS is a programme, much like Perl, that allows data to be read, transformed and written back to a database. SAS claims that it is able to use OLE DB to hook up to ODBC compliant databases. The idea is that the SAS programme is seen as a data consumer, while OLE DB is a dataprovider thru its software that is automatically installed on a windows box.
When SAS is linked to a database, two questions are asked: what is datasource? and: what is the provider? The helpfile with SAS does not offer you great help: it only indicates that a name of the provider should be given. But what is the name of the provider? And what is the name of the datasource?
It took me a little while to find the answer:
1: The name of the datasource is exactly the name of the datasource as used in the ODBC. This can be understood as OLE DB is used here to exchange data via ODBC with the database.
2: The name of the provider is “MSDASQL.1”. I found this name by looking at the properties in Excel when data were read via OLE DB from an ODBC datasource. There, the properties were given as: “Provider=MSDASQL.1;Persist Security Info=True;User ID=tom;Data Source=MYSQL_THUIS;Extended Properties=”DSN=MYSQL_THUIS;UID=tom;”;Initial Catalog=tom”. That gave me the clue: Provider=MSDASQL.1. Exactly this name was the name, I looked for.

The idea that connection properties for OLE DB can be easily found in Excel, gave me the idea how to hook up databases via OLE DB to SAS.
Example: an Access database has these properties when it is linked to Excel: “Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\tmaanen\Documents\Boekhouding_2002041.mdb;…”. I use these values in SAS to hook up an Access database via OLE DB. See picture for an example on how these properties were used:
SAS oledb