SQL & PL/SQL :: Store Data From Multiple Places In Materialized View?

Apr 14, 2011

I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.So, I created an object type (I've shortened the definitions):

CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));

Then created the table type of the object:

CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;

Then in the stored procedure defined a variable of the table type:

v_mf_record mf_table_type := mf_table_type();

Then I loop and populate the record type:

v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);

When all that is done I try and create the materialized view:

EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');

ORA-00904: "V_MF_RECORD": invalid identifier

Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?

View 1 Replies


ADVERTISEMENT

To Store Data In Materialized View

Apr 14, 2011

I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.

So, I created an object type (I've shortened the definitions):

CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));

Then created the table type of the object:

CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;

Then in the stored procedure defined a variable of the table type:

v_mf_record mf_table_type := mf_table_type();

Then I loop and populate the record type:

v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);

When all that is done I try and create the materialized view:

EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');

ORA-00904: "V_MF_RECORD": invalid identifier

Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?

View 4 Replies View Related

PL/SQL :: How To Use Materialized View And Its Data From A Query

Nov 15, 2012

I have got a simple materialized view question.

I have a view which is taking long time to get back results and this view is being used in various user queries. I noticed that the SQL of the view access some tables, data of them changes rarely. So it makes sense to retrieve that data in a materialized view and somehow re-use it.

My question is how can I use the materialized view and it's data from a query.

View 2 Replies View Related

Replication :: Create Materialized View Without Having Any Data In It

Nov 5, 2008

How I can create a Materialized View without having any data in it.

For e.g.

I create a Materialialized View based on a View.

CREATE MATERIALIZED VIEW test_mv
REFRESH FORCE ON DEMAND
AS
SELECT * FROM test_view

In the above case the data fetched by the view test_view gets stored in the Materialized View test_mv. Suppose I want materialized view test_mv to get created with all the columns of test_view but not the data. I will refresh the materialized view test_mv later for data as and when required.

What shall I do for immidiate formation of materialized view test_mv without data.

View 4 Replies View Related

SQL & PL/SQL :: Create Materialized View For Accessing Data

Feb 29, 2012

1.As we can create materialized view for accessing data from other schema but same database. will it be effective or it will act as a normal view.

2.Will materialized views can be created in Fast mode for the above scenario?

View 4 Replies View Related

PL/SQL :: Materialized View Log Used As Change Data Capture

Jan 31, 2013

I have big source tables to load in a data warehouse. We are in a full Oracle environment. So I need to extract only delta since the last extract.

I need to capture even deleted rows from the source table.

I have tested the following solution:

- declare a materialized view log on the source table
- load the content on this view log in my ODS
- empty this view log
- load my DWH with the captured delta

It is very simple and seems to work perfectly. I am just confused by the fact that nobody seems to have implemented such a solution.

View 11 Replies View Related

Performance Tuning :: How To Refresh Materialized View Data

Jun 12, 2013

I have created a materialized view and also a normal View, which has 3 tables used in both the views, when inserted new records it reflects in a normal view but when i select the materialized view i cant see the updated data.

here is the materialized view i created;

CREATE MATERIALIZED VIEW pct_sales_materialized
BUILD IMMEDIATE REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT A.DEP_NAME,B.EMP_ID,C.EMP_NAME
FROM department_head A,department_child B,emp_detail C
WHERE A.DEP_ID = B.DEP_ID
AND B.EMP_ID = C.EMP_ID

View 2 Replies View Related

Replication :: Materialized View Is Not Refreshing Data Automatically

Feb 14, 2012

I have created a MV on Oracle 10gR2 using dblink. Source database is AS400 DB2.MV script:

CREATE MATERIALIZED VIEW MV_BU
TABLESPACE TB_XXX
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
[code]...

Above query should be refreshing the data every minute but I realized that its not doing it.

View 1 Replies View Related

Networking And Gateways :: Materialized View Data Replication

Jul 9, 2012

I have two servers:

10g server 10.1.4.30
11g server 10.1.4.32

I have a table testing_mview on 10g which do not have any primary key. I have created MV log for it:

