things to ask in an online dating message - Updating a view in oracle

SESSIONKEY = sys_context('userenv','SESSION_USER') 12 and slb. November 14, 2008 - pm UTC I've written a thousand times perhaps... November 11, 2009 - am UTC you would have to write instead of triggers to do that.

TIMEUnit 7 FROM my Table A, my Slb slb, my USERPROF u1 8 where a.cny# = slb.cny# 9 and a.location# = slb.locationkey 10 -- and nvl(a.location#,0) = slb.locationkey 11 and slb. We would range partition (for 10 days requirement) and probably list partition for the T versus T_TMP stuff.

COM CREATE OR REPLACE VIEW V_my View AS 2 SELECT 3 A. See the application developers guide for workspace manager - search this site for dbms_wm for examples.

how to do carbon dating calculation - Updating a view in oracle

COM select column_name, UPDATABLE, 2 insertable,deletable 3 from user_updatable_columns 4 where table_name = 'V_MYVIEW' 5 / COLUMN_NAME UPD INS DEL ------------------------------ --- --- --- CNY# NO NO NO RECORD# NO NO NO STATUS NO NO NO TIMEUNIT NO NO NO same thing as before, it is because of the NVL on the table we are trying to keypreserve [email protected] We are not considering to range partition since there will be too many partitions (more than 5 yrs of data ~1800 partitions)to hold and local indexes at that level doesn't give that optimal performance. indexes can be maintained during partition operations, index the way you need, use partitioning I just want to know how to get the column names in spool file. November 18, 2008 - pm UTC sqlplus doesn't really work that way. Using partitioning if you need to segregate them physically for some reason, but have a single table.

COM CREATE OR REPLACE VIEW V_my View AS 2 SELECT 3 A. I don't see every review followup They are supposed to be reviews (that is, not questions) I don't spend 24 hours a day doing this. The difference between T and T_Tmp is that T will have a column named _type with Values 'Input','Output' where as the table T_Tmp will not have that column since all records will have File_type as 'Temp'. Temp1 field1 number, field2 varchar2(200) Temp2 field1 number, field2 varchar2(200) I've a view temp_v with following query create or replace view temp_v as select * from temp1 union all select * from temp2 If I want to update this view,for ex. I want to update the both the tables using a single statement. which makes me say "do not write instead of triggers" but rather 'fix your data model' triggers are magic magic is bad triggers are magic Either update the base tables (straight forward, everyone that reads the code KNOWS what is going on) or fix the data model - you want a single table apparently.

You would have to use an instead of trigger (not recommended) to make it updateable.

You might argue "why isn't ENAME updateable in the second view" - the answer is "it just isn't". The introduction of your scalar subquery dependent on something made some level of view merging not possible and made the view be marked as not updateable.

I searched some stuff and it seems that UNION ALL doesn't become an updateable view but going my your thougts in these forums the INSTEAD of triggers can do the MAGIC. We are not considering to range partition since there will be too many partitions (more than 5 yrs of data ~1800 partitions)to hold and local indexes at that level doesn't give that optimal performance .... But, if there is no record for a particular select query, the spool file only contains "no rows selected". Can we get the column names under which "no rows selected" message??? Thanks and Regards, William Sorry, The creates are: CREATE TABLE DCF_FUND ( "CODE" VARCHAR2(10), "NAME" VARCHAR2(30), CONSTRAINT "DCF_FUNDS_PK" PRIMARY KEY ("CODE"), CONSTRAINT "DCF_FUNDS_UK1" UNIQUE ("NAME") ) ; CREATE TABLE DCF_AB_STOCK_FUND_POSITION ( "CODE" VARCHAR2(10), "NAME" VARCHAR2(20), "POSITION" NUMBER, CONSTRAINT "DCF_AB_STOCK_FUND_POSITIO_PK" PRIMARY KEY ("CODE") ) ; May 10, 2012 - am UTC In the first query - you have (select 1 from dual where dummy = x) and X is an attribute from the outer query and that attribute comes from the with view and is "select 'X' from dual" Now, select 'X' from dual might be 'X' or it might be NULL - we don't know until runtime. And that is causing the database to just say "cannot update this" In the second query, the scalar subquery isn't reliant on anything - it stands alone, it doesn't need the with subquery at all.

So we are considering T to be a regular heap table and regular B*tree indexes and consider T_TMP as a range based partition table where in the temp records will be dropped after the workflow gets finished, and we have defined the retention to be a max of 10 days for a workflow to get finished. Normally, whenver we use spool file for listing the selected records, that will contain column name and corresponding data. is there a really inefficient way to write a query to make sure it returns at least one row? If you need this sort of control over the output format, I would suggest, no - I would actually state "you need to use a report writing tool or write a program" sqlplus is a simple, very very very simple command line interface for ad-hoc queries. Hi, Tom, I've a view as: CREATE OR REPLACE VIEW test AS WITH v1 AS (SELECT f.code, f.name, pcode, pname, (SELECT 'X' FROM dual ) x FROM dcf_fund f, dcf_ab_stock_fund_position p WHERE f.code=) SELECT (SELECT 1 FROM dual WHERE dummy=x ) v1, v1.* FROM v1 and it's not updatable: select column_name, updatable from dba_updatable_columns where table_name='TEST'; COLUMN_NAME UPDATABLE ------------------------------ --------- V1 NO CODE NO NAME NO PCODE NO PNAME NO START_DATE NO But, if I: CREATE OR REPLACE VIEW test AS WITH v1 AS (SELECT f.code, f.name, pcode, pname, (SELECT 'X' FROM dual ) x FROM dcf_fund f, dcf_ab_stock_fund_position p WHERE f.code=) SELECT (SELECT 1 FROM dual WHERE dummy='X' ) v1, v1.* FROM v1 It's updatable: COLUMN_NAME UPDATABLE ------------------------------ --------- V1 NO CODE YES NAME YES PCODE YES PNAME YES X NO And if I: CREATE OR REPLACE VIEW test AS WITH v1 AS (SELECT f.code, f.name, pcode, pname, 'X' x FROM dcf_fund f, dcf_ab_stock_fund_position p WHERE f.code=) SELECT (SELECT 1 FROM dual WHERE dummy=x ) v1, v1.* FROM v1 It's also updatable: COLUMN_NAME UPDATABLE ------------------------------ --------- V1 NO CODE YES NAME YES PCODE YES PNAME YES X NO Why?

I'm going to learn more about workspace manager and test it.

We already do this (and more) Tom, It's optimistic that we choose the right way to maintain inherited history model (views and "instead of" triggers) in our solution - the same as Oracle in version-enable tables.

COM CREATE OR REPLACE VIEW V_my View AS 2 SELECT 3 A. I just learn from this thread that the database allow updates to objects in the key preserved table.

Tags: , ,