SQL & PL/SQL :: Maintaining Aggregate Column Data Integrity?

Jun 8, 2012

What is the best practices to maintain aggregate columns? Suppose I have the following 2 tables:

DROP TABLE TEST.ORDERS_DET_T;
DROP TABLE TEST.ORDERS_T;
CREATE TABLE TEST.ORDERS_T (
ID NUMBER NOT NULL PRIMARY KEY,
ORDER_CODE VARCHAR2(100) NOT NULL,

[code]....

I want the following test script to act int the way, described in comments. Basically this means that the sum of TEST.ORDERS_DET_T.ORDER_DET_SUMA must be equal to TEST.ORDERS_T.ORDER_SUMA after transaction commits.

INSERT INTO TEST.ORDERS_T(ID, ORDER_CODE, ORDER_SUMA) VALUES(1,'FRUITS',100);
INSERT INTO TEST.ORDERS_DET_T(ID, ORDERS_T_ID, ORDER_DET_CODE, ORDER_DET_SUMA) VALUES(2,1,'APPLES',40);
INSERT INTO TEST.ORDERS_DET_T(ID, ORDERS_T_ID, ORDER_DET_CODE, ORDER_DET_SUMA) VALUES(3,1,'PEAT',60);
COMMIT; --SHOULD BE OK, 40+60=100

[code]....

P.S. Creating views based on ORDERS_T and ORDERS_DET_T are not an option, if the user would still be allowed to modify data in tables (as in test scenarios). This is because of current business situation, where the client has 2 teams (outsourcing for back-office solution and insourcing for web solution) that are accessing/modifying the data in the same tables.

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Maintaining Data Integrity When Single Table Split Into Two

Aug 7, 2013

I've an Oracle Table which has around 300 columns. I've a requirement to split this single table into two tables (150 columns each) by a foreign key.

Now I want to know how to maintain the data integrity while I insert the data into two tables. which means each table should have equal number of rows as we insert the 300 columns data into tables each at a time.

View 8 Replies View Related

Apply Referential Integrity To Only Selected Rows Of Particular Column?

Mar 23, 2011

Can I apply Referential Integrity to only selected rows of a particular column? This is the reference key to the primary key to another table. But, the issue is, this reference column is not having mandatory data for all the rows. So, whenever this is null, I dont want it to be referred by parent table.

no, and it makes no sense to do so either

We have an appointment form in our HIS, where patients take telephonic appointment. That time they may not know their user id given by hospital. So, it remains blank & name is entered manually. But if the user-id is entered, it must fetch patient name from the master.

The candidate key must be unique within its domain.The candidate key can not hold NULL values.

ALTER TABLE HLTHCHKAPPOINTHD ADD (
CONSTRAINT SYS_C007145
FOREIGN KEY (N_PATIENTMR_ID)
REFERENCES MRREGISTRTNHD (N_PATIENTMR_ID));

MRREGISTRTNHD is a patient master & has a primary key named N_PATIENTMR_ID.
HLTHCHKAPPOINTHD is a appointment table & has a foreign key named N_PATIENTMR_ID which references N_PATIENTMR_ID of MRREGISTRTNHD.

Also, N_PATIENTMR_ID of HLTHCHKAPPOINTHD is not a unique key & it can contain null values also. I want to define constraint or any other method such that only the not null values are referenced to the master i.e. it should validate in the master. And null values should skip this reference.

Now, what happens is due to this constraint, when I'm trying to edit & update the rows having null value in N_PATIENTMR_ID, it gives the following error.

ORA-02291: Integrity constraint (SYS_C007145) violated - parent key not found

So, can I give some condition in the above constraint saying, apply this constraint to table HLTHCHKAPPOINTHD only having the not-null values in N_PATIENTMR_ID coulmn?

View 2 Replies View Related

OEM / Unknown Encryption / Data Integrity Algorithm?

Jul 2, 2010

We are attempting to configure/use OEM (Oracle 10.2.0.4) on Solaris, and when logging in to OEM we are directed to the Database Down page, stating Enterprise Manager is unable to connect to the database instance, but showing all the components as up/open. After checking the log, I found the following error:

IO exception: Unknown Encryption or Data Integrity algorithm

I checked the settings in emoms.properties and compared the encryption parameters to those in our SQLNet file, and all match up. We are NOT using Grid Control, just db control connecting to 1 instance.

View 9 Replies View Related

RAC & Failsafe :: OCR Data Integrity Failed On Second Node?

Sep 28, 2011

What could be the reason for Such Errors.?OCR Integrity was Successful on one node and on the cloned node it was failed.

View 8 Replies View Related

SQL & PL/SQL :: Maintaining Log Table

Jan 22, 2013

The below procedure is scheduled using DBMS_JOB.

I want to maintain log table for the actions performed by this procedure and as well as the job information.

CREATE OR REPLACE PROCEDURE load_proc (P_ID IN NUMBER)
IS
CURSOR C
IS
SELECT GROUP_ID, tablename, target_table
FROM temp_table
WHERE GROUP_ID = p_id;

[Code]..

I want to capture the below information in the log table.

SNO RECORDS_INSERTED RECORDS_DELETED JOBID JOB_START_TIME JOB_END_TIME JOB_LAST_RAN_ON JOB_STATUS
1 2000 records inserted into 2000 records deleted from 1 20/01/2013 00:00:00 20/01/2013 02:30:00 20/01/2013 02:30:00 completed successfully
table WEDB_EMPLOYEE table WEDB_EMPLOYEE
2 1 21/01/2013 00:00:00 21/01/2013 01:00:00 Failed: error message
3 1 NULL

If the job is not started on the particular day NULL value has to be inserted in the JOB_LAST_RAN_ON column.

If the job is not failed in the middle on a particular day FAILED: ERROR MESSAGE value has to be inserted in the JOB_STATUS column.

Script for log table creation.

CREATE TABLE log_load_proc
(sno NUMBER,
records_inserted VARCHAR2(4000),
records_deleted VARCHAR2(4000),
jobid NUMBER,

[Code]....

View 1 Replies View Related

Server Administration :: Increase Interval For Date Ranges - Maintaining Partitions?

Jun 3, 2010

I am studying Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2),

ORA-14767: Cannot specify this interval with existing high boundsby giving an example other than given in the above mentioned document.

the example given in the document is as follows:

To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:

CREATE TABLE transactions
( id NUMBER
, transaction_date DATE
, value NUMBER)
PARTITION BY RANGE (transaction_date)

[code]/...

View 3 Replies View Related

Networking And Gateways :: Maintaining Single Listener In Multi-instance Database Server With 3 Different Oracle Home

Sep 29, 2011

We've been administering a multiple instance production dB server with 3 different versions of Oracle installed.Currently, each of Oracle version had corresponding listener.Oracle 9i had 2 instances, 10g 6 instances & 11g 2 instances also.how I can integrate this 3 listeners into 1.

View 2 Replies View Related

Referential Integrity Constraint?

Mar 23, 2011

Can I apply Referential Integrity to only selected rows of a particular column? This is the reference key to the primary key to another table. But, the issue is, this reference column is not having mandatory data for all the rows. So, whenever this is null, I dont want it to be referred by parent table.

View 4 Replies View Related

SQL & PL/SQL :: Naming Integrity Constants

Feb 24, 2013

How is naming Integrity Constants useful? See example below:-

create table incrr
(incrid number(4) constraint incrid_pk primary key,
incrdate date not null,
incramt number(8,2) not null
);

In the above if I don't use incrid_pk it also works.Then what is the use of incrid_pk ?

View 6 Replies View Related

Row Violations Due To Referential Integrity Constraint?

Feb 2, 2012

Referential Integrity is very important in Oracle database. Consider a scenario where constraints were applied to a database but never enforced on. Only application code was leveraged over the past 1 year to manage this logic.

Over the course of the year there might have been lot of Row Violations which might have led to Data Inconsistency Issues.Do we have a Script, Tools in Oracle which flag all row level violations with referential integrity now being turned on.

View 1 Replies View Related

SQL & PL/SQL :: Execution Model For Triggers And Integrity Constraint Checking

Sep 7, 2010

URL....Topic: The Execution Model for Triggers and Integrity Constraint Checking

Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:

1.Run all BEFORE statement triggers that apply to the statement.
2.Loop for each row affected by the SQL statement.
a.Run all BEFORE row triggers that apply to the statement.
b.Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)
c.Run all AFTER row triggers that apply to the statement.
3.Complete deferred integrity constraint checking.
4.Run all AFTER statement triggers that apply to the statement.

