Loop through the .sql file and executing each statement in it using perl script

Question

I have a .sql file which contains multiple SQL statements. I need to loop over each statement and execute them from a Perl script. The problem is that the SQL statements are multiline statements and they include comments.

Up until now, we used the sqlplus command manually. But now we need to automate it to execute every statement and capture the success or failure of each statement. If a statement fails, I need to stop executing SQL statements from the file. As far I know, this isn't possible with sqlplus; it goes on executing the remaining statements after one of the statement fails.

The .sql file looks as below....

--Add new columns to TABLE_NAME table

ALTER TABLE "DATA"."TABLE_NAME" ADD(NAME varchar2(30));

--Create new table FILE_4G_TABLE

CREATE TABLE "DATA"."FILE_4G_TABLE"
(

 abc VARCHAR2(30 CHAR),

 def NUMBER(38),

 ghi NUMBER,

 JKL VARCHAR(32)

CONSTRAINT "FILE_4G_TABLE" PRIMARY KEY ("abc")) TABLESPACE "DATA";

Someone, please guide me.

What flavour of SQL is being used? (For which database server?) — ikegami
– ikegami, Commented Dec 28, 2016 at 6:24
How you communicate with the database is of no importance. You're not trying to do that; you're trying to parse SQL statements. — ikegami
– ikegami, Commented Dec 28, 2016 at 8:19

Community · Accepted Answer · 2017-05-23 12:16:25Z

1

Parse the sql file and push all statements in an array, then loop over the array and use DBI to prepare and execute the statements. SQL::Statement might be helpful as suggested by ikegami.

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Dec 28, 2016 at 6:27

Chankey Pathak

21.8k12 gold badges88 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hambone · Accepted Answer · 2017-01-01 01:52:24Z

0

Presupposing your SQL file is not huge, something like this might work. I'm making a huge leap here that splitting by ';' is enough to separate statements. If you had a ';' in a comment or within a literal, that would pretty much mess things up.

use strict;

use DBI;
use DBD::Oracle;

my $dbh = DBI->connect('dbi:Oracle:MyOracleService', 'user', 'password');

$/ = undef;
open my $IN, '<', 'test.sql' or die;
my $sql = <$IN>;
close $IN;

foreach my $mini_sql (split /;/, $sql) {
  $dbh->do($mini_sql);
}

$dbh->disconnect;

Also, this example was Oracle... if your DBMS is something else... well, you know.

answered Jan 1, 2017 at 1:52

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Collectives™ on Stack Overflow

Loop through the .sql file and executing each statement in it using perl script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related