Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

jgravois's avatar

Oracle Update Query Not Responding

We have an Oracle based legacy Operations Software that we are interfacing with to set some custom local information.

I have this method

public function update_pricing($pn)
    {
        try
           {
               $pn = preg_replace('/[^\w\d]/', '', $pn);

               $part = DB::connection('oracle')->select('SELECT PNM.PNM_AUTO_KEY, PRN.CATEGORY_CODE FROM PARTS_MASTER PNM LEFT OUTER JOIN PART_RANKING_NUMBERS PRN ON PRN.PRN_AUTO_KEY = PNM.PRN_AUTO_KEY WHERE PNM.PN_STRIPPED = ?', [$pn]);

               if(count($part) == 0) {
                   return response()->json(['result' => 'error', 'error' => 'Not found.', 'message' => 'PN not found'], 404);
               } // end if

               $query = "UPDATE PARTS_MASTER SET PRN_AUTO_KEY = " . $request->input('rank') . ", IC_UDF_002 = " . $request->input('price') . ", IC_UDF_001 = '" . Carbon::now()->format('Y-m-d') . "' WHERE PNM_AUTO_KEY = '" . $part[0]->pnm_auto_key . "'";

               //$res = DB::connection('oracle')->update($query);

               return response()->json(['result' => 'success', 'query' => $query]);
           }
        catch(\Exception $e)
           {
               return response()->json(['result' => 'error', 'error' => 'Database error.', 'message' => $e->getMessage()], 500);
           } // End try...catch
    } // end function

You can see I have this line //$res = DB::connection('oracle')->update($query); commented out because when not commented out the endpoint just "spins" without error until it times out. If I take the returned query and paste it into DataGrip, the record is updated in 93ms.

UPDATE PARTS_MASTER SET PRN_AUTO_KEY = 2, IC_UDF_002 = 85000, IC_UDF_001 = '2020-07-06' WHERE PNM_AUTO_KEY = '267105'

I have tried everything I could think of to change this behavior. I am hoping fresh eyes will see something I am missing.

0 likes
1 reply
Tray2's avatar
Tray2
Best Answer
Level 73

My guess is that something else is locking that record.

I suggest you try doing a select from update no wait skip locked then it will skip the update otherwise it will hang until the update is made.

http://www.dba-oracle.com/t_select_update_skip_locked.htm

You can run this to see what is locking your table

select * from DBA_DDL_LOCKS where name like :your_table;

Please or to participate in this conversation.