Oracle-L: Re: Perl DBI/SQL question - For those who use it...

From: Philip Douglass <philipd_at_sirs.com>
Date: Fri, 03 Jan 2003 10:48:56 -0800
Message-ID: <F001.00526C4D.20030103104856@fatcity.com>

Perl DBI/SQL question - For those who use it...Hi Lisa,

It's been awhile since I've used Perl DBI, but from what you said, I think you're mixing up two different ideas. Ksh doesn't know how to talk to a database, so you just use it to invoke sqlplus, which handles the database communication and runs the script. Perl is also capable of doing the _exact_ same thing, but you would not be using the DBI, you would just be using Perl to invoke sqlplus. Here's a one-liner example:

    cat > test.sql
    select sysdate from dual;
    quit
    <Ctrl-d>
    perl -e 'system("sqlplus", "-s", "scott/tiger", "\@test")'

This is completely different from using the DBI. You use the DBI when you want more flow control and error handling, etc... You can still keep your scripts in a separate file if you really want to, just read them into a variable and prepare() and execute() it. Personally, I would define my DBI SQL in the perl file -- easier to keep track of it there.

If you like, you can contact me off-list and I can show you some things I've written. In fact, you're right down the road from me --- Do you go to the SF Oracle User Group meetings?
--

Philip Douglass
Internet Networking Group
Database Administrator
SIRS Publishing, Inc.
1100 Holland Dr.
Boca Raton, FL 33487

Original Message ----- From: Koivu, Lisa To: Multiple recipients of list ORACLE-L Sent: Friday, January 03, 2003 12:09 PM Subject: Perl DBI/SQL question - For those who use it...

Hi everyone,

This may be a stupid question. If so please humor me with a stupid answer. However:

I FINALLY have the fun fun fun chance to change one of my data loads to use the DBI instead of the procedures I hacked together. In true ksh style I had written my loads to fire a sql script (calling a stored proc) that was stored separately. It seems to me the DBI wants the text of the sql script embedded piece by piece in the code. I have looked around for examples because even though the DBI seems straightforward, it doesn't take much to confuse me. I don't see examples of firing a sql script from the DBI (like this sqlplus /@dbname < @script.sql > logfile.log ... Gosh do I miss unix, everything was SO EASY)

So my questions to you, my learned friends, are:

is it not perl-style to store the sql in a separate file? I understand I may be missing the opportunity for more specific error handling here but honestly at this point it does not matter. The thing fails, I restart the whole script.
Does anyone have an example of firing the DBI and calling a sql script like I could so easily do in ksh?

Any and all comments are welcome. Thank you

I wish everyone a rested and relaxing weekend.

  Lisa Koivu
  Oracle Database Monkey
  Fairfield Resorts, Inc.
  5259 Coconut Creek Parkway
  Ft. Lauderdale, FL, USA 33063

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Philip Douglass
INET: philipd_at_sirs.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jan 03 2003 - 12:48:56 CST