Oracle function returning custom type is not working

Question

I have an issue with my code in PL/SQL.

I'm creating a function that should return custom type defined as:

create TYPE rc_rman_guaranteed_backups_table_type AS TABLE OF RCATPROD.rc_rman_guaranteed_backups_type;

create TYPE rc_rman_guaranteed_backups_type AS OBJECT (
  REG_DB_UNIQUE_NAME VARCHAR2(32),
  MIN_GUARANTEED_DAYS DATE,
  MIN_GUARANTEED_DATE DATE
);

Function DDL is:

create or replace FUNCTION RC_RMAN_GUARANTEED_BACKUPS_func RETURN RCATPROD.rc_rman_guaranteed_backups_table_type PIPELINED IS
query_string VARCHAR2(32000);
result_cursor SYS_REFCURSOR;
CURSOR query_usr IS select username from dba_users where (username like 'RMAN%TSM' or username like 'RMAN%CV') and account_status = 'OPEN' order by 1;
i number;
REG_DB_UNIQUE_NAME VARCHAR2(32);
MIN_GUARANTEED_DAYS DATE;
MIN_GUARANTEED_DATE DATE;
BEGIN
i:=0;
query_string := '';
FOR usr IN query_usr LOOP
REG_DB_UNIQUE_NAME:=substr(usr.username, 6, 8);
IF i=0 THEN
query_string := 'select '''||REG_DB_UNIQUE_NAME||''', trunc(sysdate-min(completion_time)), min(completion_time) from '||usr.username||'.bs where incr_level=0 ';
i:=i+1;
ELSE
query_string := query_string||'union all select '''||REG_DB_UNIQUE_NAME||''', trunc(sysdate-min(completion_time)), min(completion_time) from '||usr.username||'.bs where incr_level=0 ';
END IF;
END LOOP;
query_string := query_string||'order by 1';
OPEN result_cursor FOR query_string;
LOOP
FETCH result_cursor INTO REG_DB_UNIQUE_NAME,MIN_GUARANTEED_DAYS,MIN_GUARANTEED_DATE;
EXIT WHEN result_cursor%NOTFOUND;
PIPE ROW (RCATPROD.rc_rman_guaranteed_backups_type(REG_DB_UNIQUE_NAME,MIN_GUARANTEED_DAYS,MIN_GUARANTEED_DATE));
END LOOP;
CLOSE result_cursor;
RETURN;
END;

Everything is compiled properly but, when I try to select function result, I got below error:

select * from table(RCATPROD.RC_RMAN_GUARANTEED_BACKUPS_func);

ORA-00942: tabella o vista inesistente ORA-06512: a "RCATPROD.RC_RMAN_GUARANTEED_BACKUPS_FUNC", line 22 00942. 00000 - "table or view does not exist" *Cause:
*Action:

I tried to put in output query_string and result is correct, so I think the issue is within OPEN result_cursor ... block.

This is very strange because I have other functions defined same way and they are ok.

Can you please help understand where is the point?

Thank you in advance.

Answer 1

Assuming you have privileges to see all the tables then:

1. In Oracle, if you use unquoted identifiers then Oracle will implicitly convert the identifier to upper-case. Therefore, if you read a value from dba_users that is a lower- or mixed-case identifier and then use it unquoted in a SELECT query then it will be implicitly converted to upper-case and the user with the incorrect case will not be found (or worse, you will find an incorrect user).

2. Not relevant to your problem but still a bug with your code, if there are multiple rows being returned from your function and you use the query:

   select *
   from   table(RCATPROD.RC_RMAN_GUARANTEED_BACKUPS_func)
   where  ROWNUM = 1;
   

   Then the function will be terminated as soon as sufficient rows have been read (i.e. without reading all the values from the cursor) and the second cursor will not be closed. To handle this you need to catch the NO_DATA_NEEDED exception and explicitly close the cursor.

With quoted identifiers and catching the NO_DATA_NEEDED exception:

create or replace FUNCTION RC_RMAN_GUARANTEED_BACKUPS_func
  RETURN RCATPROD.rc_rman_guaranteed_backups_table_type PIPELINED
IS
  query_string VARCHAR2(32000) := NULL;
  CURSOR query_usr IS
    select username
    from   dba_users
    where  (username like 'RMAN%TSM' or username like 'RMAN%CV')
    and    account_status = 'OPEN'
    order by 1;
    
  result_cursor SYS_REFCURSOR;
  i number := 0;
  REG_DB_UNIQUE_NAME VARCHAR2(32);
  MIN_GUARANTEED_DAYS DATE;
  MIN_GUARANTEED_DATE DATE;
BEGIN
  FOR usr IN query_usr LOOP
    REG_DB_UNIQUE_NAME:=substr(usr.username, 6, 8);
    IF i=0 THEN
      query_string := 'select '''||REG_DB_UNIQUE_NAME||''', trunc(sysdate-min(completion_time)), min(completion_time) from "'||usr.username||'".bs where incr_level=0 ';
      i:=i+1;
    ELSE
      query_string := query_string
                   || 'union all select '''||REG_DB_UNIQUE_NAME||''', trunc(sysdate-min(completion_time)), min(completion_time) from "'||usr.username||'".bs where incr_level=0 ';
    END IF;
  END LOOP;

  IF query_string IS NOT NULL THEN
    query_string := query_string||'order by 1';

    OPEN result_cursor FOR query_string;
    LOOP
      FETCH result_cursor INTO REG_DB_UNIQUE_NAME,MIN_GUARANTEED_DAYS,MIN_GUARANTEED_DATE;
      EXIT WHEN result_cursor%NOTFOUND;

      PIPE ROW (RCATPROD.rc_rman_guaranteed_backups_type(REG_DB_UNIQUE_NAME,MIN_GUARANTEED_DAYS,MIN_GUARANTEED_DATE));
    END LOOP;
    CLOSE result_cursor;
  END IF;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    CLOSE result_cursor;
END;
/

Answer 2

Oracle complains about table (or view) that doesn't exist.

There are two select statements in that code:

• one fetches data from dba_users - do you have privileges to do that? Note that this is a function, a named PL/SQL procedure; if you acquired privileges on dba_users via role, that won't work - you'll have to grant them directly

• another one presumes that there's bs table in each schema whose username is being fetched via cursor

  • question 1: are you sure that such a table exists?
  • question 2: do you have privileges to access these tables from your current user?

  Answers should help you decide what to do.

According to error line number (22), I'd put my bet on problem(s) with the bs table.