View 5 Replies View Related

RAC & Failsafe :: Oracle CRS Failure / Rebooting For Cluster Integrity

Sep 7, 2013

I got following error on Oracle 10.2.0.4.0 RAC on solaris 10

Oracle clsomon failed with fatal status 12.

Oracle CRS failure. Rebooting for cluster integrity.

I think this problem in 11g but I am using 10g.

View 14 Replies View Related

SQL & PL/SQL :: Integrity Constraint Checking / Trigger Execution Sequence

Mar 25, 2010

i have an understanding about Integrity constraint checking and the trigger execution sequence, which i decsribe below.

Integrity constraint are restiction on DML operation performed by the user. When a user deletes or updates or inserts a rows in a table then oracle performs certain checking to see that the data which is effecting the row abide certain rules. There are certain per-defined set or rules that can be applied on a table such as PRIMARY KEY, FORIEGN KEY, UNIQUE, CHECK, NOT NULL etc, user-defined rules can be applied on tables by using Triggers.

In both the cases the Integrity Constraint Checking is deferred until the complete execution of the statement. All rows are inserted first, then all rows are checked for constraint violations.

So when i see the trigger execution model the following steps are performed by oracle, Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking..This is what the Oracle Documentation Library says [extract from Oracle Database Concepts 10g Release 1 (10.1)].

