Re: PLS-00222: stored procedure's "select" calls a user-defined function

t turns out the problem pertained to packages, not permissions. I misunderstood the term 'top-level functions' in the Oracle8i SQL Reference. Again, this problem doesn't occur in 9i.

>User functions must be created as top-level functions or declared  with a package
> specification before they can be named within a SQL statement. Create user > functions as top-level functions ....

Before I could call the function from an sql statement, I had to either

1. put the function in a package
2. create a synonym for the function.

The following sql script (sqlplus-friendly) shows what worked and what didn't.

CREATE TABLE RPT_YEAR_MONTH (
  YEAR_MONTH VARCHAR2 (7) NOT NULL   );

create or replace FUNCTION year_month_plain (

	in_date date
          )
         return varchar deterministic
 AS
                /* Declare returned variable */
               return_var VARCHAR(7);
      BEGIN

       select to_char(in_date,'YYYY-MM')
        into  return_var  from dual;

                   /* Return */
                   return return_var;

create or replace package fn_pkg
as
  function year_month_pkg (in_date date)    return varchar;
end;
/

create or replace package body fn_pkg
as

    FUNCTION year_month_pkg (

	in_date date )
         return varchar

                /* Declare returned variable */
               return_var VARCHAR(7);
      BEGIN

       select to_char(in_date,'YYYY-MM')
        into  return_var  from dual;

                   /* Return */
                   return return_var;

      END;

create synonym year_month_syn for year_month_plain;

/* This does not work: results in PLS-00222 */
CREATE OR REPLACE PROCEDURE fails_in_8i (from_date date) AS
  my_var varchar(10);
    BEGIN
  select distinct year_month(from_date) into my_var from rpt_year_month;
end;
/
/* Calling a packaged function works in 8i */
CREATE OR REPLACE PROCEDURE works_in_8i_pkg (from_date date) AS
  my_var varchar(10);
    BEGIN
  select distinct fn_pkg.year_month_pkg(from_date) into my_var from rpt_year_month;
end;
/

/* Calling a function mapped via a synonym works in 8i also */
CREATE OR REPLACE PROCEDURE works_in_8i_syn (from_date date) AS
  my_var varchar(10);
    BEGIN
  select distinct year_month_syn(from_date) into my_var from rpt_year_month;
end;
/
Received on Mon Feb 10 2003 - 17:44:26 CET