ORDS调用update_player存储过程报ORA-06550 PLS-00306错误求助

问题现象 #

调用PUT类型的update_player ORDS接口时返回如下错误：

"cause": "An error occurred when evaluating the SQL statement associated with this resource. SQL Error Code 6550, Error Message: ORA-06550: line 2, column 28:\nPLS-00306: wrong number or types of arguments in call to 'UPDATE_PLAYER'\nORA-06550: line 2, column 28:\nPL/SQL: Statement ignored\n"

初步核查报错指向的存储过程第2行参数定义p_playerid IN players.playerid%TYPE，未发现明显语法问题。同方案下部署的POST接口可正常运行，已测试将日期参数格式调整为8-7-2022 11:00，问题未解决。

现有相关代码 #

update_player存储过程定义 #

PROCEDURE update_player (
  p_playerid IN players.playerid%TYPE,
  p_playername IN players.playername%TYPE,
  p_registrationDate IN VARCHAR2,
  p_dateLastmatch IN VARCHAR2,
  p_previousMatchDate IN VARCHAR2,
  p_nrofdayssincelastactivity IN players.nrofdayssincelastactivity%TYPE,
  p_lastmatchid IN players.lastmatchid%TYPE,
  p_diffandround IN players.diffandround%TYPE,
  p_nrsingleplayermatches IN players.nrsingleplayermatches%TYPE,
  p_nrmultiplayermatches IN players.nrmultiplayermatches%TYPE,
  p_nrmatches IN players.nrmatches%TYPE,
  p_nrquitmatches IN players.nrquitmatches%TYPE,
  p_nrflips IN players.nrflips%TYPE,
  p_nrclicks IN players.nrclicks%TYPE,
  p_totmatchtimesec IN players.totmatchtimesec%TYPE,
  p_score IN players.score%TYPE,
  p_nrwonmatches IN players.nrwonmatches%TYPE,
  p_nrlostmatches IN players.nrlostmatches%TYPE,
  p_nrevenmatches IN players.nrevenmatches%TYPE,
  p_templnrmatches IN players.templnrmatches%TYPE,
  p_templmatchtimesec IN players.templmatchtimesec%TYPE,
  p_isqualified IN players.isqualified%TYPE
)
AS
BEGIN
UPDATE PLAYERS SET
  playername = p_playername,
  registrationdate = TO_DATE(p_registrationdate, 'DD-MM-YYYY HH:MI'),
  datelastmatch = TO_DATE(p_datelastmatch, 'DD-MM-YYYY HH:MI'),
  previousmatchdate = TO_DATE(p_previousmatchdate, 'DD-MM-YYYY HH:MI'),
  nrofdayssincelastactivity = p_nrofdayssincelastactivity,
  lastmatchid = p_lastmatchid,
  diffandround = p_diffandround,
  nrsingleplayermatches = p_nrsingleplayermatches,
  nrmultiplayermatches = p_nrmultiplayermatches,
  nrmatches = p_nrmatches,
  nrquitmatches = p_nrquitmatches,
  nrflips = p_nrflips,
  nrclicks = p_nrclicks,
  totmatchtimesec = p_totmatchtimesec,
  score = p_score,
  nrwonmatches = p_nrwonmatches,
  nrlostmatches = p_nrlostmatches,
  nrevenmatches = p_nrevenmatches,
  templnrmatches = p_templnrmatches,
  templmatchtimesec = p_templmatchtimesec,
  isqualified = p_isqualified
WHERE playerid = p_playerid;
EXCEPTION
WHEN OTHERS THEN
HTP.print(SQLERRM);
END;

ORDS处理器绑定SQL #

BEGIN
update_player(
   playerid                  => :playerid,
   playername                => :playername,
   registrationdate          => :registrationdate,
   datelastmatch             => :datelastmatch,
   previousmatchdate         => :previousmatchdate,
   nrofdayssincelastactivity => :nrofdayssincelastactivity,
   lastmatchid               => :lastmatchid,
   diffandround              => :diffandround,
   nrsingleplayermatches     => :nrsingleplayermatches,
   nrmultiplayermatches      => :nrmultiplayermatches,
   nrmatches                 => :nrmatches,
   nrquitmatches             => :nrquitmatches,
   nrflips                   => :nrflips,
   nrclicks                  => :nrclicks,
   totmatchtimesec           => :totmatchtimesec,
   score                     => :score,
   nrwonmatches              => :nrwonmatches,
   nrlostmatches             => :nrlostmatches,
   nrevenmatches             => :nrevenmatches,
   templnrmatches            => :templnrmatches,
   templmatchtimesec         => :templmatchtimesec,
   isqualified               => :isqualified);
 END;

传入JSON参数 #