1. Run all BEFORE statement triggers that apply to the statement.

2. Loop for each row affected by the SQL statement.
a.Run all BEFORE row triggers that apply to the statement.
b.Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)
c.Run all AFTER row triggers that apply to the statement.

3.Complete deferred integrity constraint checking.

4.Run all AFTER statement triggers that apply to the statement.

As for step 3 here the checking of the constraints for the statement is performed which where defered till the complete execution of the statement, then what is done in step 2b? what constraints are checked there?

View 33 Replies View Related

Installation :: 11g2 On CentOS - How To Fix Oracle Restart Integrity

Aug 3, 2012

I install oracle 11g2 on centos, in prerequisite checks step ,oracle database show this failed "Oracle Restart integrity Failed", I don't know what should i do ?

View 2 Replies View Related

Methods To Check Index - How To Control Internal Integrity

Jun 28, 2012

How to control internal integrity (lack of self-reference keys in an index) without using regular validation and rebuilding ?

I'm using 10.2.0.5.

View 7 Replies View Related

PL/SQL :: ORA-02291 - Integrity Constraint Violated And Parent Key Not Found

May 31, 2013

i am running the query from which i am getting below mentioned error how can i find the record which is not there in parent table ora02291 integrety constrain violated and parent key not found.

View 11 Replies View Related

SQL & PL/SQL :: Aggregate Between Two Dates

Jul 13, 2010

I need to aggregate the data based the two dates criteria.

I have two tables

1. Table1
Number Date1 Date2
1 10-Jun-2010 30-Jun-2010
2 10-Feb-2010 30-Feb-2010
----------------------------------
2. Table2
Number Date Revenue
1 11-Jun-2010 100
1 09-Jun-2010 100
1 12-Jun-2010 100
2 11-Feb-2010 100
2 12-Feb-2010 100
2 13-Feb-2010 100
......................
......................
So on

Output:

Number Revenue Date2
1 200 30-Jun-2010
2 300 30-Feb-2010

View 14 Replies View Related

Aggregate Two Columns

Oct 25, 2011

I have two table and I want to merge them

TERMS_TABLE
ID | TERMS
309 | 'hardware'
309 | 'software'
309 | 'computer'

TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....'

Now I want to add TERMS column of TERMS_TABLE to terms column of TFIDF_TABLE but If TFIDF_TABLE already contains TERMS of TERMS_TABLE then I should not insert this term to the NEW_TFIDF_TABLE , like that

result should be:

NEW_TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....,hardware,software'

How can I do that ?

View 1 Replies View Related

Server Utilities :: Datapump Import And Referential Integrity Constraints

May 23, 2012

I am using Datapump import using database link to import an entire schema from another Server but it gives issues with constraints.I tried to first only import the metadata and then disable the constraints and import data and enable constraint but in this case the temp tablespace keeps filling up and i am out of space. Is there any method to do a full import including constraints and indexes.

View 7 Replies View Related

PL/SQL :: Foreign Key - Creating Table Based On Some Integrity Constraints - Not Working?

Dec 20, 2012

Am creating a table based on some integrity constraints, but it's not working.

