DEV Community

fernando gonzalez
fernando gonzalez

Posted on

Help Needed: Calling Stored Function from Laravel Controller

Hi everyone, first of all sorry for the extension but I think that the only way you could understand is by giving you the whole picture.
I hope someone can help me with this issue I'm facing. Here's the context:

  • In my current job, I'm working on a large Laravel project. I'm using the MVC paradigm that needs to display data obtained by calling a stored function in a PostgreSQL database using Oracle SQL Developer.
  • Below, I'll share the code from the controller where I call the function and the code for the function itself. The problem is that when I call the function directly in the database, it returns the correct amount the user owes. However, when I try to pass the same parameters from the Laravel project's controller, it always returns 0. This means that the function in the database does not validate everything correctly to return the amount owed by the user.
  • I should mention that I'm using a syntax to call the function that works correctly for another function in the database. There are differences between the database functions, as the one causing the error can receive a default parameter. I suspect that the error might be related to this, but I'd like to understand how the query is generated from Laravel or how to obtain the correct value. I hope my explanation is clear. Any suggestions or help would be greatly appreciated.

Here is the code for the function call that returns the correct value (and which I used as an example for the one causing the error):
`public function tieneTitulo(Request $request){
$ope = $request->operatoria;
$barrio = $request->barrio;
$adju = $request->adju;

    $query = "SELECT usua400.fun_tiene_titulo(:ope, :barrio, :adju, 'T') as respuesta from dual";
    $resultados = DB::select(DB::raw($query), ['ope' => $ope, 'barrio' => $barrio, 'adju' => $adju]);

    //dd($resultados);
    if (empty($resultados)) {
        return response()->json(['message' => 'Sin datos.']);
    } else {
        return response()->json($resultados[0]);
    }
}
Enter fullscreen mode Exit fullscreen mode

`

Here is the code for the function call that always returns 0 (and which I'm trying to debug):
`public function consultarAdeuda(Request $request) {
$ope = $request->operatoria;
$barrio = $request->barrio;
$adju = $request->adju;
$fecha = Carbon::createFromFormat('d/m/y', '15/07/24')->format('d-m-y');

    try {
        $result = DB::select('SELECT iprodha.fun_adeuda_mont(:ope, :barrio, :adju, :fecha) as respuesta from dual', [
            'ope' => $ope,
            'barrio' => $barrio,
            'adju' => $adju,
            'fecha' => $fecha,
        ]);

        if ($result === null){
            return response()->json(['respuesta' => 'Sin datos.']);
        } else {
            return response()->json(['respuesta' => $result[0]->respuesta]);
        }
    } catch (\Exception $e) {
        return response()->json(['message' => 'Ocurrió un error al ejecutar la consulta: .'], $e->getMessage());
    }
}
Enter fullscreen mode Exit fullscreen mode

`

The code of routes:
Route::get('juridicos/juicios/consultarAdeuda', [JuiciosController::class, 'consultarAdeuda'])->name('juridicos.juicios.consultarAdeuda');

Here is the code for the Oracle database function in Oracle SQL Developer:
link

Top comments (0)