Tag Archives: windows

The OS really does matter.

I admit that I’m not terribly proficient in Operating Systems other than those made by Microsoft. Back in 1999 or 2000, I attempted to install Linux on a spare machine in the interest of trying out some cool GUI experience that could be skinned but after about 2 weeks of trying, I still couldn’t get the network card to work and gave up. Other than that, my non-Windows experience pretty much consists of navigating my iPod and iPad. (And sometimes those don’t even make sense to me.)

To further prove my point, the following is an actual conversation that occurs regularly within our household:

My Partner:
Hey, do you know how to do X?
[speaking of a computer related task on her MacBook Pro]

Me:
Sure! You just right-click on …. um, oh yeah.

Today’s lesson is something that, in hind-sight, should have been glaringly obvious. However, I often take for granted that we all live in a Windows/Microsoft world.

I was working on a side project at work to convert a SQL script originally written for Microsoft SQL Server to Oracle. I took on the challenge with joy as I have a slight affinity for SQL*Plus after years of fighting with it. The original script was a stored procedure called from SQLCMD that output a text file in a specific format based on certain data in the database. The format of the text file had some specific requirements based on the process that would ultimately ingest it . Essentially, based on a data in a the database, I was to select certain data and format it in a specific way similar to the following:

SQL Server syntax:

SELECT
'Label1: ' + column1 + CHAR(13) + CHAR(10) +
'Label2: ' + column2 + CHAR(13) + CHAR(10)
FROM mytable;

The output in a text file looks something like this:

Label1: First Entry
Label2: First Code
Label1: Second Entry
Label2: Second Code
Label1: Third Entry

Migrating this to Oracle syntax is super easy.

SELECT
'Label1: ' || column1 || CHR(13) || CHR(10) ||
'Label2: ' || column2 || CHR(13) || CHR(10)
FROM mytable;

Plusses are replaced with double pipes; ASCII calls of CHAR() are replaced with CHR(). Simple.

So, I re-write the stored procedure (it was a bit more complex than my examples above) and create a .bat file to execute it, based on appropriate input, using SQL*Plus and a ton of parameters to make it real, plain text output.

SQL*Plus formatting parameters such as these limited my results to plain old text without headers, extra blank spaces after lines, etc.:

SET NEWPAGE 0;
SET SPACE 0;
SET LINESIZE 32767;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET MARKUP HTML OFF SPOOL OFF;
SET VERIFY OFF;
SET TRIMSPOOL ON;

If you haven’t guessed already, those CHAR(13) and CHAR(10) … (Or CHR(13) and CHR(10)) calls are important. They are the ASCII characters for, in order Carriage Return (13) and Line Feed (10).

So, I modify the Stored Proc to the appropriate syntax, make a ton of SQL*Plus formatting calls to get plain ol’ text out and test out the script against the database copy I have in the office. Golden. Off to the customer the script goes.

[Here's where I save you the back and forth between my contact, their input guys, our project manager, etc. which went on for quite some time.]

A few hours later they report that the export file hoses (aka breaks) the input processor. After a bit of debugging on their end, it appears that the export file looks like this in a text editor with symbols on:

Label1: First Entry[CR][CR][LF]
Label2: First Code[CR][CR][LF]
Label1: Second Entry[CR][CR][LF]
Label2: Second Code[CR][CR][LF]
Label1: Third Entry[CR][CR][LF]

The input isn’t designed to handle multiple [CR]s – it wants a [CR][LF] combination at the end of each line – and nothing else.

So, I re-run the SP in my test environment and view the output in a text editor with symbols on:

Label1: First Entry[CR][LF]
Label2: First Code[CR][LF]
Label1: Second Entry[CR][LF]
Label2: Second Code[CR][LF]
Label1: Third Entry[CR][LF]

Interesting, right? My output is exactly as expected. Theirs is different. WHY?

After analyzing the differences in environments the only thing I can come up with is that my Oracle instance is Windows based and theirs is Unix based. SQL*Plus is still running on Windows in both environments but … even if you’re selecting complete gibberish from a table (‘beit no table from SQLCMD or DUAL from SQL*Plus), you’d think the results would be the same. It’s only a Windows command-line after all …

Not so. And that’s my lesson learned today. Regardless of the tool used to access the database (afaik), the output is still dependent on the OS of the database host environment.

In the Windows world, the character [LF] (aka CHR(10) or CHAR(10)) is a Line Feed. The character [CR] (aka CHR(13) or CHAR(13)) is a Carriage Return. In the Unix world (as best I could determine from a few Google searches), the character [LF] (aka CHR(10) or CHAR(10), translates to a line feed and a carriage return. A [CR] (aka CHR(13) or CHAR(13)), is also a carriage return.

Although SQL*Plus was being run from Windows, the database backed was Unix and thus, so was the output. And so:

SELECT
'Label1: ' || column1 || CHR(13) || CHR(10) ||
'Label2: ' || column2 || CHR(13) || CHR(10)
FROM mytable;

Translates to the following on a Unix based database:

Label1: First Entry[CR][CR][LF]
Label2: First Code[CR][CR][LF]
Label1: Second Entry[CR][CR][LF]

I modified the original query as follows:

SELECT
'Label1: ' || column1 || CHR(10) ||
'Label2: ' || column2 || CHR(10)
FROM mytable;

And the results come out like this (against a Unix based database):

Label1: First Entry[CR][LF]
Label2: First Code[CR][LF]
Label1: Second Entry[CR][LF]

So yes, in hindsight, this should’ve been obvious – even if it’s gibberish SQL, it’s still being executed against the actual database. And, if that database uses a different OS, the results may vary.

Lesson learned.