Veröffentlicht am

Material für meinen Oracle Grid Control Vortrag

Wie bereits angekündigt, halte ich dieses Jahr einen Vortrag zu Oracle Grid Control auf der 20. Deutsche ORACLE-Anwenderkonferenz in Nürnberg. Meine Präsentation steht ab jetzt unter http://www.fm-berger.de/download/oragc-02.pdf zum Download bereit.

Hier noch einige Links zu diesem Thema:

Veröffentlicht am

Reading Excel Files with Perl

Sometimes you need to read data from Excel files with Perl. There are several Perl Modules (Spreadsheet::ParseExcel, Spreadsheet::ParseExcel::Simple) that make that job quite easy. Spreadsheet::ParseExcel::Simple is an lightweight abstraction of Spreadsheet::ParseExcel.

Sample Code:

#!/usr/bin/perl -w

use Spreadsheet::ParseExcel;

my $parser = new Spreadsheet::ParseExcel();
my $xls = $parser->Parse("PerlExcelTest.xls")
  or die "Can't read File!";

# Loop over all tables in Excel file
for(my $i = 0; $i < $xls->{SheetCount}; $i++) {
  $sheet = $xls->{Worksheet}[$i];

  print "\nSheet: " . $sheet->{Name} . "\n";

  # has the current sheet cells?
  next unless (exists ($sheet->{MaxRow}) and (exists ($sheet->{MaxCol})));

  # loop over the row of the current sheet
  foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
    
    # loop over the cells in the current row
    foreach my $column ($sheet->{MinCol} .. $sheet->{MaxCol}) {
      next unless (defined $sheet->{Cells}[$row][$column]);

      print $sheet->{Cells}[$row][$column]->Value . ' | ';
    }
    print "\n";
  }
}

During my tests I came across several issues:

  • If you create Excel files with OpenOffice numeric values show up as string GENERAL. There is a patch for that problem. Please note also that you could use $cell->Val instead of $cell->Value.
  • I also have problems to extract date values from the excel file. In that case I am not able to determine the type of a date cell. Normally I should see Date but I turns out to be Numeric.
Veröffentlicht am

Access MySQL Databases with Oracle HSODBC

Sometimes you need to access data stored in an different RDBMS like MySQL. Oracle provides an convenient way to access external data-sources via ODBC. To use Oracle HSODBC you can follow my step-by-step notes:

  • Install unixODBC and the MySQL ODBC Driver
    For Fedora Core 6 I installed the following RPMs:

    • unixODBC-2.2.11-7.1
    • mysql-connector-odbc-3.51.12-2.2
  • Configure /etc/odbc.ini 
    [ODBC Data Sources]
TimeSheetTest = MySQL ODBC PHP.TimeSheetTest DSN

[TimeSheetTest]
Driver       = /usr/lib/libmyodbc3.so
Description  = MySQL ODBC 3.51 Driver DSN
SERVER       = localhost
PORT         = 3306
USER         = root
Password     = SECRET
Database     = timesheet
OPTION       = 3
SOCKET       =
  • Test the ODBC Connection via isql 
    [frank@w0004]$ isql TimeSheetTest
Connected!
sql-statement
help [tablename]
quit
SQL>
  • Oracle Configuration
    First ensure that the shell environment used to start the Oracle listener includes the following
    environment variables:

    export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc

    To integrate that ODBC connection into Oracle you have to edit several configuration files:

    $ORACLE_HOME/hs/admin/initTimeSheetTest.ora

    HS_FDS_CONNECT_INFO = TimeSheetTest
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib/libmyodbc3.so

    $ORACLE_HOME/network/admin/listener.ora

    ...
SID_LIST_LISTENER =
...
    (SID_DESC =
      (PROGRAM = hsodbc)
      (ORACLE_HOME = /opt/oracle/ora10g)
      (SID_NAME = TimeSheetTest)
      (ENVS=LD_LIBRARY_PATH = /usr/lib:/opt/oracle/ora10g/lib)
    )
  )
...

    $ORACLE_HOME/network/admin/tnsnames.ora

    ...
TIMESHEET = (DESCRIPTION = 
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))
    (CONNECT_DATA = (SID= TimeSheetTest))(HS=OK)
  )
...
  • Test the Configuration with tnsping 
    $ tnsping timesheet

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 05-SEP-2007 18:26:16

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SID= TimeSheetTest))(HS=OK))
OK (10 msec)
  • Create an Oracle Link in your Oracle Database instance 
    SQL> CREATE PUBLIC DATABASE LINK timesheet
  CONNECT TO "root" IDENTIFIED BY "SECRET" USING 'TIMESHEET';

Please note that MySQL is case-sensitive on tablenames, so you have to use:

SQL> select * from "timesheet_times"@timesheet;
...
Veröffentlicht am

Vortrag - Oracle Grid Control 10g im Praxiseinsatz

Die Deutsche ORACLE-Anwendergruppe (DOAG) veranstaltet in diesem Jahr die 20. Deutsche ORACLE-Anwenderkonferenz in Nürnberg. Das Programm der SIG Database steht dieses Jahr unter dem Motto "Manageability". In diesem Programmbereich halte ich einen Vortrag mit dem Thema "Oracle Grid Control 10g im Praxiseinsatz".

Neben dem üblichen Vortrags-Programm gibt es in diesem Jahr ein Oracle 11g Labor und ein Oracle SOA Camp in denen mit eigenen praktischen Übungen erste Erfahrungen mit den neuen Oracle Technologien gesammelt werden können.

Veröffentlicht am

Oracle Database 9iR2 goes and 11gR1 comes

On the 11th of July Oracle launched Version 11 of their RDBMS software. I just received an email from Oracle stating an count-down of 11 until the software will be available for public download. Well 11 could mean 11 hours or 11 days :-)

The release of a new software version is primarily an marketing thing. From an technical point of view it is more interesting to look at version 9iR2 which reached its final phase in the product life cycle. Odd thing, 9iR2 represents still the majority release for production installations. I would guess that around 70 to 80% of the databases of my customers run with 9iR2.

As a software developer I can understand that Oracle keeps the time frame for an product life cycle quite short. The market forces you to make major releases which include some new and cool features. On the other hand they have to support that piece of software on a lot of platforms. For instance 9iR2 was available on OpenVMS or OS/390. If you have to support more than two versions of your software, software testing and support become a real nightmare.

So you still have 9iR2 in production? My advice would be to upgrade to the Terminal Patch Set 9.2.0.8 within the next six month. You should also evaluate the Critical Patch Updates (CPUs) in a timely fashion. Good would be if you could manage to be aware of the problems that are fixed with a CPU in a time frame of 4 weeks after the CPU got released. Depending on how critical the fixed problems are to your environment, you should be able to roll-out the CPUs within 3 months.