Do Oracle temp tables behave correctly under DBI?
Andon Tschauschev recently posted on perl.dbi.users concerning an apparent problem with temp tables "disappearing" between statements in the same session. He was using SQL Server via ODBC support.
The discussion and investigation continues, but it made me think to test if there's any similar strange behaviour with Oracle via DBI.
The temporary table model is somewhat different in Oracle, and centers around the "CREATE GLOBAL TEMPORARY TABLE.." statement. Temp table definitions are always global, but data is always private to the session, and whether data persists over a commit depends on whether the qualification "on commit preserve rows" or "on commit delete rows" is specified.
testOraTempTables.pl is a simple test script to check out the behaviour. Good news is that all seems to be a-ok. The temporary table definition is persistent across sessions, but data is not, and importantly (..the point of Andon's investigation..) data is preserved across DBI calls within the same session as expected.
Sample output from the test program:
C:\MyDocs\Testers2\perl\dbi>perl testOraTempTables.pl orcl scott tiger
[1st connection] connect to orcl {AutoCommit => 1}:
[1st connection] create global temp table:
create global temporary table t1 (x varchar2(10)) on commit preserve rows
[1st connection] insert 3 rows of data into it: insert into t1 values (?)
[1st connection] should be 3 rows because we have "on commit preserve rows" set:
select count(*) from t1 = 3
[2nd connection] connect to orcl:
[2nd connection] should be 0 rows because while the table definition is shared, the data is not:
select count(*) from t1 = 0
[2nd connection] disconnect:
[1st connection] disconnect:
[1st connection] reconnect {AutoCommit => 0}:
[1st connection] should be 0 rows because this is a new session:
select count(*) from t1 = 0
[1st connection] drop the temp table: drop table t1
[1st connection] create global temp table:
create global temporary table t1 (x varchar2(10)) on commit delete rows
[1st connection] insert 3 rows of data into it: insert into t1 values (?)
[1st connection] should be 3 rows because we have autocommit off and not committed yet:
select count(*) from t1 = 3
[1st connection] should be 0 rows because now we have committed:
select count(*) from t1 = 0
[1st connection] disconnect:
[1st connection] reconnect {AutoCommit => 1}:
[1st connection] insert 3 rows of data into it: insert into t1 values (?)
[1st connection] should be 0 rows because we have autocommit on and "on commit delete rows" defined:
select count(*) from t1 = 0
[1st connection] disconnect:
[1st connection] reconnect {AutoCommit => 0}:
[1st connection] drop the temp table: drop table t1
[1st connection] disconnect:
read more and comment..
Burning DVDs with unicode filename support
I have quite an eclectic music collection, which grew considerably last year when I spent quite a bit of time in Tokyo. My favourite Sunday afternoon haunt was HMV @ Times Square, checking out the Shunjuku South indie chart. A suitcase of CDs later, I finally got around to ripping my entire CD collection and adding it my old ripped vinyl collection. All nicely organised, named and categorised in iTunes.
Feeling very pleased with myself, I wanted to archive all my hard work onto a set of DVDs, only to find that my (Windows-based) recording software baulked at the Japanese, Korean and Chinese characters in the folder and filenames.
My first thought .. should just need to change the file system format? But it didn't take long to discover it wasn't so simple. In fact, of the half a dozen different disk burning softwares I ended up trying out (including most of the "major" names), all but one failed to handle my babel of disks correctly.
So I'd like to spread the word. VSO CopyToDVD was the ONLY product that worked. It costs a few bucks to buy, but I just downloaded the limited-time trial and it passed the unicode test with flying colours. I didn't really poke around all its features, but it seems to pack the lot. And the fact that I could download it and bang out a few DVDs in short order pretty much sums things up. 
I'll be buying it, and if you also need to burn disks with unicode file/folder names then I can recommend you check it out too.
read more and comment..
Getting environment variables on the Oracle database server
Say you have a connection to a remote Oracle Database server and want to get the ORACLE_HOME setting. Or any other environment variable for that matter. As far as I can see, Oracle doesn't provide any direct, supported way to do this.
In 10g however, there's an interesting procedure DBMS_SYSTEM.GET_ENV available which does the job:
set autoprint on
var ORACLE_HOME varchar2(255)
exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME)
PL/SQL procedure successfully completed.
ORACLE_HOME
-----------------------------------------
D:\oracle\product\10.2.0\db_1
DBMS_SYSTEM is an undocumented/unsupported package. It mainly seems to be an internal utility function for debugging and event monitoring. The package itself is obfusticated, but we can discover a little about it from the data dictionary. The USER_PROCEDURES view lists the individual procedures available in the package:
select PROCEDURE_NAME from USER_PROCEDURES where OBJECT_NAME = 'DBMS_SYSTEM';
PROCEDURE_NAME
------------------------------
DIST_TXN_SYNC
GET_ENV
KCFRMS
KSDDDT
KSDFLS
KSDIND
KSDWRT
READ_EV
SET_BOOL_PARAM_IN_SESSION
SET_EV
SET_INT_PARAM_IN_SESSION
SET_SQL_TRACE_IN_SESSION
WAIT_FOR_EVENT
And USER_ARGUMENTS can tell us about the parameters. For example:
select OBJECT_NAME,ARGUMENT_NAME,POSITION,DATA_TYPE,IN_OUT
from USER_ARGUMENTS
where PACKAGE_NAME='DBMS_SYSTEM' and OBJECT_NAME='GET_ENV'
order by POSITION;
OBJECT_NAME ARGUMENT_NAME POSITION DATA_TYPE IN_OUT
------------- -------------- ---------- --------- ------
GET_ENV VAR 1 VARCHAR2 IN
GET_ENV VAL 2 VARCHAR2 OUT
Given an environment variable name (VAR), GET_ENV returns its value (VAL). These values are coming from the system environment that belongs to the Oracle server process. If you have a dedicated server config, the environment is inherited from the tnslsnr process that spawned the server process. If shared server, then the environment is inherited from whatever process (PMON? PSP0?) that started the shared server process.
So an interesting poke around in some Oracle internals, but there are lots of reasons why you shouldn't use this trick in any production situation!
- It is undocumented and unsuppported. The "get_env" method seems to have appeared in 10g, but there's also no guarantee it will be present in any future versions.
- There are better solutions. SQL client code shouldn't directly depend on server environment variables.
- Remember it is instance specific, and may be misleading in a RAC environment.
read more and comment..
Synchronising two directory trees - update
I've just released an update to tree-sync-2.2.pl on CPAN. This fixes a few bugs with special character handling in filenames.
I've currently three main uses for the script:
- On my Windows XP laptop, I use it to backup my working files to an external drive. For this I use the "fwdonly" mode so that the external drive copy is a perfect copy of what I have on the laptop.
- Also on my laptop, I have a collection of files that I sync with the external drive in "full" sync mode (default). This means changes will be exchanged bidirectionally. This is useful because sometimes I will drop files into this area with the external drive connected to another machine.
- Thirdly, I use it on my server to sync a collection of files from cvs into a web-visible area.
PS: As of Oct-2008, the tree-sync project is now on github. Use this if you want to contribute to development. Of course, releases will still be distributed for use on CPAN.
read more and comment..