tundal45@wordpress:~$

>puts "Hello, World!"

Archive for October 2008

Perl::DBI and Oracle External Table Error

leave a comment »

First and foremost, I apologize for the title. I could not come up with anything better.

The issue, which I have been working for a while, is that when I run a query that creates an external table in Oracle, it works swimmingly. However, when I ran the same query using a Perl script, I was receiving the following error:

KUP-01005: syntax error found "identifier": expecting one of: "number, plussign"

I printed the query to the screen as well as to a file and it would look exactly the same as the query that worked when manually executed using PL/SQL. However, I realized, by accident of course, that if I change the “:” to a “-” in the ACCESS PARAMETERS where I provide range for columns (I am working with a fixed width file), it magically works.

Here is an Example:

QUERY THAT DOES NOT WORK IN PERL:

CREATE TABLE ex.emp
(
  emp_no varchar(255),
  emp_title varchar(255),
  emp_name varchar(255),
  emp_address varchar(255)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY empdata
  ACCESS PARAMETERS
  (
      RECORDS DELIMITED BY 0X\'0A\'
      READSIZE 1048576
      FIELDS LRTRIM
      MISSING FIELD VALUES ARE NULL
      REJECT ROWS WITH ALL NULL FIELDS
      (
        emp_no (1:5)
        emp_title varchar (6:20)
        emp_name varchar (21:51)
        emp_address varchar(52:92)
      )
  ) location('emp_info.txt')
)REJECT LIMIT UNLIMITED;

QUERY THAT WORKS IN PERL:

CREATE TABLE ex.emp
(
  emp_no varchar(255),
  emp_title varchar(255),
  emp_name varchar(255),
  emp_address varchar(255)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY empdata
  ACCESS PARAMETERS
  (
      RECORDS DELIMITED BY 0X\'0A\'
      READSIZE 1048576
      FIELDS LRTRIM
      MISSING FIELD VALUES ARE NULL
      REJECT ROWS WITH ALL NULL FIELDS
      (
        emp_no (1-5)
        emp_title varchar (6-20)
        emp_name varchar (21-51)
        emp_address varchar(52-92)
      )
  ) location('emp_info.txt')
)REJECT LIMIT UNLIMITED;

I basically spent two days a while back on a similar issue for another script a while back and half a day today before I found this solution. However, its one of those very dissatisfying resolution because you feel what you were doing SHOULD WORK. If anyone has a satisfying answer to why this makes sense, I would love to hear it. As for now, the only satisfaction I can get out of this situation is that it will help me keep moving forward with this script as well as the script I stopped working on couple of months back because of this error.

Advertisements

Written by tundal45

October 29, 2008 at 7:52 pm

What’s up with sudden obsession with Pirates?

leave a comment »

That was the question that was posed by a co-worker today that got me thinking. Off the top of my head, it seemed to me that the reason there is an obsession with Pirates, at least in the geek community, is because Pirates are so different in their ways from regular folks, a scenario that maybe the geek community relates to. Then another co-worker suggested that maybe its because Pirates, like any other creation of fantasy genre, are automatically deemed fascinating.

Since I was running time consuming queries at work, this gave me a perfect pass time in between queries. So I decided to look around and see if I can find anything on the topic. The only thing that I could find that was remotely substantial was this thread. The initial poster of this thread also confused why “a bunch of murderers, rapists and thieves, who robbed many coastal settlements of their goods, women and children.” Responses ranged from jack Sparrows unparallelled coolness (and sexiness for some) to the pirate language.

The question this raises is that is it ok then to ignore the atrocities that pirates have caused just because their speech and mannerism are considered cool? I do not know the answer to this but I sure would love to hear from people on what they think about this issue.

Written by tundal45

October 24, 2008 at 6:51 pm