To Select Recent Row Updated Detail
May 26, 2011I have one table,in that table have number of rows. I want to select recent row updated details.
View 2 Replies
ADVERTISEMENT
SQL & PL/SQL :: Select Updated Rows From One Table
Jul 13, 2013There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.
TABLE A
COL_A1COL_A2
AAAMOBILE
BBBTABLET
CCCDESKTOP
DDDUNKNOWN
TABLE B
COL_B1COL_B2
AAAMOBILE
BBBUNKNOWN
CCCDESKTOP
The select statement should return following rows from TABLE A
COL_A1COL_A2
BBBTABLET
DDDUNKNOWN
SQL & PL/SQL :: How To Select Latest Updated Record From Table
Sep 21, 2011I want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0
View 8 Replies View RelatedReports & Discoverer :: Master-detail Report ( 3 / 4 Columns Detail To Be Printed)
Jul 19, 2013my report is based on two tables.master and detail. i am using GROUP ABOVE style.displaying master tables columns up and detail table columns down.
my problem is ...i want 3 columns from master table (i.e)RECEIVER ID,RECEIVER NAME and RECEIVE DATE to be printed at the bottom of the page.
and also i want only 4 records from detail table to be printed on each page.
Forms :: Oracle - Date Field In Detail Part Of Master Detail?
Jul 13, 2011I have to date field in detail part of master detail Form. In detail part i have field of From_date and To_date. I want when i go to next record in detail part ,,,from date is initiaze by previous to date+1....eg.like
1.record: From date=01-jan-2011, To_date=10-jan-2011
2.Record: From_date = 11-jan-2011;
I have Attached calender to two feild...and calender is the different block... In Detail block when_record_instance i handled the;; From date=previous to-date +1;
but when i double click the feild calender is open and when_new_record trigger is fired agian. calender called ...the when_new_record is not callled .....
Application Express :: Unable To See Items Of Detail Form In Master Detail Form In Page Details?
Oct 31, 2013I created a master-detail form using wizard in oracle apex 4.2 Now I want to attach an LOV to one of the items of detail form, which is visible on the same window as of master form. To do so, I need to first find the item in detail page, details of which are not available in page definitions. I can see all the items of master form but none for detail form in "Page Rendering" section.
View 0 Replies View RelatedSelecting The Most Recent Value?
Sep 7, 2007Have a table like this:
ID1ID2DATEID2Value
1121/1/20066
1241/1/2006400
1246/1/2006410
1366/1/2006100
2121/1/20077
2246/1/2007350
2247/1/2007360
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1246/1/2006410
2247/1/2007360
I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.
Finding 10 Most Recent Missions
Jun 23, 2008What i have is table with:
mission_id, mission_type_id, security_level and code_name.
What i have to do is get the 10 most recent missions and change their security level to the highest one in their mission_type_id but ONLY if code_name length is >7
So far I have this, the problem is that the oracle moans about the order and wants me to close the bracket before the order
sql
UPDATE missions m
SET m.security_level = (
SELECT max(m2.security_level)
FROM missions m2
WHERE m2.mission_type_id = m.mission_type_id
AND length(m2.code_name) > 7
)
WHERE m.mission_ID IN (
Select m3.mission_id
From missions m3
ORDER BY m3.mission_id desc)
PL/SQL :: Get Not Null Recent Values From History Data?
Jul 5, 2012Below given is the sample data
SELECT *
FROM
(
SELECT 1 ORDR_ID, TO_DATE('01-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 5 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('15-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'KPD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('16-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'ALD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('02-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 9 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('05-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 10 SIZE_, '' Colr_CD FROM DUAL UNION ALL
[code]....
Where every first row is the initial entry for an order and following rows are the changes done to the order with modification date
And the expected output is
ORDR_ID Start_date End_date SIZE_ COLR_CD
1 01-01-12 01/15/2012 5 APD
1 01/15/2012 01/16/2012 5 KPD
1 01/16/2012 00-01-00 5 ALD
2 01-02-12 01-05-12 9 APD
2 01-05-12 01-06-12 10 APD
[code]....
I cam use a select statement within select, but wanted to write an efficient SQL...
I'm using
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0
ORA-00338 - Log 3 Of Thread 1 Is More Recent Than Control File
May 9, 2013I encountered error ORA-00338 while performing incomplete database recovery:
ORA-00283: recovery session canceled due to errors
ORA-00338: log 3 of thread 1 is more recent than control file
ORA-00312: online log 3 thread 1: '+DG_REDO/dbase/onlinelog/group_3.267.814820795'
Recovery Manager complete.
I performed this recovery on oracle 10g, ASM database.
1. What are possible causes behind this error ORA-00338 ?
2. This error occurs at the start of media recovery, while RMAN is applying archived logs. So does RMAN apply "online REDO" logs after applying "archived" logs required for recovery or before them ?
3. How does rman come to know that online log 3 is more recent than control file ? How can I get scn of online log & control file in my environment ?
Following are few details related to checkpoint scn/change# info. I found in my environment..Before recovery:
view
v$datafile: 642935
v$datafile_header: 642935
v$database: 642935
v$log: 640956
After unsuccessful recovery (ORA 338 :( )
v$datafile: 640917
v$datafile_header: 640056
v$database: 640906
v$log: 640906
SQL & PL/SQL :: Update Complete Flag Most Recent 2 Rows By Category
Jul 29, 2013I would like to update the complete flag of all the rows of a table except the most recent two rows of each category.
The table has fields like:
category - string,
item - string,
creation_date - string ('YYYYMMDDHHMI'),
complete_flag - boolean,
etc.
Each category does not have the same amount of records with the same creation_date so I do not want to filter by creation_date. Is there a way to accomplish this?
SQL & PL/SQL :: Return Most Recent Record Previous To Start Date?
Nov 18, 2011I have a sub query (already dervived from several other tables) that has a list of children in with the date they started their course (tableofchildren). Child IDs may be duplicated in this query but each record will have unique start dates per child ID.
I have a second sub query which lists workers involved with the children in the first query (tableofworkers). A worker may be responsible for more than one child but has the unique child in the record to identify involvement with the child.
I need to join these queries together to return the child's record with the WorkerName and the AllocatedStartDate of the worker who was most recently involved with the child prior to the date (EnteredCourseDate) the child started their course (OutputWanted) - the worker associated with the child when they started their course.
A couple of points - I need to deploy this into another reporting application that doesn't support cursors etc or the 'With' operator. Also, I tend to join tables/queries in the Where clause so if it's possible that way that would be great.
OC.
create table tableofchildren
(ChildID varchar(20),
ChildName varchar (50),
[Code].....
Restore Recent Backed Up Control File And Then Restore The Database?
May 6, 2013The controlfiles were corrupted due to some issue. So, I had to restore the recent backed up control file and then restore the database.
The weird thing which I have noticed is that. I tried backing up Yesterday's Control file. So, I would say that the backup pieces it should pick should be from yesterday. however, the backup pieces are getting restored from last week's backup.
PL/SQL :: How To Get The Record Before Updated
Sep 3, 2012I have a problem where my record is updated yesterday. How to get the record before updated?
View 4 Replies View RelatedLogon Trigger Not Getting Updated
May 9, 2011There is a xxxxx.sql file in the OS level which contains create or replace trigger script. I modified that file with the code below:
if(upper(USER)= 'U_USER') then
execute immediate 'alter session set current_schema=USER';
end if;
after that, I went to sqlplus
and logged in to U_USER like this
SQL>conn U_USER/U_USER;
connected.
SQL>@xxxxx
some numbers are getting generated if i press enter.
Then i press ctrl +c and get to sql prompt.
I issue
SQL>select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;
I am getting the output as U_USER but I must get the output as USER and not as U_USER. Basically the trigger is not getting updated.
SQL & PL/SQL :: How To Find Which Table Is Last Updated
Apr 11, 2012I want to know that How to find which table got last updated and how to find last DDL and DML operation obtained in which table? here I know the table name
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_
---------
20-MAR-12
SQL> TRUNCATE TABLE PREM;
Table truncated.
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_
---------
10-APR-12
Note: With out enable the auditing I want to know that .
SQL & PL/SQL :: Rows Of A Table Updated
Jul 17, 2012write a query find which rows of a table is updated on 2 days before?
(OR)
In table there r so many rows write a query which two rows r updated last two day before?
SQL & PL/SQL :: What Is Being Inserted / Updated In Database Currently
Apr 2, 2011I wanted to know if there is a way to keep track of what is being inserted/updated in the database currently.
I tried psoug website and SQL_Trace at orafaq (I cannot post the links) but those are useful when you want to see what happens with your queries in a session... When I tried to modify an outer session by its id I couldn't find any trace file that could've been generated (and its impossible to change "tracefile_identifier" in an outer session)
There is an application that is customized by me and it inserts some additional records into the DB in its backend functionality to which I do not have an access (I mean I don't have any source of the backend code), so if I need to see what are the inserts being made, I have to do that directly from the oracle level. I've heard that it is possible and easy to do in MySQL (but I never used that DBMS).
SQL & PL/SQL :: How Many (count Of) Records Got Updated
Dec 8, 2011I Have a table with 100records.after sometime i updated some records . But after that i want to know how many (count of) records got updated
View 3 Replies View RelatedSQL & PL/SQL :: Field Keep On Getting Updated By Other System
May 20, 2011I have a promotion table which is having 3 columns.
PROMOTION_ID
display_name
ASSET_VERSION
This ASSET_VERSION
field keep on getting updated by other system where they may update the display name of the promotion_id.The main concept of this table is there will be a unique combitination of asset_id and promotion id.
Promotion_idDISPLAY_NAME ASSET_VERSION
123 1ST VERSION 1
123 2ND VERSION 2
123 3RD VERSION 3
456 1ST VERSION 1
456 2ND VERSION 2
456 3RD VERSION 3
789 1ST VERSION 1
789 2ND VERSION 2
789 3RD VERSION 3
So,my requirement is that i have the fetch promotion_id ,display_name that is having highset asset_version.
The OUTPUT SHOULD BE:
Promotion_idDISPLAY_NAME
123 3RD VERSION
456 3RD VERSION
789 3RD VERSION
I need a single sql for this .
Driver used: oracle.jdbc.xa.client.OracleXADataSource
Orcale version: 11G
How View Data Gets Updated
Jul 16, 2013I 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 View RelatedTriggers / Referencing Non-updated Fields?
Jan 11, 2011I'm having a bit of a problem getting the syntax of a trigger right. Unfortunately, I have no DBAs locally, I use some third party software, and for reasons beyond my understanding, I have been told to use triggers, and not stored procedures, so I'm running with it.
The set up:
STRUCTURES table: contains several columns, one of which is the unique ID column.
ASSAY table: contains several rows, also with the same ID column, but can have more than one row per ID (several assays per compound). One column is XC_ASSAYS.
The idea of the trigger is basically: When a row in the ASSAYS table is updated, pull out the ID of the row, then calculate the average of the XC_ASSAYS columns for those rows, and report it to the STRUCTURES.XC_ASSAY column for that row ID.
My best attempt thus far results in compilation errors.
CREATE TRIGGER INHIB_W_ALA_TR
AFTER INSERT OR UPDATE ON ASSAYS
FOR EACH ROW
BEGIN
UPDATE STRUCTURES SET XC_ASSAY = (SELECT AVG(XC_ASSAY) FROM ASSAYS WHERE ASSAYS.ID = :NEW.ID) WHERE STRUCTURES.ID = :NEW.ID
END;
/
The resulting errors are:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
2/190 PL/SQL: ORA-00933: SQL command not properly ended
12/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
[code]...
I don't understand some of the errors, such as why line 2 SQL is ignored (it seems correct?), or I'm supposed to properly terminate the trigger (I've read ; and /, but I'm getting the end-of-file errors when I do so). Tried shuffling syntax and ' or " around - and I can't get it.The body SQL works when I replace :NEW.ID with an actual variable (such as 'NMP12'), but I'm not sure how to pass the ID variable from the updated row into the body. The ID is not updated, but other columns are.
Forms :: Details Should Be Updated From One To Other Table
Mar 19, 2011when the tables are updated, the following detals must be correct to ensure that the links in the affected tables are in place.
PLUPDATE_NEW(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_NEW (PLUP_NEW_BENEF_SAVE_SEQ field value)
PLUPDATE_OLD(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_OLD (PLUP_OLD_BENEF_SAVE_SEQ field value)?
[Code]....
i tried this code, what should i do in the link for this tables?
SQL & PL/SQL :: Data Should Be Updated Back To Table
Aug 16, 2011The 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]....
Replication :: Breaks When Subscribers Updated
May 10, 2012 i am more than ready to RTFM, if any of the members give me the right pointers to proceed. My company has a replication issue, which my manager asked me to check anything i can. This can be pure architectural question where i will have redesign the application, i am also ready to read on any design patterns and change code
Basically, we have a Primary Publisher in US and the Subscriber in UK. I am told that the replication setup is MASTER - MASTER. Any updates in US will update the servers in UK and vice versa. The .Net application is client machine hosted application which does the changes in the local server respective to its geographical domain i.e applications running in U.K will update the U.K server only. Once the update is done, the data is replicated to other server. It works well most of the time, but sometimes when the user is updating the same record in US and U.K at the same time. The Replication breaks, since before updating either server, if the row in the table is in different due to an user update, replication breaks.
point me to any solutions either Oracle Replication design wise or .net application design wise.
SQL & PL/SQL :: Returning Updated Rows With REF CURSOR?
Sep 27, 2013The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?
-- Incorrect indicative example 1.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
RETURNING <updated_rows> INTO v_cur
END;
-- Incorrect indicative example 2.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
END;
SQL & PL/SQL :: Get Last Updated Date When Data Modified?
Jun 20, 2011I 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 RelatedTransaction To Be Updated To Master Table
Feb 16, 2012I have approximately 1200 transaction to be updated to a master table. There are other columns in the master table but only one column is being updated. I would like to use sqlloader if possible or any other efficient means. Those 1200 record is stored in an excel spreadsheet. The col1 of the excel spreadsheet have to match col1 of the master table inorder for update col2 from the excel spreadsheet. Here is an example of the data. My operation system is HPUX and database is Oracle 10g.
Master table
col1 col2 col3 col 4
4238 susan 56e
5879 h698c rich 12g
7091 joyce 34b
0876 mike 25n
7501 k956b robert 87c
9498 angela 67r
3645 doris 92y
excel spreadsheet
col1 col2
9498 a784r
3645 a784r
4238 a784r
7091 a784r
0876 a784r
PL/SQL :: Inserting Data Into Updated Table
Jul 22, 2012I 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:
RMAN :: Incrementally Updated Backups
Jun 7, 2012I have two questions with this script
RUN
{
RECOVER COPY OF DATABASE
WITH TAG 'incr_update';
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE;
}
taken from here [URL]....
1) Does it backup archivers? If not, how to add them?
2) Can this script be run daily for ever?