Compare Oracle Data

Question

Compare Oracle Data

Navigation

#1 by (1 votes)

0

I have the following column in oracle

DATA_FILTRO
01/04/2017 10:27:15

I would like to compare the month and year:

SELECT DATA_FILTRO
 FROM TABELA
WHERE TO_CHAR(DATA_FILTRO,'MM/YYYY') ='04/2017'

In PLSQL it works, however in PHP it gives error

oci_execute(): ORA-01843: not a valid month in

PHP code

public function metodo($data){
    $conn = new ConnectionFactory();
    $con = $conn->getConnection();
    $query = "SELECT DATA_FILTRO
                FROM TABELA
               WHERE TO_CHAR(DATA_FILTRO,'MM/YYYY') ='04/2017'";
    $stmt = ociparse($con, $query);

    oci_execute($stmt);
     while ($row = oci_fetch_array($stmt, OCI_ASSOC)){
        echo $row['DATA_FILTRO'];
     }
    $conn->closeConnection($con);
    return $protocoloList;
  }catch ( PDOException $ex ){  echo "Erro: ".$ex->getMessage(); }
}

In PHP Only works if the month is 05/2017

php oracle

asked by anonymous 10.05.2017 / 19:42

1 answer

How to update a collection in laravel / eloquent Help with script that maps routes

score 1 · Accepted Answer

ORACLE does not recommend you convert a field to perform a query, because if the column data_filter has an index, when converting the column the bank will not use the execution plan as the index, burdening the execution time of its query. Another point is that ORACLE recommends using bind variable to perform dynamic queries, because, when assembling a string with the date value (without bind) ORACLE will not use the statistics and will create a new execution plan, too burdening your query.

No bind:

$query = "SELECT DATA_FILTRO
            FROM TABELA
           WHERE DATA_FILTRO = TO_DATE('01/04/2017', 'DD/MM/YYYY')";

With Bind:

$query = "SELECT DATA_FILTRO
                FROM TABELA
               WHERE DATA_FILTRO = to_date(':data01', 'dd/mm/yyyy'";
$valor_data = '01/04/2017';
oci_bind_by_name($stid, ":data01", $valor_data);

link