ORA-00942: 表或视图不存在 dba_objects

来源：互联网发布：自行车风火轮diy软件编辑：程序博客网时间：2024/05/16 17:44

create type date_obj as object (
    month varchar2(20),
    sun char(2),
    mon char(2),
    tue char(2),
    wed char(2),
    thu char(2),
    fri char(2),
    sat char(2)
);
/
create type date_obj_tab as table of date_obj;
/

create or replace function get_date_str(in_date in date default sysdate)
return date_obj_tab
pipelined is
date_o date_obj := date_obj(to_char(in_date, 'Month'),
                              1,
                              1,
                              1,
                              1,
                              1,
                              1,
                              1);
cursor c1 is
    select sum(case
                 when date_str = 'sun' then
                  date_d
               end) sun,
           sum(case
                 when date_str = 'mon' then
                  date_d
               end) mon,
           sum(case
                 when date_str = 'tue' then
                  date_d
               end) tue,
           sum(case
                 when date_str = 'wed' then
                  date_d
               end) wed,
           sum(case
                 when date_str = 'thu' then
                  date_d
               end) thu,
           sum(case
                 when date_str = 'fri' then
                  date_d
               end) fri,
           sum(case
                 when date_str = 'sat' then
                  date_d
               end) sat
      from (select to_number(to_char(date_d, 'dd')) date_d,
                   to_char(date_d, 'dy') date_str,
                   to_number(to_char(date_d, 'ddd')) -
                   to_number(to_char(date_d, 'd')) date_w
              from (select trunc(date_d) + rownum - 1 date_d
                      from DBA_OBJECTS a,
                           (select trunc(in_date, 'mm') date_d from dual) b
                     where rownum <=
                           to_number(to_char(last_day(date_d), 'dd'))))
     group by date_w;
begin
for rs in c1 loop
    date_o.sun := rs.sun;
    date_o.mon := rs.mon;
    date_o.tue := rs.tue;
    date_o.wed := rs.wed;
    date_o.thu := rs.thu;
    date_o.fri := rs.fri;
    date_o.sat := rs.sat;
    pipe row(date_o);
end loop;
return;
end;

Compilation errors for FUNCTION LANDUSER.GET_DATE_STR

Error: PL/SQL: ORA-00942: 表或视图不存在
Line: 46
Text: from dba_objects a,

解决办法

connect sys/ as sysdba;

grant select on DBA_OBJECTS to landuser;

select * from table(get_date_str(sysdate-10));

MONTH SU MO TU WE TH FR SA
-------------------- -- -- -- -- -- -- --
March 1 2 3 4 5
March 6 7 8 9 10 11 12
March 13 14 15 16 17 18 19
March 20 21 22 23 24 25 26
March 27 28 29 30 31