REGEXP_LIKE W/DBI

dirtdog has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I'm trying to execute sql in perl w/DBI that works when i run from sqlplus using Oracle 11, but it doesn't seem to work when I run it in perl using DBI. It doesn't fail. It just doesn't return anything.

REGEXP_LIKE(note_tx, 'CA\s+\n?Merged\s+\n?From:\s+\n?Vendor:')
[download]

I'm using perl v5.8.0 with DBI version 1.30 on aix versions 6.1

Does anyone know why it's not working?

Any help is much appreciated

thanks, Tony

Comment on REGEXP_LIKE W/DBI Download Code

Replies are listed 'Best First'.
Re: REGEXP_LIKE W/DBI by erix (Prior) on May 29, 2014 at 22:39 UTC
Why post the SQL? The SQL does work. You tested it yourself. (I did too, see below). That reduces your question to "it doesn't work" while you give absolutely no further information about wat 'it' might be. You should post the perl DBI code that fails (ideally, a self-contained example of that failure). I tested your query on oracle 11 too (see this): SQLfiddle.com uses oracle 11 too: in SQLfiddle's left-hand pane: `create table t (note_tx varchar2(100));` [download] and in the right-hand pane: `insert into t values ('1st: XX Merged From: Vendor: Acme Corp 1'); insert into t values ('2nd: CA Merged From: Vendor: Acme Corp 2'); select * from t where REGEXP_LIKE(note_tx, 'CA\s+\n?Merged\s+\n?From:\s+\n?Vendor:');` [download] which displays the second record, as expected.	[reply] [d/l] [select]
Re: REGEXP_LIKE W/DBI by runrig (Abbot) on May 29, 2014 at 23:30 UTC
Perhaps you need to double up on the backslashes? `my $like = "REGEXP_LIKE(note_tx, 'CA\\s+\\n?Merged\\s+\\n?From:\\s+\\n +?Vendor:')";` [download]	[reply] [d/l]
Re^2: REGEXP_LIKE W/DBI by AnomalousMonk (Archbishop) on May 30, 2014 at 01:48 UTC
Or even single down on the quotes: `c:\@Work\Perl\monks>perl -wMstrict -le "my $like = q{REGEXP_LIKE(note_tx, 'CA\s+\n?Merged\s+\n?From:\s+\n?Ven +dor:')}; print qq{:$like:}; " :REGEXP_LIKE(note_tx, 'CA\s+\n?Merged\s+\n?From:\s+\n?Vendor:'):` [download] BTW: `\s` includes `\n` so `\s+\n?` is redundant; might as well just be `\s+`	[reply] [d/l] [select]
Re^2: REGEXP_LIKE W/DBI by dirtdog (Monk) on May 30, 2014 at 12:49 UTC
Sorry for the late response, but i had to leave unexpectedly yesterday shortly after posting my question. "Doubling up" on the backslashes solved the issue! Thanks again.	[reply]
Re: REGEXP_LIKE W/DBI by GotToBTru (Prior) on May 29, 2014 at 21:19 UTC
Not enough code to work with. Can you elaborate? I am assuming you have already verified that the database connection works. Just guessing at this point, but I would try incremental regexes to see if you can identify if it is a problem with quoting, or with literals. 1 Peter 4:10	[reply]