why oci_bind_by_name not working if inside oracle function

Question

Sorry for my bad english. I think it would be easier if I just put my codes here. So here is what I have tried:

//this is working

$sql = "select ID, NAME, BDATE from EMPLOYEES where BDATE >= DATE '2020-01-01'"; 

//but when I tried to do this, it fails:

$sql = "select ID, NAME, BDATE from EMPLOYEES where BDATE >= DATE :bdate"; //this is 
$compiled = oci_parse($conn, $sql);
oci_bind_by_name($compiled, ":bdate", "2020-01-01");

above will raise this error:

"code":936,"message":"ORA-00936: missing expression"

I have tried something else like:

//fail

$sql = "select ID, NAME, BDATE from EMPLOYEES where BDATE >= TO_DATE(:bdate, 'YYYY-MM-DD')"; 
oci_bind_by_name($conn, ':bdate', '2020-01-01');

above will raise this error:

"code":1847,"message":"ORA-01847: day of month must be between 1 and last day of month"

//always fail when I put inside function like DATE, TO_DATE, UPPER, etc

$sql = "select ID, NAME, BDATE, STATUS from EMPLOYEES where upper(STATUS) = upper(:status)"; 

//event in In clause will also fail

$sql = "select ID, NAME, BDATE, STATUS from EMPLOYEES where upper(STATUS) in (:status1, :status2)"; 
oci_bind_by_name($compiled, ":status1", "SINGLE");
oci_bind_by_name($compiled, ":status2", "MARRIED");

Could please anyone tell me how to fix this? I have followed many tutorial from the documentation, and also from another stackoverflow answers. But nothing works.

Or is it maybe there is something I missed to set on my web server? or on my database server?

Fyi, I am using PHP7.2, OCI8, Oracle 11g.

thank you very much.

Answer 1

This worked for me on php 7.4,Oci8/Oracle 19c database.(I never worked on php just googled here and there)

    $sql = "SELECT ename FROM emp WHERE hiredate > TO_DATE(:hdate,'YYYY-MM-DD')";
    $stid = oci_parse($conn, $sql);
    $hdate = "1980-01-01";
     oci_bind_by_name($stid,':hdate', $hdate);
   // oci_bind_by_name($stid,":hdate", $hdate);  //this line also works i.e double quotes

Edit:- IN clause also works for me.This is what I learned you cannot pass literal values in OCI_BIND i,e first assign literal to a variable

$sql = "SELECT ename FROM emp WHERE job IN (:job1,:job2)";
$stid = oci_parse($conn, $sql);
$job1 ='CLERK';
$job2 = 'SALESMAN';
oci_bind_by_name($stid,':job1', $job1);
oci_bind_by_name($stid,':job2',$job2);
oci_execute($stid);

Check these link1 link2 for examples that works with Oracle Database