CREATE MATERIALIZED VIEW LOG ON testing_mview WITH ROWID;
Materialized view log created.

My requirement is
1) if there is an UPDATE/DELETE/INSERT on testing_mview, it should be writen to MATERIALIZED VIEW LOG (this has been achieved)
2) Materialized view testing_mview1 of 11g on server 10.1.4.32 should pull these changes on a scheduled basis ( I have created the database link ORCL10R2 here from 11g to 10 g)

On 11g:
SQL> create database link ORCL10R2 connect to omig identified by pswd using
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.4.30)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ICS3)
)
)';

Database link created

SQL> create materialized view testing_mview1 REFRESH FAST with rowid as select * from testing_mview@ORCL10R2;
create materialized view testing_mview1 REFRESH FAST with rowid as select * from testing_mview@ORCL10R2
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object SYS.DBMS_SNAPSHOT@ORCL10R2
ORA-00604: error occurred at recursive SQL level 2
ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [55916]
ORA-02063: preceding 2 lines from ORCL10R2

View 3 Replies View Related

PL/SQL :: Materialized View Based On Stored Procedure Data

Sep 23, 2013

is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it? 

View 4 Replies View Related

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related

Creating Materialized View On Remote Simple View

Dec 11, 2012

I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.

A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.

Is there another work around for this situation without having to work inside the data sources' environment?

View 5 Replies View Related

SQL & PL/SQL :: Regular Table Or Materialized View - Clone Table Data In Another Database

Jul 19, 2010

There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.

Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.

View 19 Replies View Related

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

May 2, 2012

I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.

EXEC DBMS_MVIEW.REFRESH('pro_mview','C');

But I am getting the below error.

*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1

I am able to fetch the data from that materialized view(pro_mview).

View 3 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View

Apr 26, 2011

difference between view and materialized view?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View?

Aug 12, 2013

what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?

View 7 Replies View Related

Store Procedure In View With Select Statement

Aug 31, 2012

Need a trigger in view with select statement that means

CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TEST_TABLE;
CREATE OR REPLACE TRIGGER TEST_VIEW_TRG1
INSTEAD OF DELETE ON TEST_VIEW DECLARE
BEGIN
Dbms_Output.Put_Line('STATEMENT TRIGGER.');
END;

i wanted to use select statement instead of delete.How can i get that

View 1 Replies View Related

Oracle Store Procedure - Table Or View Does Not Exist

Nov 21, 2011

I have created this store procedure:

