I've got a Source Data in complex relational formFor reporting purposes, a simpler, less normalized data model is neededThere are two Target views from the Source Data: one of them with full access to all datathe second one with access only to a subset of the data (same columns, but not all the records)For both target groups, a separate schema shall be available, each containing only relevant dataToday, these schemas are physically located on the same DB instance and host as the source dataA daily refresh is sufficientA later relocation of the reporting schemes to other DB instances shall be possible without major changes neededOracle 10g should be used I tried to accomplish this using Materialized Views (Materialized seems better since there will be sometime a need to have all the apropriate data somewhere else, geographically, AND it provides Complete Refresh from the Source), but there is a problem: when creating the MV there is a possibility to type 'SELECT *' - but after execution it changes into real columns names. It is important because later after adding a new column into Source Data it WILL NOT appear in MV after refresh.
I also thought about Data Guard, Streams and RAC, but I think only in the Materialized Views you may choose the data to show (rows, columns).
I have a set of rows based on a complex view from multiple table.
I will be updating some of its columns from front-end . Is there any possible ways to lock those rows of data while updating and no other users can update it;
I have a table tblcustomer and a view called vworder. I need to create a trigger such that any data being added into vworder first checks if the field customerid has the data in customerid of tblcustomer.. all it has to do is spit out a error "Customer ID not found"
So I created a view such as
Quote: create or replace TRIGGER trig_order BEFORE INSERT OR UPDATE ON vworder FOR EACH ROW DECLARE cust_id VARCHAR2(20); BEGIN SELECT customerid INTO cust_id FROM tblcustomer WHERE customerid = :new.customerid; EXCEPTION WHEN NO_DATA_FOUND THEN Raise_application_error(-20000, 'Customer ID Does Not Exist'); END;
But it comes with error
Quote: ORA-25001: cannot create this trigger type on this type of view 25001. 00000 - "cannot create this trigger type on views" *Cause: Only INSTEAD OF triggers can be created on a view. *Action: Change the trigger type to INSTEAD OF.
I am not an expert at all and I need to finish this today itself.
"Create a view named vuPassFailRate that will show the pass rate and fail rates of each test."
I have a table named Test_ID containing the following columns:
TEST_ID TEST_NAME PASSING_GRADE
And another table named Test_History containing the following columns:
TEST_ID STUDENT_ID SCORE
I'm assuming i have to create an inline view, and to work out the pass/fail rates i need to do something along the lines of (For pass rate) Where SCORE is greater than or equal to PASSING_GRADE, and TEST_ID equals TEST_ID, divide by a count of SCORE and multiply by 100. I just cant work out the nested select statements, and work out the formula using two columns in two tables.
I have been staring at this problem for so long now i cant see the wood for the trees.
I'm having trouble with a little assignment. "Create a view named vuPassFailRate that will show the pass rate and fail rates of each test." I have a table named Test_ID containing the following columns:
TEST_IDTEST_NAMEPASSING_GRADE
And another table named Test_History containing the following columns:
TEST_IDSTUDENT_IDSCORE
I'm assuming i have to create an inline view, and to work out the pass/fail rates i need to do something along the lines of (For pass rate) Where SCORE is greater than or equal to PASSING_GRADE, and TEST_ID equals TEST_ID, divide by a count of SCORE and multiply by 100. I just cant work out the nested select statements, and work out the formula using two columns in two tables. I have been staring at this problem for so long now i cant see the wood for the trees.
I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.
Materialized view is created by
CREATE MATERIALIZED VIEW mv_employee_name AS SELECT EMPLID, EMPL_NAME FROM VEMPDATA@REMOTEDB WHERE REGION = 'US';
I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.
I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.
I have two databases (say DATABASE 1 with user USER1 with table A and DATABASE2 with user USER1 with table A). I want to select the table A in USER1 DATABASE2 from DATABASE1 USER1.
I have 3 reporting tables with 2.2 million records each being rebuilt nightly. The data is used online 24/7 by users and thus, snapshot tables are being built from the refreshed reporting tables. The current method to do this:
delete from snapshot table; insert into snapshot table (select * from report table); <repeat for other 2 tables> commit;
This seems to me to be resource intense on the system even though the table is defined with nologging option.
Is it better to create a MV (select only with refresh complete on demand)? The query is very simple without joins so it at first seems like overkill. However, I am also seeing that dbms_mview.refresh allows for an atomic option. Thus, if 1 of the 3 MVs fails during refresh all 3 rollback, which is a nice feature.
Are there better ways to replicate a snapshot table that I've missed? Is a delete and insert strategy a bad idea?
UPDATE PMAPS_Z_DEMAND_MASTER_DATA_TMP C SET PACKAGE_NAME = (SELECT DISTINCT A.PACKAGE
[Code]...
i wanted to update data in PMAPS_Z_DEMAND_MASTER_DATA_TMP table but i kept getting "more than 1 value returned in sub query" error, i know what it is but i don't know why it is happening here.
I have been using MSaccess to do some updating and deleting of data.I have 2 tables, 1 with data, 1 with the criteria to change, 2 colums (FINDWORD and REPLACEWORD).
The main table has lots of data, and the SQL query im using in access looks like this:
UPDATE TABLE1, tblFindReplaceWords SET CleanList.COLUMN-NAME = Replace([TABLE1].[COLUMN-NAME],[tblFindReplaceWords].[FINDWORD],[tblFindReplaceWords].[REPLACEWORD]);
how would I go about doing the same thing in orcale?
Is it possible to create trigger on the various tables and views exists (i.e. dynamic performance views) in data dictionary, when ever any DML operations performs by Oracle it self?
I have a task to update one of the rows in a table (having only 2 columns, number and long) which is long data type. We are on Oracle 10g. Not sure how to use update for a long data type column.
I have tried using dbms_metadata_util.long2varchar, but still not getting what I want.
Need to create a report based on date ranges and for this created a interactive report and two page item datepicker fields P15_fromdate and p15_todate. Report works fine with this criteria.But user wants one more field quarter(P15_quarter), When they select the quarter the range values has to get reset and as to get applied to report.
Issue here is unable to find a way to set the page range item values based on the quarter field selection
I have to convert some existing materialized views (fast refresh) to partition materialized views.
Database version is oracle 10.1.0.4. I have decided to use on prebuilt table option to do the partitioning as it minimizes the time to transfer from the master site.
1) stop replication 1) create interim tables with similar structure as the materialized views 2) transfer all data from the materialized views to the interim tables 4) script out the materialized views structure and add in on prebuilt table option in the scripts 5) drop the materialized views 6) rename the interim tables with the same name as the materialized views 7) run the scripts to create the materialized views with on prebuilt table option 8) refresh the newly created materialized views -> it should take a short time since I am using on prebuilt table option
But I am facing one major issue. That is if I drop the materialized views, the materialized view logs of the master tables are purged. When the materialized views are refreshed fast, there are some data missing. the data that are purged out when the materialized view are dropped.
Do you happen to know other ways that existing materialized views can be converted to partitioned materialized views? Do you have any workaround to prevent the materialized view logs from being purged?
Table 1Name Item DateJon Apples 06/11/2013 00:30:00 hrsSam OrangesNish Apples Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon Apples 8
I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar
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()
I have a requirement to create an xml file from a table. The table has product number repeated for each values of attrbiutes. Is it possible to create an xml file with product_id only once and all the attributes and values listed under that.
Example
WITH tmp AS (SELECT '0983433' AS product, 'color' prd_attr, 'blue' AS val FROM DUAL UNION SELECT '0983433' AS product, 'size' prd_attr, '23' AS val
I am trying to develop an ongoing interview schedule.
interviews may be held on FRIDAY, SATURDAY, TUESDAY or any other day or Days in a every week selected by the user and also at some specific time.
we need to schedule the closest comming day based on Application date.
for example: today is WEDNESDAY and our schedule interview day is FRIDAY and SUNDAY. If one application is received today we need to schedule interview on FRIDAY because this is the nearest/closest day of WEDNESDAY. and if the application is received on Saturday then of course Sunday will be scheduled.
if Application is received on same day e.g. FRIDAY is scheduled and we receive Application on FRIDAY then comes the concept of time. interview is schedules on FRIDAY 11:30 AM.
if application is received on before 11:30AM, the same day will be scheduled. If application is received after 11:30 AM then of course next closest day will be scheduled.
I'd like to select one of two fields from a table, based on which of them is greater for that row.
e.g. For every row in JOINING_TBL, I'd like to select either JOINING_DATE or REJOINING_DATE, whichever is greater. I do dream about being able to use a Java-like ternary operator in here, something like this:-
SELECT (JOINING_DATE>REJOINING_DATE?JOINING_DATE:REJOINING_DATE) FROM JOINING_TBL
However, I understand(through other fora) that this can't be used in here.
We are using oracle forms 6i as front end and oracle 10g as back end.When we are creating a new user, the password should be stored in back end table in encrypted format.
i have a parameter form where user will input from item to item and items within this range will be come as output , but i want an option to select the random items from the list, like i may not select all the items from 1 to 10 , but instead i want to pick or select randomly , i may opt for 1,3,4 and 7.
Our term (strm) is dictated by the term_begin_dt and term_end_dt dates but I want to keep selecting that term until 1 week before the next term opens and then switch to that term.
Basically, I don't want any gaps between a term.
Output:
select strm when sysdate is between term_begin_dt and term_end_dt (strm would equal 3943) select strm until 1 week before the start of the next term (4027) (strm would equal 3943) select strm when 1 week before term_begin_dt (strm would equal 4027)
Repeat for the next term and so on 12/16/2011 select strm from term where trunc(sysdate) between trunc(term_begin_dt) and trunc(term_end_dt)
Output: 3943 12/17/2011 - 01/01/2012
select strm from term where ?
Output: 3943
01/02/2012 - 5/4/2012 select strm from term where ?
Output: 4027
Repeat.
Test Case:
CREATE TABLE TERM ( STRM VARCHAR2(4 BYTE), DESCR VARCHAR2(20 BYTE), TERM_BEGIN_DT DATE, TERM_END_DT DATE )
Insert into TERM (STRM, DESCR, TERM_BEGIN_DT, TERM_END_DT) Values ('3943', '2011 Fall Semester', TO_DATE('08/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into TERM [code].....
I have a question regarding a selection limit from a list of values. The table where my IDs (unit_codes) come from has another column called 'points'. each ID has its own number of points. Is there a way to put a selection limit on the display field that points will be placed on?
FOR EXAMPLE: a course block has a selection limit of 70 points, A person named someguy selects : music = 20 points maths = 20 points mathsXTRA = 40 points **ALERT!!! - U can ONLY select 70 points worth of units**
