PERL using WITH clause in Database prepare

Question

I'm trying to prepare a complex query using WITH in perl against an Oracle DB and keep getting an error when preparing.

DBD::Proxy::st execute failed: Server returned error: Failed to execute method CallMethod: DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD ERROR: error possibly near <*>

Here is the sql I'm trying to prepare:

my $sql = <<EOT;
;WITH GCG as (
SELECT FIRST_NAME, CONFIRMATION,EMAIL,
row_number() over (partition by EMAIL order by (1)) gemail
FROM GENERAL_VIEW 
WHERE 
CONFIRMATION is not null
AND TO_CHAR(INSERT_DATE, 'YYYY-MM-DD HH24:MI:SS') <= ?
AND TO_CHAR(INSERT_DATE, 'YYYY-MM-DD HH24:MI:SS') >= ?
AND EMAIL is not null
)
SELECT FIRST_NAME, CONFIRMATION,EMAIL FROM GCG WHERE gemail=1;
EOT

I've executed the query in DataGrip and I get a result. Seems there may be a perl nuance I'm missing.

Anyone have any ideas that could help me out?

Thanks in advance.

Is that a stray semi-colon `;` in the line `;WITH GCG as (` ? — Mark Stewart, Sep 24 '19 at 21:46
Does it work without the leading semi-colon (`;`)? If not, where does it show the asterisk (`*`)? — ikegami, Sep 24 '19 at 21:51
Ok. That's embarassing. I removed the semi-colons and it worked. I thought I had tried that but I'm mistaken. Thanks everyone, sometimes fresh eyes on something helps. — Kevin Murray, Sep 25 '19 at 12:18

Bob Jarvis - Слава Україні · Accepted Answer · 2019-09-24T22:23:26.697

1

Change your statement to

my $sql = <<EOT;
WITH GCG as (
SELECT FIRST_NAME, CONFIRMATION,EMAIL,
row_number() over (partition by EMAIL order by (1)) gemail
FROM GENERAL_VIEW 
WHERE 
CONFIRMATION is not null
AND TO_CHAR(INSERT_DATE, 'YYYY-MM-DD HH24:MI:SS') <= ?
AND TO_CHAR(INSERT_DATE, 'YYYY-MM-DD HH24:MI:SS') >= ?
AND EMAIL is not null
)
SELECT FIRST_NAME, CONFIRMATION,EMAIL FROM GCG WHERE gemail=1
EOT

Bon chance.

edited Sep 24 '19 at 22:23

answered Sep 24 '19 at 22:05

Bob Jarvis - Слава Україні

48,992
9
77
110

PERL using WITH clause in Database prepare

1 Answers1