CREATE TABLE member
(
member_id NUMBER(10),
last_name VARCHAR2(25) NOT NULL,
first_name VARCHAR2(25),

[code],,,

Error:

Error report:
SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNScatalog view

View 4 Replies View Related

RAC/ASM Clusterware Installation :: OLR Integrity Error During Oracle Database Install

Sep 12, 2012

OS=Redhat 5
Oracle=11gr2
Grid=11gR2

I am trying to install Oracle Database 11gR2 database on Oracle Grid 11gR2. I successfully installed Grid without errors. However, when installing the database, I got an OLR Integrity error during the prerequisit Check

OLR Integrity - This test checks the integrity of OLR on the local node.
Expected Value : n/a
Actual Value : n/a
List of errors:
- Permissons of file
"/u01/app/oracle/product/11.2.0/grid/cdata/localhost/localhost.olr" did not match the expected value. [Expected = "0600"; Found = "0660"]
- Cause: Cause of Problem Not Available
- Action: User Action Not Available.

View 1 Replies View Related

Tuning Aggregate Queries?

May 1, 2011

I have performance problem with 7 queries involving groupby clauses in OLAP database.These are queries triggered during siebel DAC run

kumar[size="4"][/size][color="#0000FF"][/color]kumardba

View 5 Replies View Related

SQL & PL/SQL :: Aggregate Functions On A Join

Nov 7, 2012

I am trying to Show all Customers (custId, last name, first name) who bought cars at the Arctic AND Pacific branches and Order it by CustId.

These are the tables:

VIN CARTYP BRNAME BUYERID SALEDATE
------- ------ ---------- ---------- -----------
1JJ0001 CT9015 Arctic
1NN0001 CT9014 Arctic 10010 18-mar-2012
1PP0001 CT7013 Arctic 10007 25-sep-2012
1QQ0001 CT7012 Atlantic 10014 22-dec-2009
1RR0001 CT7011 College 10013 30-sep-2012
2JJ0001 CT9015 Pacific
2NX0001 CT9014 Pacific
2PP0001 CT7013 Arctic
2QQ0001 CT7012 Arctic

[Code]...

CUSTID LNAME FNAME BALANCE_DUE
---------- ------------ ------- -----------
10001 Disney Daisy 9000.5
10010 Dupont Caryn 40000
10006 Gordon Diane 75000.5
10005 Gordon Tracy 3000.25
10013 Gross Henry 50000
10015 Hilton Kate 40000
10011 Lea Diane 12000
10003 Lopez Athena

[Code]...

No matter how I write the query, I get the message that no rows have been selected.

View 3 Replies View Related

SQL & PL/SQL :: List Function (Aggregate)

Dec 13, 2010

I have one table emp with columns id , name and dept. i want query to get the list like this........

ID name did
1 ajay 10,30,40,60
2 sree 10,30,70

View 7 Replies View Related

Server Administration :: ORA-02291 / Integrity Constraint Violated - Parent Key Not Found

Oct 13, 2010

I have Oracle 9i and yesterday I wanted to create new DB(datebase) but I couldn't did it.

In Database Configuration Assistant chose Create database then New database. Used configuration by default. On 92% I got this error

Quote:ORA-02291: integrity constraint (SH. COSTS_PRODUCT_FK) violated - parent key not found

View 2 Replies View Related

SQL & PL/SQL :: Row To Column / Column Data Can Be Varied From 1 To Any Number Of Words

Mar 21, 2013

I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.

eg

id description
78664 Pumps Alarm from CAMS RTU154

In the above example this column has 5 word so i want data in 5 rows like below

78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154

This column data can be varied from 1 to any number of words.

View 11 Replies View Related

Aggregate Function - Combine Results

Nov 24, 2009

I have a query more or less like this:

SELECT field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY field1;

For field1 I want to get a count, but if field1 is like 'ABC%' then I want to combine all of those.

So if I have the following:
ABC1 | 5
ABC2 | 10
XYZ1 | 3

I want results like this:
ABC | 15
XYZ1 | 3

I've tried using some case statements like

SELECT CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END AS field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END;

but this just gives me
ABC | 5
ABC | 10
XYZ1 | 3

How can I combine record 1 and 2 from the last record set example above?

View 4 Replies View Related

SQL & PL/SQL :: De-Aggregate Year To Date Figures?

Apr 27, 2011

I have a table as below:

Year Month Value
2011 01 15
2011 02 26
2011 03 34

[code]...

The value is an aggregate Year to Date Figure And I was wondering what the best method of splitting this data out into a Monthly Figure so that it would look like below:

Year Month Mth Value
2011 01 15
2011 02 11
2011 03 8
2011 04 9

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Aggregate Contiguous Range Of Days?

Oct 21, 2010

I intend to get for every client the start date and end of a contiguous range of days. Example for the same client have two records, in the first goes from day 1 to day 5 and the second from day 3 to day 9, i intend to get a record for this client where indicated that the start date is on day 1 and ending on Day 9.

SELECT 123 as CLI_ID, TO_DATE('20100101', 'YYYYMMDD') as DT_START, TO_DATE('20100105', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100208', 'YYYYMMDD') as DT_START, TO_DATE('20100321', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100219', 'YYYYMMDD') as DT_START, TO_DATE('20100228', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100227', 'YYYYMMDD') as DT_START, TO_DATE('20100405', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100901', 'YYYYMMDD') as DT_START, TO_DATE('20101013', 'YYYYMMDD') as DT_END FROM DUAL

Expected :

CLI_ID,DT_START,DT_END
123,01/01/2010,05/01/2010
123,08/02/2010,05/04/2010
123,01/09/2010,13/10/2010

View 2 Replies View Related







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