How to call an oracle procedure on Laravel?

I have an oracle database with Laravel and Yajra package, how can i call an oracle procedure from Laravel 5.4

This is my oracle procedure:

create or replace procedure PRW_GENERA_FACTURA(
            pnro in number, 
            pmsj out VARCHAR2
            ) is
        BEGIN
            pmsj := 'El nro. es '||pnro;

        end PRW_GENERA_FACTURA;

And i tried this on my Controller:
 $val1 = 20; 
 $result = DB::connection('ora_db')->select('exec 
 PRW_GENERA_FACTURA(?)',array($val1));
 return $result;

it gave me this error:

Error Code : 900
Error Message : ORA-00900: invalid SQL statement
Position : 0
Statement : exec PRW_GENERA_FACTURA(:p0)
Bindings : [20]
(SQL: exec PRW_GENERA_FACTURA(20))

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I assume you have to do this in RAW format:

$val1 = 20; 

$result = DB::connection('ora_db')->select(
    DB::raw("exec PRW_GENERA_FACTURA(:val1)"), array ('val1' => $val1)
);

return $result;

this using php into the controller:
$conn = oci_connect('apxjfdev01', 'x....' ,'x...'); 
if (!$conn) { 
 $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), 
 E_USER_ERROR); 
} 
$sql = ' BEGIN PRW_GENERA_FACTURA( :PNRO, :PMSJ ); END; '; $sql = 
oci_parse($conn,$sql); oci_bind_by_name($sql, ":PNRO", $val1, 32); 
oci_bind_by_name($sql, ":PMSJ", $val2, 32); 
$val1 = $id_f; 
oci_execute($sql);


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x