oracle - plsql ora -00932 : inconsistent datatypes: expected - got -

Question

我有一个包 (DATA_PKG)，其中包含在给定主键值时返回一行数据的函数。我发现了一种情况，其中一个 DATA_PKG 函数在被另一个包 (CALLER_PKG) 中的过程调用时失败。它抛出以下错误:

ora -00932: inconsistent datatypes: expected - got -

当我在包体中使用完全相同的函数编译 CALLER 时，它起作用了。这是我的代码:

CREATE OR REPLACE PACKAGE "DATA_PKG" AS

  FUNCTION data_function
   (primary_i IN table_name.id%type)
   RETURN table_name%ROWTYPE;

END DATA_PKG;

/

CREATE OR REPLACE PACKAGE BODY "DATA_PKG" AS

  FUNCTION data_function
   (primary_i IN table_name.id%type)
   RETURN table_name%ROWTYPE AS
   retval table_name%ROWTYPE;
  BEGIN
    SELECT * INTO retval FROM table_name WHERE id = primary_i;

    RETURN retval;
  END data_function;

END DATA_PKG;

/

CREATE OR REPLACE PACKAGE "CALLER" AS

  PROCEDURE calling_proc
   (key_i IN table_name.id%TYPE);

END CALLER;

/

CREATE OR REPLACE PACKAGE BODY "CALLER" AS

  PROCEDURE calling_proc
   (key_i IN table_name.id%TYPE) AS
   table_rec table_name%rowtype;
  BEGIN
   table_rec := data_pkg.data_function(primary_i=>key_i);
   /*
     do something with the returned record
   */
  END calling_proc;

END CALLER;

当执行 caller.calling_proc 时，此代码以上述方式失败并返回 ora -00932。

以下代码有效:

CREATE OR REPLACE PACKAGE BODY "CALLER" AS

  FUNCTION data_function
   (primary_i IN table_name.id%type)
   RETURN table_name%ROWTYPE AS
   retval table_name%ROWTYPE;
  BEGIN
    SELECT * INTO retval FROM table_name WHERE id = primary_i;

    RETURN retval;
  END data_function;

  PROCEDURE calling_proc
   (key_i IN table_name.id%TYPE) AS
   table_rec table_name%rowtype;
  BEGIN
   table_rec := data_function(primary_i=>key_i);
   /*
     do something with the returned record
   */
  END calling_proc;

END CALLER;

谁能告诉我为什么这适用于我认为对代码的微不足道的更改？该错误表明数据类型不一致，但我没有在我的“解决方案”中进行任何更改。

有没有人遇到过这种情况？也有人可以解释错误消息中的破折号代表什么吗？我希望看到此处命名的数据类型。

Answer 1

代替这个

table_rec := data_pkg.data_function(primary_i=>key_i);

试试这个(可能有用，尽管它们实际上是一样的)

SELECT data_pkg.data_function(primary_i=>key_i)
  INTO table_rec
  FROM dual;