Run SQL script which contains multiple line statements

[karthik.raju]

Hi,
How to execute the .sql file which contains multiple line statements.

CREATE TABLE TRN_SAAA
(
   TRN_ST   NUMBER (30) NOT NULL,
   TRN_SE            NUMBER (30) NOT NULL, 
   CHKD                  VARCHAR2 (1) DEFAULT 'N' NOT NULL,
);
 
ALTER TABLE TRN_STL ADD CONSTRAINT XPK PRIMARY KEY (TRN_STLN);
ALTER TABLE TRN_STLM ADD CONSTRAINT XFK1 FOREIGN KEY  (TRN_S) REFERENC
+ES  TRN_SE;

sqlplus command tool has been installed in my machine. and with the help of SQL client, i can able to run

SQL> @C:\Users\aaa\Desktop\DBScripts\M_sample.sql

i've tried by reading the file and executing line by line,
since there are multi line statements, so cant able to complete the process correctly.
Can you please help on how we can execute this.
Thanks,
Karthik

[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

[Corion]

You will have to split up the SQL into separate statements.

DBIx::RunSQL implements a crude approach by splitting the SQL at ;\n mostly.