Perl or PL/SQL?

It happens every three month – the project managers need to generate their quarterly report. Every three month they decide that the reports they used until now are not good enough and they come up with a new design for the report. The DBAs now need to get the data, preferably yesterday. After we gather the data and create the report, there is another week during which we try to convince them that the results are correct and they try to convince us to massage the data a bit to reflect the results they expected.

This time, to generate the report I need to collect data from six different databases which  together contain about 30 schema, one of them is SQL Server. Sounds like fun already.

Naturally, I want to write a script that will run all the necessary queries, do all the necessary calculations and aggregations, and dump everything into a table. Then I’ll grab the results from that table and put them into a nice looking Excel (with graphs and colors!).

My only dilemma is: Perl or PL/SQL?

Why Perl?

  1. I know Perl much better than PL/SQL, so I’ll get it written fast and more correctly.
  2. Perl has no problem connecting to SQL Server – I’m kind of worried it may be an issue with PL/SQL.
  3. If I do the report in Perl, I won’t have to create all those linked servers to all the other systems. Our security team dislikes linked servers.

Why PL/SQL?

  1. Well, learning PL/SQL was one of my objectives this year – so this is a great opportunity.
  2. It will probably run much faster. I can’t be sure about it at this stage, but this is usually true.
  3. Other DBAs can maintain PL/SQL code. I’m the only one who does Perl.

I think that if I’ll find a way to get the SQL Server data from PL/SQL, I’ll go the PL/SQL route. I think its called “heterogeneous server”, but last time I checked it was quite complicated to use. Maybe it is worth a second chance.

Advertisements

6 Comments on “Perl or PL/SQL?”

  1. John Scott says:

    Hi Chen,

    I’d definitely recommend using Oracle Application Express for this –

    http://www.oracle.com/technology/products/databaseapplication_express/index.html

    It’s free and you can install it into your Oracle DB and then create websites/dashboards etc. The advantages are –

    1) APEX runs inside the Database, so you can use SQL and PL/SQL within your app quite nicely.
    2) You can use the DB features like heterogeneous server to access data from other systems but still use the same app to display the data.

    Feel free to drop me a message if you want any more info/help etc.

    John.

  2. Mathew Butler says:

    I’ve used HS in the past to pull data from MS access. It’s really not that complicated to set up and test. I read the supporting doco for my platform – Heterogeneous Connectivity Administrator’s Guide and also this this link for practical application.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358

    There is also a good “investigating issues with HS” note on metalink, which I couldn’t find to reference.

    In my case, I was running reports on a bug database written in MS Access (home grown)- extracting the data, manipulating it and then loading it into a different application – Bugzilla to take advantage of some its ad-hoc reporting functionality. The long terms goal, was to migrate to Bugzilla.

    I found it much easier to leverage my Oracle/SQL/PL/SQL skills in order to achieve what I wanted – I couldn’t find a neat MS Access equivalent to Oracle analytics in order to do soem of the transforms I could easily complete in SQL if I was in Oracle. It also meant I learnt a something about HS.

    I did document the HS setup at the time, but just had trouble finding the doc. If you are think this may be useful, let me know and I’ll see if I have a copy at home.

    Mathew Butler

  3. Rich says:

    At my last shop we used “Oracle Transparent Gateway for DB2” which essentially let us create a database link from an Oracle DB to the DB2 database. This worked out well for most things, except when we were joining data from Oracle to DB2 over the gateway (way slow). Setting this up however wasn’t trivial, perhaps it’s easier to do with SQL server.

    Might be better if you could just dump the data from SQL Server and load it up into Oracle and then go to PL/SQL town on it (or perhaps use an External Table on the dumped text file).

    Let us know what you come up with and keep on bloggin…Rich

    p.s. if you need any pl/sql help let me know

  4. Freek says:

    Here is a link on an old howto setup generic connectivity that I have written:

    http://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=1857

  5. prodlife says:

    Hi John,

    Mmmm… APEX is definitely worth looking into. Especially if it means that someone else can write the reports with a nice web interface and I don’t have to deal with the ever changing requirements.

    However, I’m not sure I want to start maintaining a web application with users and such, it can grow into a big headache, and if I’m the only one who will use it – well I don’t really need fancy web interfaces, so why waste my time installing HTTP servers and such?

  6. prodlife says:

    Thanks to everyone with the generic connectivity tips. My situation is a bit more complicated than you guessed – the Oracle server is on Linux, which means fun with unixODBC and friends.

    I’m lucky to have such smart co-workers, who saw that the report can be generated according to the requirements without using any data from the SQL Server. Then they convinced me that I don’t need the linked servers either – have each server generate the report using its own data, and have an additional small script to just query the results and combine them in one report. With all the problems out of the way, I can now write this report in PL/SQL.

    Yay for smart readers and smart co-workers!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s