Re: Run SQL script which contains multiple line statements

[talexb]

The DBI module has fairly complete documentation -- it should allow you to run SQL statements of any length. However, I don't see much effort (or any Perl) in this question.

What have you tried?

[karthik.raju]

Hi, I've tried with this option

system("D:\\oracle\\product\\11.2.0\\client_1\\BIN\\sqlplus.exe userna
+me/password\@ServiceName");

with this, we can able to open  sql > command prompt but now how we can pass the .sql file ?
I've tried so many ways, like

system("D:\\oracle\\product\\11.2.0\\client_1\\BIN\\sqlplus.exe creden
+tials @C:\\Users\\namburuk\\Desktop\\CostSettlement_DBScripts\\MI_SCR
+_0001_FS_sample.sql");

but i'm getting few errors.

[Corion]

Consider using DBI instead and running your SQL through Perl directly.

All your other questions seem to relate more on how to operate the sqlplus.exe program. Can you please explain where Perl is necessary for the operation?

Do you know how to run the sqlplus.exe program outside of Perl and get the results into a text file?

Also, "a few errors" is not something we can provide specific help on. Please tell us what you did, the exact error message and what you expect to happen instead.

[talexb]

... but i'm getting few errors.

Think about anyone, in any industry, trying to diagnose the root cause of a problem based on this comment. It's really an impossible task.

By all means pass along *all* of the errors you've seen (OK, maybe just the first dozen), so that we can better diagnose what's going on.

[poj]

but i'm getting few errors.

There is an error in the sql CREATE table statement in your OP, a trailing comma.
Try something like this

#!perl
use strict;
use warnings;

# configuration
my $sqlplus = 'sqlplus';#'D:/oracle/product/11.2.0/client_1/BIN/sqlplu
+s.exe';
my $dir     = 'C:/Users/namburuk/Desktop/CostSettlement_DBScripts/';
my $sqlfile = 'MI_SCR_0001_FS_sample.sql';
my $logon   = 'user/password@ServiceName';

#open IN,'<',$dir.$sqlfile or die "$!";
my $sql = join '',<DATA> ; # use IN
#close IN;

# add exit to sql to return from command line
open OUT,'>','~temp.sql' or die "$!";
print OUT $sql."\nexit;\n";
close OUT;

# run sqlplus.exe in silent mode
system($sqlplus,'-S',$logon,'@~temp.sql') == 0
   or die "Couldn't launch $sqlplus: $!";


__DATA__
SELECT CURRENT_TIMESTAMP FROM DUAL;

CREATE TABLE TRN_SAAA
(
   TRN_ST   NUMBER (30) NOT NULL,
   TRN_SE   NUMBER (30) NOT NULL, 
   CHKD     VARCHAR2 (1) DEFAULT 'N' NOT NULL  
);

INSERT INTO TRN_SAAA VALUES (1,1,'A');
INSERT INTO TRN_SAAA VALUES (2,1,'B');
INSERT INTO TRN_SAAA VALUES (3,1,'C');

SELECT * FROM TRN_SAAA;

poj