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

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.