Error calling a stored procedure PHP and Oracle-CodePudding

I have the following issue. I have a stored procedure in Oracle. When I call the procedure in PHP, it gave me the following error:

Warning: oci_execute(): ORA-01403: No se ha encontrado ning�n dato ORA-06512: en "ESTUDIANTE.P_CONSULTA_LIBRO", l�nea 9 ORA-06512: en l�nea 1 in C:\xampp\htdocs\biblioteca\prueba.php on line 11

The procedure in Oracle is working properly, but I don't know what it's happening. I'm new to coding in PHP.

This is the code in PHP

<?php

require_once 'conexion.php';


$sql = 'BEGIN p_consulta_libro(:variable); END;';
$stmt_id = oci_parse($conexion, $sql);
$variable=0111;
oci_bind_by_name($stmt_id, 'variable', $variable);

$res = oci_execute($stmt_id);

print_r($res, true);

?>

This is the stored procedure in oracle

create or replace procedure p_consulta_libro(idlibro in number )
as

obra varchar2(200);
autor varchar2(50);
valores varchar2(200);

BEGIN
select  L.TITULO, A.NOMBRE_AUTOR into obra, autor 
from LIBRO L, AUTOR A
where L.ID_LIBRO = idlibro  AND A.ID_AUTOR = L.ID_AUTOR ;
valores:=concat(concat(obra,' '),autor);

DBMS_OUTPUT.PUT_LINE('El libro es... '||valores);
end;

I call the procedure using this sentence in Oracle

set serveroutput on
BEGIN 
p_consulta_libro(0111);
END;

CodePudding user response：

I don't know PHP, but - from Oracle-ish point of view, perhaps you'd rather use a function that returns value; if there's nothing to be found, handle that exception and return something else (a message, or even an empty string).

Also, a hint or two:

you don't have to first select values into locally declared variables and then concatenate them
nowadays, it is suggested that we separate joins from conditions (which should be put into the where clause), so - don't comma-separate tables in the from clause and join them in where - properly join them
concatenation - in Oracle - is way simpler if you use the double pipe || concatenation operator, instead of nested concat function calls

Something like this:

create or replace function f_consulta_libro (p_idlibro in number)
  return varchar2
as
  l_valores varchar2(200);
begin
  select l.titulo ||' '|| a.nombre_autor
    into l_valores
    from libro l join autor a on a.id_autor = l.id_autor
    where l.id_libro = p_idlibro;

  return l_valores;
  
exception
  when no_data_found then
    return 'Nothing has been found';
end;
/

CodePudding user response：

The number 0111 gets treated as octal so it is not the number 111.

In your particular example you don't need PL/SQL. Just execute the SQL query directly:

<?php

error_reporting(E_ALL);
ini_set('display_errors', 'On');

$c = oci_connect("hr", "welcome", "localhost/XEPDB1");
if (!$c) {
    $m = oci_error();
    trigger_error('Could not connect to database: '. $m['message'], E_USER_ERROR);
}

// Use a NOWDOC for SQL, see https://blogs.oracle.com/opal/post/php-53-quotnowdocsquot-make-sql-escaping-easier
// Note there is no trailing semi-colon after the SQL
$sql =<<<PHPEND
  select  L.TITULO || ' ' || A.NOMBRE_AUTOR
  from LIBRO L, AUTOR A
  where L.ID_LIBRO = :idlibro  AND A.ID_AUTOR = L.ID_AUTOR
PHPEND;

$s = oci_parse($c, "select * from employees where salary > :sbv");
if (!$s) {
    $m = oci_error($c);
    trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
}

$id = 111;   // Note no leading 0
$r = oci_bind_by_name($s, ':idlibro', $id);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not bind a parameter: '. $m['message'], E_USER_ERROR);
}

$r = oci_execute($s);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
}

echo "<table border='1'>\n";
$ncols = oci_num_fields($s);
echo "<tr>\n";
for ($i = 1; $i <= $ncols;   $i) {
    $colname = oci_field_name($s, $i);
    echo "  <th><b>".htmlspecialchars($colname,ENT_QUOTES|ENT_SUBSTITUTE)."</b></th>\n";
}
echo "</tr>\n";

while (($row = oci_fetch_array($s, OCI_ASSOC OCI_RETURN_NULLS)) != false) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "<td>";
        echo $item!==null?htmlspecialchars($item, ENT_QUOTES|ENT_SUBSTITUTE):"&nbsp;";
        echo "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

?>