关于oracle PL/SQL存储过程 PLS-00905 object is invalid，statement ignored问题的解决

昨天在学习oracle存储过程的时候，写了一个存储过程的demo，语句是这样的：

 

CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR2(20))
AS
    psssal TESTDELETE.TESTID%TYPE;
BEGIN
    SELECT TESTID INTO psssal FROM TESTDELETE WHERE TESTNAME=PNAME;
    UPDATE TESTDELETE SET TESTID=(TESTID+10000) WHERE TESTNAME=PNAME;
    DBMS_OUTPUT.PUT_LINE('The original salary'||psssal||'    After the raise'||(psssal+1000));
end;
/

想法是通过表的varchar(20)类型字段找到number类型字段，然后更改number类型的字段。表结构如下：

create table TESTDELETE
(
    TESTID   NUMBER,
    TESTNAME VARCHAR2(20)
)

将存储过程调用，出现结果如下：

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR2(20))
  2  AS
  3      psssal TESTDELETE.TESTID%TYPE;
  4  BEGIN
  5      SELECT TESTID INTO psssal FROM TESTDELETE WHERE TESTNAME=PNAME;
    UPDATE TESTDELETE SET TESTID=(TESTID+10000) WHERE TESTNAME=PNAME;
    DBMS_OUTPUT.PUT_LINE('The original salary'||psssal||'    After the raise'||(psssal+1000));
  8  end;
  9  /

Warning: Procedure created with compilation errors.

SET SERVEROUTPUT ON;
BEGIN
    RAISESALARY('name2091');
    COMMIT;
END;
SQL>   2    3    4    5  /
    RAISESALARY('name2091');
    *
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00905: object TEST.RAISESALARY is invalid
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored


SQL> 

出现错误 ：该存储过程无效。

what？明明刚刚建立的存储过程啊。然后我就翻到上面创建完之后的一句话，Warning: Procedure created with compilation errors. 翻译过来应该是：创建的过程带有编译的错误。

也就是说创建存储过程有错误，那么OK，找找存储过程看看哪错了。

emmmm，回头看了半天，就这么几行，死活没看出到底哪出问题了，没办法，baidu，google。

后来在StackOverFlow看到了这个，原文链接如下：

https://stackoverflow.com/questions/48497140/oracle-sql-stored-procedure-object-invalid

楼主在里面发现了这么一句话：

You can’t give a size or precision restriction for the data type of a formal parameter to a function or procedure, so NUMBER(10,0) should just be NUMBER;

也就是说，你不能给函数和存储过程的参数指定数据的大小或者精度。OK，回头看看我这个参数，懂了，varchar2(20)是明显的给参数的类型指定精度了。需要改成varchar2这种类型。

或者直接写   表名.字段%TYPE 也是可以的（亲测） 。

更改完之后运行如下：

 

CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR)
  2  AS
  3      psssal TESTDELETE.TESTID%TYPE;
  4  BEGIN
  5      SELECT TESTID INTO psssal FROM TESTDELETE WHERE TESTNAME=PNAME;
    UPDATE TESTDELETE SET TESTID=(TESTID+10000) WHERE TESTNAME=PNAME;
    DBMS_OUTPUT.PUT_LINE('The original salary'||psssal||'    After the raise'||(psssal+1000));
  8  end;
  9  /

Procedure created.

BEGIN
    RAISESALARY('name2091');
  3      COMMIT;
  4  END;
  5  /
The original salary2091    After the raise3091

PL/SQL procedure successfully completed.

SQL> 

 

 

改完之后明确看到已经没有warning了，出现的是   Procedure created.

运行成功！问题解决。