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

Question

I have a package (DATA_PKG) which contains functions to return a row of data when given a primary key value. I have found a situation where one of the DATA_PKG functions fails when called by a procedure in another package (CALLER_PKG). It throws up the following error:

ora -00932: inconsistent datatypes: expected - got -

When I compile CALLER with the exact same function in the package body it works. Here is my code:

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;

When caller.calling_proc is executed this code fails with ora -00932 in the way described above.

The following code works:

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;

Can anyone tell me why this works with, what I consider to be, a trivial change to the code? The error suggests that the datatypes are inconsistent but I haven't changed any in my 'solution'.

Has this happened to anyone before? Also can anyone explain what the dashes in the error message represent? I would expect to see a datatype named here.

Answer 1

Instead of this

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

Try this (may work, though both of them are literally same)

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