Though I very much like Perl, sqlplus sometimes is the correct tool to use, even from cron.
If you are on at least version 9 of Oracle, it becomes an even better tool, as external tables, bulk binds and the 'FORALL' statement can load data extremely quickly and efficiently. see: http://www.freelists.org/archives/oracle-l/10-2005/msg01318.html Jared On Mon, 2005-10-24 at 11:22, [EMAIL PROTECTED] wrote: > This runs as a CRON job loading syslog data into Oracle so SQL*Plus is not a > good answer. Thanks for help though. > > -----Original Message----- > From: Reidy, Ron [mailto:[EMAIL PROTECTED] > Sent: Monday, October 24, 2005 11:08 AM > To: Fontenot, Ward P.; [email protected] > Subject: RE: Newline inserted? > > If your file has a '\n' in it, it cannot possibly be an issue with DBI or > Oracle v anything. > > Are you processing individual SQL statements (does your file contain SQL > insert statements)? If so, DBI is not your best bet to do this. I would > rethink this and: > > a. Use SQL*Plus to load the file: > sqlplus -L /nolog <eof > connect uid/[EMAIL PROTECTED] > @file > commit; > exit > eof > > b. Consider getting a file of data (no SQL statemets) and use SQL*Loader to > load the data. > > -- > Ron Reidy > Lead DBA > Array BioPharma, Inc. > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Monday, October 24, 2005 12:01 PM > To: [email protected] > Subject: Newline inserted? > > > I have the following sql insert statement in a file: > > INSERT INTO message(FULLDATE,HOSTNAME,FACILITY,PRIORITY,MESSAGE,SHA1) > VALUES('2005-Oct-18 11:53:21','pkitest.wellsfargo.com','auth > ','info','su(pam_unix)[3638]: session opened for user oracle by > root(uid=0)','H4vgmf+OJsT99kCfQuIpALY7k9k='); > > When I attempt to load this file into Oracle 10g using the DBI I get the > following error: > > DBD::Oracle::st execute failed: ORA-00911: invalid character (DBD ERROR: > error possibly near <*> indicator at char 238 in 'INSERT INTO > message(FULLDATE,HOSTNAME,FACILITY,PRIORITY,MESSAGE,SHA1) > VALUES('2005-Oct-18 > 11:53:21','pkitest.wellsfargo.com','auth','info','su(pam_unix)[3638]: > session opened for user oracle by > root(uid=0)','H4vgmf+OJsT99kCfQuIpALY7k9k=')<*>;') > > This is the code that loads this file: > > while (<SQL>) { > chomp; > $sql = qq{$_}; > > $sth = $dbh->prepare($sql); # Should these be outside the loop? > $sth->execute(); > } > > Od -cx FILENAME shows a "\n" character at the <*> point in the above > file. VI -b does not. Is this a known issue with DBI and 10g or am I > overlooking something? > > > Paul Fontenot > WFS - CAST Operations > Email: [EMAIL PROTECTED] > Phone: (480) 437-7795 > > This message may contain confidential and/or privileged information. If > you are not the addressee or authorized to receive this for the > addressee, you must not use, copy, disclose, or take any action based on > this message or any information herein. If you have received this > message in error, please advise the sender immediately by reply e-mail > and delete this message. Thank you for your cooperation. > > This electronic message transmission is a PRIVATE communication which > contains > information which may be confidential or privileged. The information is > intended > to be for the use of the individual or entity named above. If you are not > the > intended recipient, please be aware that any disclosure, copying, > distribution > or use of the contents of this information is prohibited. Please notify the > sender of the delivery error by replying to this message, or notify us by > telephone (877-633-2436, ext. 0), and then delete it from your system. > >
