How View Data Gets Updated

Jul 16, 2013

I want know how the values in view DBA_FEATURE_USAGE_STATISTICS gets updated i have already checked the metadata of this view but unable to find how / when the values are being updated.

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Know If All_source View Updated Or New Data Inserted

May 28, 2010

how can i know if all_source view is updated or a new data is inserted..

View 6 Replies View Related

SQL & PL/SQL :: Data Should Be Updated Back To Table

Aug 16, 2011

The requirement is, the combination of col1,col2,col3 and col4 should always be unique, and wherever the col1, col2,col3 are same then col4 should be the sequence, starting from 1. Likewise the data should be updated back to the table.I'm able to do this using PL/SQL. Can I do the same using a single update statement?

create table tab1 (col1 number(5), col2 number(5), col3 number(5), col4 number(5));

Existing Data:

insert into tab1 values (101,521,3,1);
insert into tab1 values (101,521,3,1);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,523,3,1);
insert into tab1 values (101,523,3,2);
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Get Last Updated Date When Data Modified?

Jun 20, 2011

I have multiple project databases where their tables are similar. I wanted to know just a single date from each project databases is modified/updated. If I run a query like select * from component order by eng_proj_id, chg_date desc then it will return many records sorted by their eng_proj_id and chg_date in their descending but I only need just a single record with last update from each project databases.

View 13 Replies View Related

PL/SQL :: Inserting Data Into Updated Table

Jul 22, 2012

I have a table created with the following code:

CREATE TABLE CLIENT
(
CLIENT_ID NUMERIC(2),
CLIENT_NAME VARCHAR2(25),
CONTACT_LAST_NAME VARCHAR2(15),

[Code]...

I have altered the table to have the following:

ALTER TABLE CLIENT
ADD CLIENT_CITY VARCHAR2(25);

I am trying to insert new data into said table that was updated:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1;

Then I get the following error:

Error starting at line 1 in command:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1
Error at Command Line:4 Column:15
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:   
*Action:

View 3 Replies View Related

Forms :: Auto Refresh When Data Updated

Apr 19, 2010

i need a code to auto refresh or after 1 minute in form & report when the data is updated. basically i am deploying the plasma tv in hall so executive can see the status report where they need its going to be like a stock system multiple forms or reports sticky dynamically change .

View 2 Replies View Related

Forms :: How To Use Updated Temptable Data In Report

Apr 26, 2010

I have the manipulated data on temporary table name "tempdata" and i want to display it in to my report, I am using the temporary table, and also call report from the same session on which data is updated on temporary table. but its still not showing data on the report, I think this is the session problem of the form but i also call report from the same form , so the session should b same for both the report as well as for the form, so y don't the report show the temporary table data.

View 39 Replies View Related

SQL & PL/SQL :: Data Dictionary View Not Available

Nov 1, 2011

Question 1) I have read the following statement in a PL/SQL book.

Quote:To check whether an existing procedure is compiled for native execution or not, you can query the following data dictionary views:

[USER | ALL | DBA]_STORED_SETTINGS
[USER | ALL | DBA ]_PLSQL_OBJECTS

However, I when i query the view USER_PLSQL_OBJECTS I get the following error message:

Quote:ORA-00942: table or view does not exist

Question 2) I have read the PLSQL_WARNING can be set to DEFERRED at the system level. However, I am unable to defer it. tell me how to apply defer caluse to following statement:

Quote:ALTER SYSTEM SET PLSQL_WARNINGS ='DISABLE:ALL'

View 10 Replies View Related

SQL & PL/SQL :: Insert Data Into View

Mar 27, 2010

I have created a view in that the primary key column is not included,so that we cannot insert data into that view,but i want to insert data into that view without using Instead of Triggers.

CM: swapped version and title so they're the right way round.

View 12 Replies View Related

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

SQL & PL/SQL :: How To Parse Data And Add Columns In A View

Jan 11, 2012

I have a column which holds the data in the below format.

Source Data :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192>SNO_54-90-16489-008G
>SNO_SDPMDB-008G-11>SNO_54-90-18008-008G>SNO_54-62-08791-008G>SNO_20-81-00327
>SNO_SDPMDB-008G-12>SNO_54-90-17830-008G>SNO_54-62-08598-008G>SNO_20-81-00327

[code]..

Problem Statement :
Split the above data into individual components and create columns / aliases dynamically.If the column is present then place the data under the created column.

Example 1) :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192

Result
Column Name : SKUCol_54-99
Data :SNO_SDSDQ-8192-BNSNO_54-99-24120-8192

Example 2)
>SNO_SDSDAA-002G-101-J>SNO_54-90-16002-002G>SNO_54-62-05781-002G>SNO_20-81-00135-5

Column Name : SKU54-90Col_54_62Col_20_81
Data :SNO_SDSDAA-002G-101-JSNO_54-90-16002-002GSNO_54-62-05781-002GSNO_20-81-00135-5

Column Name can be derieved from the components like SNO_54-62-05781-002G

i.e. SNO_54-62-05781-002G ==> SNO_-05781-002G
"54-62" will give the Col_Name

