Oracle DBD Error from Perl script

Question

I am trying to run perl script but i get an oracle error.

DBD::Oracle::db prepare failed: ORA-01756: quoted string not properly terminated (DBD ERROR: OCIStmtPrepare)

But this SQL QUERY perfectly works fine in TOAD

MY perl connection details:

my $dbh = DBI->connect($dsn, $dbuser, $dbpass, { RaiseError => 1, AutoCommit => 0 });
    my $sth=$dbh->prepare($SQL);
    $sth->execute or die "EXEC ERROR $sth->errstr";

sql query:

SELECT name FROM employee WHERE
            event IN ('IPO', 'RIGHTS')
        AND (NOT market_code = 'ID' OR NOT event = 'RIGHTS')
        AND NOT market_code = 'IN'
        AND NOT market_code = 'NZ'
        AND name NOT LIKE '%stat%'
          AND NOT REGEXP_LIKE (name, 'S.K(Q|S)$')
          AND name NOT LIKE '.%'
          AND name NOT LIKE '%ol.SI'
          AND name NOT LIKE '%bi.SI'

Could you show the Perl code which creates $SQL please? Also try printing $SQL to check it's what you think it is. BTW the or die is unnecessary since you have RaiseError on. — Schwern
– Schwern, Commented Mar 20, 2015 at 7:22
You have a $ in there. Maybe that confused Perl when creating the query text? — Thilo
– Thilo, Commented Mar 20, 2015 at 7:26

Thilo · Accepted Answer · 2015-03-20 10:09:18Z

0

Perl will interpolate double-quoted string literals, like

 my $SQL = "REGEXP_LIKE (name, 'S.K(Q|S)$')";

In here, your "variable" $' will be replaced with its value.

If you don't want that, use a non-interpolating version:

 my $SQL = q{REGEXP_LIKE (name, 'S.K(Q|S)$')};

Single-quoted strings would also do, but since you have single quotes inside, the q{} is convenient. You can choose any terminator you want (such as q[]) and you can make it interpolate, too, with qq{}.

answered Mar 20, 2015 at 10:09

Thilo

264k107 gold badges527 silver badges674 bronze badges

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

1 Comment

stephenjacob Over a year ago

hi Thilo . thank you very much !!!!!it perfectly works tried my $SQL = q{REGEXP_LIKE (name, 'S.K(Q|S)$')};

Collectives™ on Stack Overflow

Oracle DBD Error from Perl script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related