Jump to content

ORA-03131: an invalid buffer was provided for the next piece


chn262

Recommended Posts

hello,

 

I not able to call a function with php oci8. i got an error. ORA-03131: an invalid buffer was provided for the next piece. 

the error seem to be at "oci_execute" statement.

anyone can help? thank you

 

// Establish a connection to Oracle database

$connection = oci_connect($database_username, $database_password, "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$database_host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=$database_service_name)))");

if (!$connection) {
    $error_message = oci_error();
    echo "Failed to connect to Oracle: " . $error_message['message'];
    exit();
}

// Establish a connection to Oracle database

$connection = oci_connect($database_username, $database_password, "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$database_host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=$database_service_name)))");

if (!$connection) {
    $error_message = oci_error();
    echo "Failed to connect to Oracle: " . $error_message['message'];
    exit();
}
echo "Connected to Oracle database successfully<br>";

// Prepare the PL/SQL call statement
$sql = "BEGIN :result := wm_mvs_mvd.get_employee_salary(:p_employee_id); END;";
$statement = oci_parse($connection, $sql);

if (!$statement) {
    $error_message = oci_error($connection);
    echo "Failed to prepare SQL statement: " . $error_message['message'];
    exit();
} else {
    echo "SQL statement prepared successfully<br>";
}

// Set the prefetch rows attribute for the statement
oci_set_prefetch($statement, 50000); // Adjust the buffer size as needed
if (!$statement) {
    $error_message = oci_error($connection);
    echo "Failed to set prefetch for SQL statement: " . $error_message['message'];
    exit();
} else {
    echo "Prefetch set successfully<br>";
}

// Bind the parameters
$p_employee_id = 1; // Example employee ID
oci_bind_by_name($statement, ':p_employee_id', $p_employee_id, 255);
oci_bind_by_name($statement, ':result', $result, 255); // Assuming the result length is 255 characters

if (!$statement) {
    $error_message = oci_error($connection);
    echo "Failed to bind parameters for SQL statement: " . $error_message['message'];
    exit();
} else {
    echo "Parameters bound successfully<br>";
}

// Execute the statement
$result = '';
if (!oci_execute($statement,OCI_COMMIT_ON_SUCCESS)) {
    $error_message = oci_error($statement);
    echo "Failed to execute SQL statement: " . $error_message['message'];
    exit();
} else {
    echo "SQL statement executed successfully<br>";
}
echo "Employee salary: $result"; // Output the result

// Free statement and close connection
oci_free_statement($statement);
oci_close($connection);

 

Link to comment
Share on other sites

Welcome to phpfreaks.  Please in the future use a code block.  I fixed your post this time.

I haven't done anything with Oracle and PHP in quite a while, but my first thought would be to have you try providing the type constant parameters.  Your $p_employee_id is an integer, yet you are providing a length pertinent to a varchar, which doesn't make sense.  

I'd experiment with this:

 

oci_bind_by_name($statement, ':p_employee_id', $p_employee_id, -1);
oci_bind_by_name($statement, ':result', $result, 255); // Assuming the result length is 255 characters

See what happens.

If that doesn't fix it, you also might try this:

oci_bind_by_name($statement, ':p_employee_id', $p_employee_id, -1, SQLT_INT);
oci_bind_by_name($statement, ':result', $result, 255, SQLT_CHR); // Assuming the result length is 255 characters

Or even:

oci_bind_by_name($statement, ':p_employee_id', $p_employee_id, -1, SQLT_INT);
oci_bind_by_name($statement, ':result', $result, -1, SQLT_CHR); 

 

Also the pl/sql code for the sproc might be helpful/necessary in figuring out what might be happening.

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.