Oracle SQL Developer

Having been developing my PL/SQL in Toad Data Modeler, and running via a customer VB program, the debugging effort has been poor at best. A co-worker pointed me to Oracle SQL Developer, it’s in the league as MS SQL Server Management Studio, but for Oracle!

The other point I’ve been tripping on, in PL/SQL to use a date (via SQL*Plus) to_date(‘2007-08-06′,’YYYY-MM-DD’) is the trick.

PL/SQL cryptic error messages

I have just found (oh thank you Google and the Oracle forums) the solution to my PL/SQL error

CA_ADD_DESIGN_1 (28,5): PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification

My first problem was which line does the 28 refer to, as I’m editing one large PL/SQL file, it appears to count from the line before the

CREATE OR REPLACE
PROCEDURE ca_add_design_1(

But the real problem was what did the error mean.

The third Google result had a Oracle forum post, so I should have gone there first, but I didn’t. The first two sites are just AdSense whore sites, in fact 6 of the first 10 results are AdSense whores. It makes me sad that these sites are there, as the information contain was worthless, and been top Google results means they will get a lot of hits. GRRR and for bonus points these results were from google.co.nz, using google.com the useful answer is in 5th place.

Anyway the Oracle forum answer said “you have an extra comma after your lass SET clause before the WHERE” and low and behold I did.

    UPDATE  ca_p_design
    SET     design_type_id=NVL(pDesignType,design_type_id),
            session_id=NVL(SessionID,session_id),
            time_end=NVL(pTimeEnd,time_end),
    WHERE   design_id=ID;

So now my Update looks like:

    UPDATE  ca_p_design
    SET     design_type_id=NVL(pDesignType,design_type_id),
            session_id=NVL(SessionID,session_id),
            time_end=NVL(pTimeEnd,time_end)
    WHERE   design_id=ID;

and it works! I had spent ages triple checking each of my column names, and it turned out to be the column I “didn’t mention” that was upsetting it.

SSIS Experimenting

For the last week (or unofficially last two weeks) I’ve been using SQL Server Integration Services to build the import process for the daily data exchange we have with our customers. The system is used to aggregate a fragmented market place after deregulation. The cost saving coming from many companies using a single source to do the grunt work (so taking that line of thought further, what value was gained by deregulation, but I digress)

The way data is exchanged is via flat files that are fixed position or comma separated. After seeing demo’s of SSIS at TechEd and the likes, it seemed a great tool to manage the data import/export process here. This would leave the core system to focus on aggregation and job management. The other tool that could have great impact is the Workflow Foundation.

The first challenge to over come is that in the data exchange files, many relational tables are merged and layed out by the related rows after each other (with foreign key relationships implied by order). This required that I build a custom source component that could parse the rows and output the separated table, but also insert the correct foreign keys. Well I’ve got this step completed with a hardcode format, and it works a treat inside SSIS. With the hurdle passed we now know we can process the different types of files we exchange with our current business partners.

Now at this point, the business decision of encapsulating our current business processes in SSIS, so the resulting data can be push directly in the database. For our current product, that’s a lot of work, with little clear gain. The major gains I see are the higher level view you get in SSIS would allow better understanding of the import process. We would separate the import process from the core product, allowing independent release cycles. Load would be removed from the SQL database (via stored procedures) to a potential different location to allow better load management.

At this point I think the management team is putting the idea to rest.

Things I’d still like to do with the custom component is make it configurable at design time via a GUI, with either external configuration files, or stored in the meta data of the SSIS package (haven’t worked out how to do this one yet). I’d be keen to release the component, to give back to the community.

I found the online MSDN really helpful and the people at the Microsoft Technical Forum where great, especially Jamie Thomson and Simon Sabin. The resources at sqlis.com were also really great.