Matieu, See below:
---------------- Ron Reidy Senior DBA Array BioPharma, Inc. -----Original Message----- From: Mathieu Barbeau [mailto:[EMAIL PROTECTED] Sent: Monday, May 17, 2004 7:00 AM To: Reidy, Ron; [EMAIL PROTECTED] Subject: RE: Question with DBI versus PERL Ok, but, my SQL file contains procedure with variables. If I execute one statement at once (if I parse my SQL file) my pl/sql will work? I'm not sure I understand you here. When you say: "1. All spool statements will turn to Perl print() calls. 2. Any PL/SQL dbms_output calls will need to be handled with the DBD::Oracle calls to enable dbms_output." What I understands: 1. I need to intecept my spool statement and call print()? 'spool' is a SQL*Plus command - not a SQL command. DBI only runs SQL commands. As you know, the spool command captures output to the file given as an argument to 'spool' in SQL*Plus. Because you are not running SQL*Plus, this mechanism is not available to you. To get the output, you will need to use the Perl built-in print() function. 2. I can't use DBI interface? I need to use directly the DBD::Oracle? DBI is a general interface to databases. You still need a driver (an implementation layer) to perform your work. In your case, you will either need DBD::Oracle or DBD::ODBC (I recommend DBD::Oracle, but this is just my personal preference). The driver is specified in the DBI connect() function. Thanks a lot! P.S. My script run with dbi but for one sql statement at once Are you saying you want to run all statements at once? You will need to do this within a PL/SQL block. Output will need to be managed using DBMS_OUTPUT (be aware of buffer limits in DBMS_OUTPUT). From: "Reidy, Ron" <[EMAIL PROTECTED]> To: "Mathieu Barbeau" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject: RE: Question with DBI versus PERL Date: Fri, 14 May 2004 15:19:08 -0600 MIME-Version: 1.0 Received: from onion.perl.org ([63.251.223.166]) by mc7-f10.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Fri, 14 May 2004 14:20:03 -0700 Received: (qmail 6131 invoked by uid 1005); 14 May 2004 21:19:42 -0000 Received: (qmail 6116 invoked from network); 14 May 2004 21:19:42 -0000 Received: from x1.develooper.com (63.251.223.170) by onion.develooper.com with SMTP; 14 May 2004 21:19:42 -0000 Received: (qmail 3314 invoked by uid 225); 14 May 2004 21:19:42 -0000 Received: (qmail 3306 invoked by alias); 14 May 2004 21:19:41 -0000 Received: from smtp1.arraybiopharma.com (HELO smtp1.arraybiopharma.com) (65.114.206.101) by la.mx.develooper.com (qpsmtpd/0.27.1) with ESMTP; Fri, 14 May 2004 14:19:41 -0700 Received: from ims01bldr.arraybp.com by smtp1.arraybiopharma.com id i4ELIsJQ012696; Fri, 14 May 2004 15:18:55 -0600 Received: from fiji.arraybp.com ([10.10.10.215]) by ims01bldr.arraybp.com with Microsoft SMTPSVC(5.0.2195.6713); Fri, 14 May 2004 15:18:56 -0600 X-Message-Info: JGTYoYF78jHEESQ34gOiw0saBXPgxldm Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm Precedence: bulk List-Post: <mailto:[EMAIL PROTECTED]> List-Help: <mailto:[EMAIL PROTECTED]> List-Unsubscribe: <mailto:[EMAIL PROTECTED]> List-Subscribe: <mailto:[EMAIL PROTECTED]> Delivered-To: mailing list [EMAIL PROTECTED] Delivered-To: [EMAIL PROTECTED] X-Spam-Status: No, hits=0.0 required=7.0tests= X-Spam-Check-By: la.mx.develooper.com Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Content-Class: urn:content-classes:message Message-ID: <[EMAIL PROTECTED]> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Question with DBI versus PERL thread-index: AcQ57y3We6zZtYoiTTqCuDV5UAeudwAABJ8w X-OriginalArrivalTime: 14 May 2004 21:18:56.0814 (UTC) FILETIME=[110114E0:01C439F9] X-Spam-Rating: onion.develooper.com 1.6.2 0/1000/N Return-Path: [EMAIL PROTECTED] Mathieu, Sure, you can run the SQL statements in your file with DBI, but you will need to parse the file to get all the SQL statements and then run them through the DBI interface. I would suggest that if you need to use Perl/DBI, that you instead look at a different method than running the SQL statements from the file, parsing the individual SQL statements, and instead look at possibly using the Class::Phrasebook and Class::Phrasebook::SQL modules from CPAN. The number of SQL statements is not significant with Perl/DBI. The following will be your output issues: 1. All spool statements will turn to Perl print() calls. 2. Any PL/SQL dbms_output calls will need to be handled with the DBD::Oracle calls to enable dbms_output. You will need to: 1. Read the Perl docs to get familiar with the syntax of the language. 2. Read and understand the DBI docs (perldoc DBI). 3. Read and understand the DBD::Oracle docs (perldoc DBD::Oracle). Hopefully, I have not clouded the issue for you. ----------------- Ron Reidy Senior DBA Array BioPharma, Inc. -----Original Message----- From: Mathieu Barbeau [mailto:[EMAIL PROTECTED] Sent: Friday, May 14, 2004 2:08 PM To: [EMAIL PROTECTED] Subject: Question with DBI versus PERL Hi, I have a really big sql scripts with many select, procedures and spool spool off statements. I want to know how can i run this sql script with DBI. With sqlplus, this sql script run correctly. But I don't know how can i run this script with perl. Can you help me? 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. 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.
