[SOLVED] PERL using WITH clause in Database prepare

PERL using WITH clause in Database prepare

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.

Solution

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.