playerRecord.playerid = "583CA078CA47D87";   
playerRecord.playername = "Maria";
playerRecord.registrationdate = "7/1/2022 10:33";
playerRecord.datelastmatch = "9/7/2022 11:00";
playerRecord.previousmatchdate = "8/7/2022 11:00";
playerRecord.nrofdayssincelastactivity = 1;
playerRecord.lastmatchid = "LASTMATCHID";
playerRecord.diffandround = "D1R1";
playerRecord.nrsingleplayermatches = 9;
playerRecord.nrmultiplayermatches = 33;
playerRecord.nrmatches = 136;
playerRecord.nrquitmatches = 0;
playerRecord.nrflips = 2022;
playerRecord.nrclicks = 4444;
playerRecord.totmatchtimesec = 9999;
playerRecord.score = 5555;
playerRecord.nrwonmatches = 20;
playerRecord.nrlostmatches = 10;
playerRecord.nrevenmatches = 3;
playerRecord.templnrmatches = 0;
playerRecord.templmatchtimesec = 0;
playerRecord.isqualified = 0;

底层PLAYERS表结构 #

CREATE TABLE PLAYERS (
  playerID VARCHAR(20) NOT NULL PRIMARY KEY,
  playerName VARCHAR2(20),
  registrationDate DATE,
  dateLastMatch DATE,
  previousMatchDate DATE,
  nrOfDaysSinceLastActivity INT,
  lastMatchId VARCHAR(12),
  diffAndRound VARCHAR2(4),
  nrSingleplayerMatches INT,
  nrMultiplayerMatches INT,
  nrMatches INT,
  nrQuitMatches INT,
  nrFlips INT,
  nrClicks INT,
  totMatchTimeSec FLOAT,
  score INT,
  nrWonMatches INT,
  nrLostMatches INT,
  nrEvenMatches INT,
  templNrMatches INT,
  templMatchTimeSec INT,
  isQualified INT
);

错误根因 #

PLS-00306错误的直接原因是ORDS处理器中调用存储过程时，命名参数与存储过程定义的形参名不匹配：

• 存储过程定义的所有形参均带p_前缀，例如p_playerid、p_playername
• 但ORDS处理器的调用语句中，关联绑定变量时写的参数名完全缺失p_前缀，例如playerid => :playerid，Oracle解析时无法识别这些不存在的形参，直接判定调用参数数量/类型不匹配。

此外还存在两个后续会触发异常的隐患：

• 日期格式不匹配：存储过程中TO_DATE使用的格式掩码为DD-MM-YYYY HH:MI（横杠分隔、12小时制分钟），但传入的日期参数为斜杠分隔格式，参数名修复后会触发日期转换错误。
• 异常捕获不规范：存储过程中自定义异常打印逻辑，会覆盖ORDS原生错误返回格式，容易吞掉核心报错信息。

修复方案 #

第一步：修正ORDS处理器的存储过程调用参数名 #

将所有调用参数补上p_前缀，和存储过程形参名完全一致，修正后的SQL如下：

BEGIN
update_player(
   p_playerid                  => :playerid,
   p_playername                => :playername,
   p_registrationdate          => :registrationdate,
   p_datelastmatch             => :datelastmatch,
   p_previousmatchdate         => :previousmatchdate,
   p_nrofdayssincelastactivity => :nrofdayssincelastactivity,
   p_lastmatchid               => :lastmatchid,
   p_diffandround              => :diffandround,
   p_nrsingleplayermatches     => :nrsingleplayermatches,
   p_nrmultiplayermatches      => :nrmultiplayermatches,
   p_nrmatches                 => :nrmatches,
   p_nrquitmatches             => :nrquitmatches,
   p_nrflips                   => :nrflips,
   p_nrclicks                  => :nrclicks,
   p_totmatchtimesec           => :totmatchtimesec,
   p_score                     => :score,
   p_nrwonmatches              => :nrwonmatches,
   p_nrlostmatches             => :nrlostmatches,
   p_nrevenmatches             => :nrevenmatches,
   p_templnrmatches            => :templnrmatches,
   p_templmatchtimesec         => :templmatchtimesec,
   p_isqualified               => :isqualified);
 END;

第二步：统一日期格式 #

二选一即可：

• 方案A：修改传入的日期参数格式，和存储过程的格式掩码完全匹配，例如registrationdate传'01-07-2022 10:33'（注意日、月占两位，不足补0，横杠分隔）
• 方案B：修改存储过程中的TO_DATE格式掩码，适配实际传入的日期格式，例如如果传入的是M/D/YYYY HH24:MI（月/日/年 24小时制），则把转换语句改为：

  registrationdate = TO_DATE(p_registrationdate, 'MM/DD/YYYY HH24:MI'),
  datelastmatch = TO_DATE(p_datelastmatch, 'MM/DD/YYYY HH24:MI'),
  previousmatchdate = TO_DATE(p_previousmatchdate, 'MM/DD/YYYY HH24:MI'),
  

推荐使用方案B，24小时制格式可以避免上下午时间识别错误。

可选优化 #

建议去掉存储过程中WHEN OTHERS THEN HTP.print(SQLERRM);的异常捕获，让ORDS直接返回原生Oracle错误信息，方便后续排查问题。

内容的提问来源于stack exchange，提问作者PeterK