Insert data in clob column and varchar column in a single insert statement using PHP Oci8

Question

I want to insert the data in clob and varchar2 column in a single insert statement in oracle.

$conn=oci_connect();

    if (!$conn)
    {
            $e = oci_error();   // For oci_connect errors pass no handle

            echo "if not connection<br>";

            echo htmlentities($e['message']);
    }
    else
    {
    //Where email and type column has varchar2 datatype and elements column has CLOB datatype
            $isql="INSERT INTO TEST_DEV
            VALUES(':email',':type',':elements')";

            $stmt = oci_parse($conn,$isql);

            oci_bind_by_name($stmt, ":email", 'test@test.com');
            oci_bind_by_name($stmt, ":type", 'test_records');
            oci_bind_by_name($stmt, ":elements", 'asdadasdsa|asdsa',-1,OCI_B_BLOB);

            $rc=oci_execute($stmt);

            if(!$rc)
            {
                $e=oci_error($stmt);
                var_dump($e);
            }

            oci_commit($conn);
        }

        oci_free_statement($stmt);
        oci_close($conn);

This code is giving the error. How to solve ??

Thanks,

Faisal Nasir

Answer 1

You need to remove the apostrophes around the placeholders in your SQL, otherwise you'll insert the text ':email' literally, not the variable data you are trying to bind:

$isql = 'INSERT INTO TEST_DEV VALUES(:email, :type, :elements)';

oci_bind_by_name() doesn't accept string literals as values since it binds by reference. Assign your values to variables and use those in the function call:

$email = 'test@test.com';
oci_bind_by_name($stmt, ':email', $email);

Since you are only inserting character data (it's a CLOB after all) you don't need to do anything different with your binding. oci_bind_by_name() defaults to SQLT_CHR so just bind like the other variables:

$elements = 'asdadasdsa|asdsa';
oci_bind_by_name($stmt, ':elements', $elements);

For further reference (including multiple examples of working with LOBs) read Working with LOBs in Oracle and PHP in 'The Oracle + PHP Cookbook'.