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

[Philip Douglass]

Title: 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: 1. 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. 2. 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