在使用Doctrine DBAL在Oracle数据库上执行查询时，如何逃避oracle保留关键字？

Question

I'm trying to use Doctrine DBAL to execute the following query on an Oracle database. I've executed other queries before so it's not a connection issue. The problem stems from trying to escape the reserved word Number. I need to select a column as Number for later logic down the road. The simplified working version of the query is as follows:

SELECT instructor.SecNum AS "Number"
      FROM (
           SELECT dbA.ID,
                  dbA.ClsNumber as SecNum,
                  dbA.CrsCatlgNbr,
                  ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA.CrsCatlgNbr) AS rn
           FROM dbA
           WHERE (dbA.SubCd = '5')
           AND (dbA.ID IS NOT NULL)
           ) instructor,
           (
            SELECT dbB.SecNum
            FROM dbB
            WHERE (dbB.SubCd = '5')
           ) student
      WHERE (instructor.rn = 1)
      AND (instructor.SecNum = student.SecNum);

The PHP code is as follows

// set up connection stuff and relevant variables
$sectionTable = array();
$dss1prdDatabase->executeQuery(
"SELECT instructor.SecNum AS \"Number\"
      FROM (
           SELECT dbA.ID
                 dbA.ClsNumber as SecNum
                 dbA.CrsCatlgNbr
                 ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA   CrsCatlgNbr) AS rn
           FROM dbA
           WHERE (dbA.SubCd = '5')
           AND (dbA.ID IS NOT NULL)
           ) instructor,
           (
            SELECT dbB.SecNum
            FROM dbB
            WHERE (dbB.SubCd = '5')
           ) student
      WHERE (instructor.rn = 1)
      AND (instructor.SecNum = student.SecNum);", $sectionTable);

When I do this I get an ORA-00911: invalid character error.

I've also tried preparing the statement first and then binding the escaped column name to the query:

// ...
$ESCAPEDNUMBER = '\"Number\"';
$sql = "SELECT instructor.SecNum AS ?
      FROM (
           SELECT dbA.ID
                 dbA.ClsNumber as SecNum
                 dbA.CrsCatlgNbr
                 ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA   CrsCatlgNbr) AS rn
           FROM dbA
           WHERE (dbA.SubCd = '5')
           AND (dbA.ID IS NOT NULL)
           ) instructor,
           (
            SELECT dbB.SecNum
            FROM dbB
            WHERE (dbB.SubCd = '5')
           ) student
      WHERE (instructor.rn = 1)
      AND (instructor.SecNum = student.SecNum);"
$stmt = $dbalDatabaseConnection->prepare($sql);
$stmt->bindvalue(1, $ESCAPEDNUMBER);
$stmt->execute($sectionTable);

But this gives me a different error: ORA-00923: FROM keyword not found where expected.

The ORA-00923 error is also found when I say $ESCAPEDNUMBER = addslashes('"Number"');

I have no idea what the issue is. Any ideas?

Again my question is how to properly escape oracle reserved words in php queries using Doctrine DBAL. I use quotes because in Oracle quotes are required in the query otherwise you'll get a ORA-00923: FROM keyword not found where expected" error.

Relevant documentation on DBAL can be found here And also

Answer 1

Got it. The issue was the semi-colon at the end of the query. This works fine in the console but apparently gives the ORA-00911: invalid character when ran with DBAL. Go figure...

Fixed query:

// set up connection stuff and relevant variables
$sectionTable = array();
$dss1prdDatabase->executeQuery(
"SELECT instructor.SecNum AS \"Number\"
      FROM (
           SELECT dbA.ID
                 dbA.ClsNumber as SecNum
                 dbA.CrsCatlgNbr
                 ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA   CrsCatlgNbr) AS rn
           FROM dbA
           WHERE (dbA.SubCd = '5')
           AND (dbA.ID IS NOT NULL)
           ) instructor,
           (
            SELECT dbB.SecNum
            FROM dbB
            WHERE (dbB.SubCd = '5')
           ) student
      WHERE (instructor.rn = 1)
      AND (instructor.SecNum = student.SecNum)", $sectionTable);