Solved

Materialized view calling pl/sql table function

Posted on 2004-10-20
13
1,894 Views
Last Modified: 2012-08-13
Guys

I have an issue.

I have created a materialized view which calls a plsql table function to populate itself.

Here is the code:-

create materialized view data_gmsone.wasd_spells
parallel
nologging
refresh complete next trunc(sysdate) + 1
as
SELECT *
FROM TABLE(control.WASD_CALC_SPELL_9i(CURSOR(      
            select  /*+ use_hash(a) */ nino$1 ,
                  benefit$1,
                  claim_start_date$1,
                  wasd_claim_end_date$1
            from data_gmsone.data_gmsone_cust_ben_dets a
            where s$delflag = 'I'
            and (MAX_CLAIM_DATE$1 is null or MAX_CLAIM_DATE$1 > '28-JUN-1999')
            and BENEFIT$1 in ('JUV', 'IS', 'IB', 'SDA', 'ICA', 'WB', 'BB' , 'PC')
            and CLAIM_START_DATE$1 is not null
            and CUSTOMER_TYPE$1 = 'C'
            order by nino$1, claim_start_date$1)
                  )
      );

It creates fine, but the dbms_job refresh that gets created fails with

control@assist_o> exec dbms_refresh.refresh('"DATA_GMSONE"."WASD_SPELLS"');
BEGIN dbms_refresh.refresh('"DATA_GMSONE"."WASD_SPELLS"'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

I dont understand this. The source tables exist. I have granted execute on the table function to the owner of the materialized view. The materialized view works when I create it, but doesnt work on a refresh.!!! I have tried the refresh under several users including the owner and a dba, and get the same error message for all of them.

Any ideas?
0
Comment
Question by:morphman
  • 4
  • 4
  • 2
13 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12355827
try this(without the quotes) :

exec dbms_refresh.refresh('DATA_GMSONE.WASD_SPELLS');
0
 
LVL 6

Author Comment

by:morphman
ID: 12355867
nope, same error. the refresh job was not added by me, it was added automatically y the creation of the mview.
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12355916
Do you(the user who is executing the procedure) have access rights on the Materialized view? Check that...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:anand_2000v
ID: 12356018
The problem is with the dbmsmv.sql and prvtmv.plb.....copy them from another system which has a slightly different version of oracle.
0
 
LVL 6

Author Comment

by:morphman
ID: 12356067
anand 2000v, please can you explain why the problem is to do with those scripts?

We have just performed an upgrade fmro 8i to 9i on our system. All of our systems are now 9i, so they will all have the same versions of those scripts.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 12366408
sorry for not being online...
I have observed the same problems in Oracle 8i. I just copied those scripts and executed them in sys login and then it started working. your problem might be that you could have done a migration and not an exp/imp method. if so....
$sqlplus
username:sys as sysdba
Password:<type ur password>
SQL> @$ORACLE_HOME/rdbms/admin/dbmsmv.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmv.plb
and then check whether it works
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 12366428
sorry did not go through it properly...
ignore my previous comments

let's suppose that you are executing the refresh command through a user "USER1".

log on as DATA_GMSONE and give the command
grant refresh<or all> on WASD_SPELLS to user1;
grant refresh < or all> on data_gmsone_cust_ben_dets to user1;


if they have been given via a role then you might have this problem
0
 
LVL 6

Accepted Solution

by:
morphman earned 0 total points
ID: 12367215
I am executing the refresh via user DATA_GMSONE who owns the mview. I have other mviews in this user that do refresh (on the same source tables)

I think I have found the problem.

The mview calls user defined types and packages created in user CONTROL. however, how do I grant enough permissions for the refresh job to be able to run and DATA_GMSONE to be able to access the control.package?

For info, the mview created fine in DATA_GMSONE, its only the refresh job failing!!
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 12553439
why me? you should have asked for a refund as no one gave you a solution?
0
 
LVL 6

Author Comment

by:morphman
ID: 12553456
Its not a problem, as I get unlimited points anyway, but as I dont want to abuse that, for future reference, how to you request a refund?

I have left messages on my questions before, and they just sat open, and I dont like open questions.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
help on oracle query 5 52
oracle date format checking 7 26
Oracle create type table from existing table%rowtype ? 6 34
pl/sql parameter is null sometimes 2 13
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question