chn262 Posted February 20 Share Posted February 20 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); Quote Link to comment Share on other sites More sharing options...
gizmola Posted February 20 Share Posted February 20 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.