create or replace PROCEDURE INSERT_TESTTABLE
(
PrimaryKey IN NUMBER
,One IN VARCHAR2
,Two IN VARCHAR2
,Three IN VARCHAR2
,Four IN VARCHAR2
[code].......

And I get this error: Error(15,13): PL/SQL: ORA-00942: table or view does not exist

View 1 Replies View Related

PL/SQL :: How To Compress Multiple Files And Store In Table

Apr 9, 2013

I am having many different files and i want to use pure plsql to get these files stored as blob from a table then compress them into 1 files and store that into another table. I did some search and its like possible but didnt get conclusive solution

View 4 Replies View Related

PL/SQL :: Update Multiple Records Using Store Procedure?

Feb 27, 2013

i am trying to update multiple records using store procedure but failed to achieve

for example my source is

emp_name sal
abhi 2000
arti 1500
priya 1700

i want to increase salary of emp whose salary is less than 2000 it means rest two salary should get update..using stored procedure only

i have tried following code

create or replace procedure upt_sal(p_sal out emp.sal%type, p_cursor out sys_refcursor)
is
begin
open p_cursor for
select sal into p_sal from emp;
if sal<2000 then
update emp set sal= sal+200;
end i;f
end;

and i have called the procedure using following codes

set serveroutput on
declare
p_sal emp.sal%type;
v_cursor sys_refcursor;
begin
upt_sal(p_sal,v_cursor);
fetch v_cursor into p_sal;
dbms_output.put_line(p_sal);
end;

the program is executing but i should get o/p like this after updating

1700
1900

but i am getting first row only

2000

and record is not updating...

View 15 Replies View Related

SQL & PL/SQL :: Materialized View

Jul 7, 2010

I have requirement to create materialized views. The design states to use Complete refresh. Now I am using WITH ROW_ID clause.

1. Question - what are the criteria to decide between WITH PRIMARY KEY & WITH ROW_ID clauses? I referred oracle doc, but couldn't exactly get this.

2. Background - The oracle doc mentions that - Primary key materialized views are the default type of materialized view. The master table must contain an enabled primary key constraint, and the defining query of the materialized view must specify all of the primary key columns directly.

Question - I saw some existing materialized views in my project using WITH PRIMARY KEY clause, but all of the primary key columns are not part of the Select query, but still these views are working fine, how is this possible?

3. Background - The oracle doc mentions that - Rowid materialized views must be based on a single table and cannot contain any of the following:

■Distinct or aggregate functions
■GROUP BY or CONNECT BY clauses
■Subqueries
■Joins
■Set operations

Question - But I have created a Rowid materialized view selecting data from more than one table & defining query involving outer joins. This materialized view got created & getting refreshed (complete refresh). But again this is contradicting with Oracle' statement.

View 1 Replies View Related

Creating Materialized View Log

Feb 7, 2013

There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?

View 3 Replies View Related

STALE Materialized View?

Jul 14, 2011

i have created FAST refresh materialized view.it is eligible for FAST refresh because when i executed SELECT * FROM USER_MVIEWS i could see FAST_REFRESHABLE=DIRLOAD_DML and STALENESS=STALE and it is not getting refreshed(through DBA_JOBS). Why it is in STALE status and how to resolve it. and one of my materialized views is in NEEDS_COMPILE status

View 3 Replies View Related

Materialized View Refresh?

Sep 10, 2010

A scheduler job is there to refresh some materialized views. All the views are run under single job name. Some mviews are not refreshing and those are not refreshing in the next run too.if we run those mviews manually then they are working fine from then . after some days those views which i told before are not refreshing and this happens frequently and i check the job_run_details views also and i am not seeing any errors in that.

View 6 Replies View Related

SQL & PL/SQL :: Materialized View Redefinition

Dec 11, 2008

I need redefine a Materialized View (add some columns). I try it with:

drop materialized view TCPIP preserve table;

ALTER TABLE TCPIP ADD (SUM_IP_OUT_OF_SEQ NUMBER)
/
ALTER TABLE TCPIP ADD (SUM_IP_OUT_OF_SEQ NUMBER)
/

and than create a new MV:

CREATE MATERIALIZED VIEW TCPIP
ON PREBUILT TABLE USING NO INDEX NEVER REFRESH ENABLE QUERY REWRITE as
SELECT ...

But with Oracle error: ORA-12060: shape of prebuilt table does not match definition query

I check the new MV definition, it is contain the 2 added columns.

View 6 Replies View Related

SQL & PL/SQL :: Indexes On Materialized View

Mar 16, 2011

Why we can create indexes only on materialized view and not on normal views?

View 8 Replies View Related

SQL & PL/SQL :: Syntax Of Materialized View?

Sep 9, 2010

Can u explain the syntax of materialized view??????

View 1 Replies View Related

SQL & PL/SQL :: How Much Space Is Used By The Materialized View

Oct 20, 2010

How can we find how much space is used by the materialized view.

View 1 Replies View Related

SQL & PL/SQL :: Oracle Materialized View

Jul 5, 2011

I have created a Materialized View (MV) and within the MV DDL have included a statement as per below. However, the MV does not seem to be refreshing on a daily basis. I can see the job in dba_jobs table. So the question, is there some system setting i need to execute or create in order for Oracle to know that it needs to run the Job defined in the dba_jobs table on a daily Basis?

View 8 Replies View Related

Analyzing Materialized View

Oct 26, 2012

I have created materialized view which hold few million records.Should i have to analyze the view and compute the statistics after i create the materialized view?

Also, just in case i need further indexing, should i have to take the statistics for the table again?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved