Can we use named parameters in Procedures?

[ultrablue]

Summary of problem or feature request

Hi:

This is a question (and a feature request if what I'm asking doesn't exist).

I have an Oracle Procedure that uses named parameters: p_update(first_thing, second_thing, third_thing) It's executed like this from Oracle: execute p_update(first_thing => 'Number 1', second_thing => 2, third_thing => 'three');

My php looks like this:

$bindings = ['first_thing' => 'Number 1', 'second_thing' => 2, 'third_thing' => 'three');
DB::executeProcedure($procedure, $bindings);

And laravel-oci8 generates this:

p_update(:first_thing,:second_thing,:third_thing); end;
Bindings      : ['Number 1',2,'three']"}

I can use positional parameters, but the actual Procedure has around 70 arguments, and named parameters are much more convenient. Is there a way to let executeProcedure() use named arguments?

Thank you!

Code snippet of problem

System details

• Operating System: Laradock
• PHP Version: 7.2.4
• Laravel Version: 5.6.38
• Laravel-OCI8 Version: 5.6.4 (According to CHANGELOG)

[yajra]

I am not yet familiar with named parameters but based on your example, maybe using raw sql may do the trick?

$bindings = [
  DB::raw("first_thing => 'Number 1'"),
  DB::raw("second_thing => 2"), 
  DB::raw("third_thing => 'three'")
];
DB::executeProcedure($procedure, $bindings);

[ultrablue]

Thanks, Arjay! Great idea. I'll do my best to post back here with results.

[stirlingcrow]

I experimented with the example and did the following... Unfortunately nothing seemed to work:

$procedureName = 'baninst1.sb_section.p_update';
 
//Got: oci_bind_by_name(): Invalid variable used for bind
            $bindings = [
                DB::raw('p_term_code => 201880'),
                DB::raw('p_crn => 10200'),
                DB::raw('p_max_enrl => 8')
                ];
 
//Got: oci_bind_by_name(): Invalid variable used for bind
            $bindings = [
                DB::raw('p_term_code','201880'),
                DB::raw('p_crn','10200'),
                DB::raw('p_max_enrl','8')
            ];
 
//Got: oci_bind_by_name(): Invalid variable used for bind
            $bindings = [
                DB::raw("p_term_code => '201880'"),
                DB::raw("p_crn => '10200'"),
                DB::raw("p_max_enrl => '8'")
            ];
 
$result = DB::executeProcedure($procedureName, $bindings);

I also tried the following:

//Got ORA-00900: invalid SQL statement
DB::statement("execute baninst1.sb_section.p_update(p_term_code => '201810', p_crn => '10200', p_max_enrl => 22)");
 
//Got: ORA-00900: invalid SQL statement
DB::statement("baninst1.sb_section.p_update(p_term_code => '201810', p_crn => '10200', p_max_enrl => 22)");
 
//Got: ORA-00900: invalid SQL statement
$results = DB::select( DB::raw("baninst1.sb_section.p_update(p_term_code => :p_term_code, p_crn => :p_crn, p_max_enrl => :p_max_enrl)"
            ),
                array(
                    'p_term_code' => $ssbsect_term_code,
                    'p_crn' => $ssbsect_crn,
                    'p_max_enrl' => $update_ssbsect_max_enrl
                ));

...and that didn't work either.

I did get the following to work in PDO:

$pdo = DB::getPdo();
$stmt = $pdo->prepare("begin baninst1.sb_section.p_update(p_term_code => :p_term_code, p_crn => :p_crn, p_max_enrl => :p_max_enrl); end;");
$stmt->bindParam(':p_term_code',$ssbsect_term_code, \PDO::ATTR_EMULATE_PREPARES);
$stmt->bindParam(':p_crn',$ssbsect_crn, \PDO::ATTR_EMULATE_PREPARES);
$stmt->bindParam(':p_max_enrl',$update_ssbsect_max_enrl, \PDO::ATTR_EMULATE_PREPARES);
$result = $stmt->execute();

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.