Oracle PL/SQL PLS-00103、PLS-00201报错排查咨询

报错1：PLS-00103 遇到符号"&"，预期应为符号% #

报错触发场景 #

执行如下代码时报错：

DECLARE
invalid_product EXCEPTION;
BEGIN 
UPDATE product
SET name = '&product_description'
WHERE productid = 12;
IF SQL&NOTFOUND THEN
RAISE invalid_product;
END IF;
COMMIT;
EXCEPTION 
 WHEN invalid_product THEN
 DBMS_OUTPUT.PUT_LINE('invalid product number.');
END;

排查思路 #

• 首先定位代码中所有&符号的位置，错误提示明确是&符号使用不符合语法规范
• Oracle内置游标属性的固定语法为SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT，属性和SQL前缀之间必须用%连接，你代码中误写为&，如果是从HTML页面复制的代码，还要注意&是&的转义字符，要先还原为原始符号再校验语法。

解决方法 #

将IF SQL&NOTFOUND THEN修改为IF SQL%NOTFOUND THEN即可，修改后代码如下：

DECLARE
invalid_product EXCEPTION;
BEGIN 
UPDATE product
SET name = '&product_description' -- 此处&为Oracle替换变量前缀，客户端执行时会提示输入值，属于正常用法
WHERE productid = 12;
IF SQL%NOTFOUND THEN
RAISE invalid_product;
END IF;
COMMIT;
EXCEPTION 
 WHEN invalid_product THEN
 DBMS_OUTPUT.PUT_LINE('invalid product number.');
END;

报错2：PLS-00201 标识符'CUSTOMER.ID'必须声明 #

报错触发场景 #

执行如下代码时报错：

DECLARE
  c_id customer.id%type:= -8;
  c_name customer.Name%type;
  c_addr customer.address%type;
  invalid_id EXCEPTION;
BEGIN
 IF c_id <0 THEN
  RAISE invalid_id;
  ELSE
  SELECT name,address INTO c_name,c_addr
  FROM customer
  WHERE id = c_id;
  DBMS_OUTPUT.PUT_LINE('name' || c_name);
  DBMS_OUTPUT.PUT_LINE('addresS:' || c_addr);
  END IF;
EXCEPTION
  WHEN invalid_id THEN
   DBMS_OUTPUT.PUT_LINE('invalid customer id!');
  WHEN no_data_found THEN
   DBMS_OUTPUT.PUT_LINE('no such customer');
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR');
END;   

排查思路 #

该报错本质是Oracle无法识别customer.id这个标识符，按以下优先级排查：

• 先单独执行测试查询SELECT id FROM customer WHERE ROWNUM = 1;，看是否能正常返回结果
  • 如果提示表或视图不存在：首先检查表名是否拼写错误，Oracle默认不区分大小写，如果你建表时用双引号指定了小写表名/字段名，访问时也必须加双引号；其次确认表是否在当前登录用户的schema下，如果在其他用户下，需要加schema前缀（比如scott.customer）；最后确认当前用户是否有该表的查询权限
  • 如果测试查询提示标识符id无效：检查字段名是否拼写错误，同样注意大小写问题

解决方法 #

根据排查结果对应处理：

• 表归属其他schema：在表名前加schema前缀，比如c_id scott.customer.id%type
• 无查询权限：联系DBA执行授权语句GRANT SELECT ON 所属用户.customer TO 当前登录用户;
• 建表时指定了大小写：访问时加双引号匹配大小写，比如c_id "customer"."id"%type

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