The First Column will always have data starting with
SNO_SD%
This column is constant and will be named as SKU.

I need this data to be placed in the same record / row but under different columns as per the data set.Basically, Can the data be split into multiple parts based on delimeters and the columns are created based on the unique data in the parts that form the data in the column.

View 1 Replies View Related

SQL & PL/SQL :: How To Load The Data From View To Table

Feb 27, 2012

How can i load the data into a new table from view,when ever scheduler runs in the night, the data gets loaded in to a view and data coming from different tables and i should load that data every day and i dont want previous data again.The data should be loaded along with view .

View 6 Replies View Related

Security :: View Column Data

Jul 25, 2012

My boss make a requirement in exist database as some user can view salary column at employment table by SQL and some user can view salary column at employment table by SQL.

The boss do not like to make changes front SQL. Ooracle 11g vault or Oracle Label Security is best for this requirement?
my oS is 2008 32 bit window and DB is 11.2.0.1

View 4 Replies View Related

Forms :: Using Data Grid View In 6i?

Jan 26, 2010

Is it possible to use Data grid view in Forms6i just as in Visual Basic?

View 7 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

SQL & PL/SQL :: Convert Data To Table / Inline View

May 10, 2013

Is there a function that allows the following?

select SOME_FUNCTION('N','E','S','W') from dual;

That returns

N
E
S
W

Currently I'm just doing the following

WITH direction AS
(SELECT 'N' dir FROM DUAL
UNION
SELECT 'E' FROM DUAL
UNION
SELECT 'S' FROM DUAL
UNION
SELECT 'W' FROM DUAL)
SELECT *
FROM direction;

View 4 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 :: Merge Data To A Database View On Demand

Jan 25, 2011

I have two tables:

Table 1:
CONTENT_ID number primary key,
URL VARCHAR2(1024) not null,
TITLE VARCHAR2(200) not null

Table 2:
CONTENT_ID number primary key,
URL VARCHAR2(1024) not null,
TITLE VARCHAR2(200) not null

Both tables are the same layout however I need to merge the data to a DB View on demand. What would the SQL look like to join like this?

View 7 Replies View Related

Reports & Discoverer :: View Not Showing All Data?

Nov 1, 2011

We have a view that shows the all the data that we expect, so we created a report to show that data, the report worked, then all of a sudden the report stopped showing all the data. (we did have a server move)I checked the view and the all the data is there....

I have created a new report based on the view but it does the same thing, it doesn't bring all the data up (i get very old data), there are no conditions or anything.

other tables and views seem fine. i have recreated the view and i still have the same problem.

View 2 Replies View Related

SQL & PL/SQL :: Procedure Successfully Completed But Not Able To View The Data

Sep 26, 2010

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.

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

SQL & PL/SQL :: Data Manipulation Operation Not Legal On View

Jan 11, 2012

CREATE TABLE TEST(FIRST VARCHAR2(10),SECOND VARCHAR2(10));
INSERT INTO TEST VALUES('AA','BB');
INSERT INTO TEST VALUES('AA','BB');
INSERT INTO TEST VALUES('bb','aa');
INSERT INTO TEST VALUES('bb','aa');
COMMIT;
SQL> SELECT * FROM TEST;

[code]....

ORA-01732:data manipulation operation not legal on this view

I want to delete the Repetitive data,What's wrong with these SQL statements?

View 1 Replies View Related

Force View Number Data Type Precision?

Aug 18, 2011

I am working on an application that pulls data from an Oracle view into Microsoft Excel (Oracle 11g, MS Excel 2003). It is an automated pivot table created through vba. The problem I'm having is that the decimal points from number columns are being truncated - they don't make it to the pivot table.

When I use the pivot table wizard and set the external data source using a SQL string (select * from view), the precision is not lost. When I create the pivot table automatically in vba, the precision is lost. The pivot table settings, regardless of how the data is brought in, shows the format of the number column as general. This tells me that Excel is trying to figure out what the data type is, and can't (not smart enough).

When looking at the description of the view, the data type is NUMBER (no precision). The table that it is pulling from has the precision set (NUMBER(11,3)). I have tried using the following, but it doesn't work:

select to_number(field_name, '99999999.999') field_name from view

View 7 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

SQL & PL/SQL :: Where Oracle Stores Data Fetched From Inline View

Apr 25, 2012

Just want to confirm, that where Oracle stores data fetched from inline view.

I am asking, as I am making an inline view on a very large table and its getting failed with TEMP space error.

View 3 Replies View Related

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 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

SQL & PL/SQL :: View Sample Data From Very Table Which Is Large In Size?

Apr 26, 2010

I have a query on , how to view the sample data from a very table which is large in size ( more than 10 million ).

I just need to see some sample data from a large table ( to see what kind of data which is application related ).

My question is :

Select *
from Sample_table
where rownum < 10

is this a Good way to view the sample data ?

I have understanidng that the rownum will be assigined to the rows once all the rows are reteived.

So what is the best way to view ?..I am not sure of any condition to put in the intial time of querying.

View 5 Replies View Related







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