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 RepliesI have a Materialized view based on 2 views. I modified one column in both the views.Do i need to refresh the Materialized view.
E.g. :
In view1 and view2 i changed column like this
nvl(test.no,'n/a') testdata --changed
nvl(test.id,'n/a') testdata --original
CREATE MATERIALIZED VIEW MV_test
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
[code].........
I need to create a materialized view with a clob column based on a varchar2 column of a table.This is because in the mv the clob column data gets appended one after another.
View 2 Replies View RelatedHow can I view the log of a stored procedure after it is called by an application?
View 5 Replies View RelatedIs there a way to grant rights to view a stored procedure without granting execute permissions?
View 2 Replies View RelatedI 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?
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.
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.
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?
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.
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
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?
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.
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
CREATE OR replace PROCEDURE T_name_column
AS
file_handle utl_file.file_type;
BEGIN
[Code]....
How to create Data Block Using Stored Procedure in Forms10G ?
View 1 Replies View RelatedThis is my first time running a stored procedure. The procedure is already written.
We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.
The following code is a stored procedure I plan to use to populate a Data Warehouse dimension using data from two OLTP tables which already exist in my database. Notice that in my cursor select statement, I calculate an attribute using substr and instr, and I also assign a true or false value to a flag using a CASE statement.
CREATE OR REPLACE PROCEDURE populate_product_dimension
AS
v_Count NUMBER := 0;
v_NumRecs NUMBER;
/*Declare a cursor on the following query which returns mulitple rows of data from product and price_hist tables*/
[code]....
In my mind, Product_Code is declared correctly in the Cursor declaration Select statement.
I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
View 14 Replies View RelatedI am using:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Microsoft Visual Studio 2010 : VB Express
Microsoft .NET Framework : Version 4.0.30319 RTMRel
When I populate dataset using select query and use OracleCommandBuilder and update dataadapter, data is saved in database.
But if I put same select in store procedure and use this to populate dataset then data is not updated in db.
I get this error
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
at da1.Update(ds1, "req") where I am going wrong.
Dim conn As New OracleConnection
Dim da1 As OracleDataAdapter = New OracleDataAdapter
Dim ds1 = New DataSet
With conn
If .State = ConnectionState.Open Then .Close()
[Code]....
This procedure is not working properly.
create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;
the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated
ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1
can we change the existing materialized view to normal view? if yes how?
View 2 Replies View RelatedI am trying to create a datablock based on a procedure , but im getting errors in compilation:
Errors are :
1) identifier 'HSM_WSH_DEL_UTIL.DEL_TBL' must be declared
2) PL/SQL ERROR 320 at line 7, column 27 the declaration of the type of this expression is incomplete or malformed
Heres my pkg spec and body for the data block:
CREATE OR REPLACE PACKAGE hsm_wsh_del_util
IS
TYPE del_record IS RECORD (delivery_id NUMBER);
TYPE del_tbl IS TABLE OF del_record
INDEX BY BINARY_INTEGER;
PROCEDURE do_query (p_del IN OUT del_tbl);
END hsm_wsh_del_util;
[Code] .....
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?
I've created one procedure with following code.
PROCEDURE MaxNo IS
A Number;
BEGIN
Select Max(Supplier_ID)+1 into A from supplier ;
END;
SQL> Select Max(Supplier_ID)+1 from supplier;
MAX(SUPPLIER_ID)+1
------------------
6
when I execute it in a SQL PLus
SQL> SET SERVEROUTPUT ON
SQL> exec MaxNo;
PL/SQL procedure successfully completed.
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.
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).
Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4
All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like
create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;
difference between view and materialized view?
View 1 Replies View Relatedwhat 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