SQL & PL/SQL :: Creating Composite Unique Key With Only 1 Column Allowed To Be Changed

Jul 3, 2013

I have a table say MY_TAB with columns as below

emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)

insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');

How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.

insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.

View 13 Replies


ADVERTISEMENT

Performance Tuning :: Creating Normal / Composite Indexes On Fields

Feb 14, 2013

I Want to tune the attached query. I have tried by creating the normal indexes and composite indexes on the fields . I feel that , Only normal index is required for this instead of composite index?

11:15:19 SQL> @slot.sql
11:16:03 SQL>
11:16:03 SQL> drop table slot purge;

Table dropped.

Elapsed: 00:00:00.05
11:16:03 SQL>
11:16:03 SQL> create table slot
11:16:03 2 (
11:16:03 3 id varchar2 (40) not null,
[code]....

- dynamic sampling used for this statement
22 rows selected.

View 8 Replies View Related

SQL & PL/SQL :: Copy Related Data From / To Same Tables - Combined With Changed String And Unique ID

Aug 12, 2010

I'm trying to copy a particular set of rows into two tables. I want to change one varchar column to a different string in one of the tables, and create a new unique ID for the copied rows that will match on both tables. I also want that new unique ID to be one digit higer than the current highest unique ID being used across the tables as a whole.

create table testing_query_dtl
(query_is number not null, query_type varchar2(20) not null, area_code varchar(4), stn_code varchar2(3))
create table testing_query_hdr
(user_name varchar2(30) not null, query_indicator varchar2(1) not null
, query_created date not null,
query_last_used date not null,
query_name varchar2(40) not null
, query_is number not null)
[code]...

I then used 'mymax+test_seq.nextval' but that won't avoid the unique constraint issue on the table testing_query_hdr and on the table testing_ query_ dtl increments the ID on EVERY row even when I don't want it to.

View 4 Replies View Related

SQL & PL/SQL :: Composite Index Having Varchar2 Number And CLOB Column

Feb 25, 2012

I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...

[URL]......

Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?

View 11 Replies View Related

SQL & PL/SQL :: ORA-00984 / Column Not Allowed Here

Oct 30, 2010

I have created the following package-

create or replace PACKAGE TABLE_PKG1 AS
PROCEDURE make(table_name VARCHAR2, colspec varchar2);
PROCEDURE add_row(table_name VARCHAR2, value_s VARCHAR2,cols VARCHAR2);
END TABLE_PKG1
CREATE OR REPLACE PACKAGE BODY TABLE_PKG1 AS

[code]....

The package and the package body compiles successfully. When I add table-

BEGIN
TABLE_PKG1.make('EMP_new','country varchar2(20), currency varchar2(20)');
END;

The table is created successfully.However when I invoke the package using the following anonymous block for adding the row-

SET SERVEROUTPUT ON
BEGIN
TABLE_PKG1.add_row('EMP_new','"India","Rs"','Country,Currency');
END;

The it generates the following error-

Error report:

ORA-00984: column not allowed here
ORA-06512: at "SCOTT.TABLE_PKG1", line 14
ORA-06512: at line 2
00984. 00000 - "column not allowed here"

View 3 Replies View Related

Creating Unique Constraint That Excludes Null

May 22, 2013

I have a table where I want user to fill in unique values for a field which is easy to do.

Problem is sometimes the values can be null so an ordinary unique constraint does not work because multiple null records. Is there a way of validating only non null values to ensure all data entered that is non null is unique?

View 1 Replies View Related

SQL & PL/SQL :: Special Character Not Allowed In Column Value?

Aug 8, 2012

I have a table stud_mst In which i have following columns STUD_NAME , ROLL_NO , COURSE

Table values are for e.g

STUD_NAME , ROLL_NO , COURSE
Harshita 1323 BSC
Atul 1734 BCOM
L'Pradhan 3739 BA

Still today user can enter any special character value in stud_name like L'Pradhan . I want to put a restriction on stud_name so that user can enter only alphabetical value into stud_name column.

View 6 Replies View Related

SQL & PL/SQL :: ORA-01733 - Virtual Column Not Allowed Here

Nov 23, 2011

Does know why I am getting ORA-01733- virtual column not allowed here Following :

CREATE TABLE EMPL
(
EMP_ID NUMBER(10),
CNT NUMBER(10)
)
insert into empl(EMP_ID)
values(1)

[code]....

ORA-01733- virtual column not allowed here

View 33 Replies View Related

SQL & PL/SQL :: Value Larger Than Specified Precision Allowed For Column

Mar 21, 2013

I am trying to do bulk insert into a table. Attached is the script I am running:

But when I run this script I get exception : 'ORA-01438: value larger than specified precision allowed for this column'.

I have checked in my soucre table as well as in inserting table, everything looks fine to me.

how to handle such exception, which could actually tell me for what column and what data it is throwing exception.

View 7 Replies View Related

SQL & PL/SQL :: What Should Be Order Of Columns While Creating B-tree Non-unique Index

Jul 19, 2012

What should be the order of columns while creating b-tree non-unique index.

Low cardinality first
or
Hight cardinality first

View 15 Replies View Related

Getting Error - ORA-01438 (value Larger Than Specified Precision Allowed For Column)?

Dec 31, 2012

SQL> create table t51(t5 NUMBER(25,8));

Table created.

SQL> insert into t51 values (100000000000000000.00000);
insert into t51 values (100000000000000000.00000)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

View 5 Replies View Related

SQL & PL/SQL :: Column Names Are Changed

Jul 21, 2011

when we decribe one table structure, it shows the column name as below:

DUKEDMTS03DB01:SYS@TODS1> desc uet_rep.ECM_MAINTENANCE

Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NOT NULL VARCHAR2(15)
C2 VARCHAR2(254)
C3 NOT NULL NUMBER(15)
C4 VARCHAR2(254)
C5 NOT NULL VARCHAR2(254)
C6 NOT NULL NUMBER(15)
C7 NOT NULL NUMBER(15)
C8 NOT NULL VARCHAR2(254)

[Code]...

But, till yday it was showing the orignal column name..

View 3 Replies View Related

Forms :: How Column Name Of A Database Can Be Changed At Runtime

Feb 20, 2012

My form has to go into two different environment in which the one column name of a table is different. datatype is same.

I would like to change the database column name of the item in the form for that block at run time.

View 2 Replies View Related

SQL & PL/SQL :: How To Get Unique Value In Column

Oct 25, 2012

i am using one query which should return unique value. I have one table that has one column punch date datatype is date.

here value is stored in two rows that is

1. 24-10-12
2. 24-10-12

and my query is this:

SELECT distinct(PunchDate) FROM Trans_RawProcessDailyData ORDER BY PunchDate ASC;

but still getting two values.

View 11 Replies View Related

SQL & PL/SQL :: How To Add Column In Unique Index

Jan 17, 2011

I have created one unique index on one column of my table. Now i would like to add one more column in the same index without dropping the index.

SQL > CREATE TABLE DEBUG_TABLE
2 (
3 SLNO NUMBER,
4 MESSAGE VARCHAR2(4000 BYTE),
5 CREATED_DATE DATE DEFAULT SYSDATE,
6 CREATED_TIME TIMESTAMP(6) DEFAULT SYSDATE
7 );

Table created.

SQL > CREATE UNIQUE INDEX index_debug1 ON debug_table (SLNO);

Index created.

SQL > ALTER INDEX index_debug1 ADD COLUMN MESSAGE;
ALTER INDEX index_debug1 ADD COLUMN MESSAGE
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL >

View 6 Replies View Related

PL/SQL :: Unique Values Of Each Column

Aug 21, 2013

I have a problem .I have a oracle table its column count more then 500 . I want unique values of each column and insert one table.

View 44 Replies View Related

SQL & PL/SQL :: Unique Constraint When 1 Column Is Not Null

Jun 15, 2012

I have 3 columns in a table: colX, colY, colZ.

Trying to find a way to prevent duplicates with these, but only if colX is not null.

For example, if there are already values for: colX = 1, colY = 1, colZ = 1

then:

Allowed: colX = null, colY = 1, colZ = 1
Not allowed: colX = 1, colY = 1, colZ = 1

I can't create a unique constraint on these columns because there are many null values for column colX, and as mentioned, when colX is null, colY and colZ can be any values.

I also tried using a before insert trigger to find duplicates before posting and raise an error if found, but this causes an ORA-04091 mutating error since the trigger in the table is referencing itself to check for duplicates.

Also, I know there is something called a function based index, but I cannot use those with my code, so I need another solution if possible.

View 4 Replies View Related

SQL & PL/SQL :: Multiple Unique Values In One Column?

Aug 26, 2011

I have a problem with some tables in database.

Table has three columns: userid, pname, pvalue.

Userid has unique values, for example: 234, 123, 587, etc.

In the field pname, there is three possible values: MAC, IP, S/N.

So if I go like this:

select pvalue
from table
where pname = 'MAC';

i get the values of MAC.

If I go like this:

select pvalue
from table
where pname = 'IP';

i get the values of IP.How can I join MAC with IP that is matching this MAC? I need an SQL statement for this.

View 11 Replies View Related

SQL & PL/SQL :: Updating Column With Unique Values Without Using Sequences

Apr 17, 2013

i am having a table with out pk along with data.

Now , i need to add one column to that table , and update this column with the sequence no

like 1,2,3...... upto the max no of records. but i have to do this with out using a sequence.

how can i do.

View 1 Replies View Related

ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jan 14, 2013

I'm trying to make a foreign key against a table which has a unique index instead of a primary key.

i get this error:

Error SQL: 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.

my question is (i've searched several times with no results) can i create a foreign key with a table wich has no primary key but a unique index ?example:

CREATE TABLE TABLE1(
IDTABLE1 NUMBER(5),
NAME VARCHAR2(30)
);
CREATE TABLE TABLE2(

[code]....

or TABLE1.IDTABLE1 must be a 'Primary Key' instead of unique index ?I know it should be primary, but i need to know if it would work with the index somehow. The reason is i'm migrating a large database and the original structure in mysql uses fk with indexes and no pks in some tables (48 to be precise)

View 11 Replies View Related

SQL & PL/SQL :: Column DA_OBJECT_NAME Is Not Unique / Same Object Name Can Have Multiple Entries

Jul 22, 2011

I have a table DN_ACTIONS with 9.5 million records. Column DA_OBJECT_NAME is not unique and same object name can have multiple entries. Column DA_ACTION_STATUS can have any values between 1 to 5.

I want to find all the DA_OBJECT_NAME in DN_ACTIONS where all of its entries has DA_ACTION_STATUS=3.

CREATE TABLE DN_ACTIONS
(
DA_ID NUMBER,
DA_OBJECT_NAME VARCHAR2(192 BYTE),
DA_ACTION_STATUS NUMBER(1)
);

best way as it does a self join on big table of 9.5 million.

(select distinct a.DA_OBJECT_NAME from DN_ACTIONS a where not exists ( select distinct DA_OBJECT_NAME from DN_ACTIONS b where a.DA_ OBJECT_NAME = b.DA_OBJECT_NAME and b.DA_ACTION_STATUS != 3))

What are the better ways to rewrite this query?

View 2 Replies View Related

Reports & Discoverer :: Get Unique Values With Formula Column

Jul 5, 2013

I am facing a problem in leave_form report! want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP' repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'

problem is report is showing 22 records of leavetype 'CL' of same leavedate!

i want that report should show the actual leavedate and leavetype records.using 9i database 6i developer server2003

For leavetype

FUNCTION Cf_1formula
RETURN CHAR
IS
v_leavetype CHAR(40);
BEGIN
SELECT LT.description
INTO v_leavetype
FROM hrm_attendance L,
hrm_leavetype LT,
hrm_employees E
[code]....

View 2 Replies View Related

Server Utilities :: ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jul 6, 2010

I was cloning a schema user1 as user2 in the same database.

user1 had quota on 2 tablespaces user1_data and user1_index.

I created user with name as user2.

I created tablespace user2_data only and granted user2 unlimited quota on that tablespace only (did not grant him 'resource' role or unlimited tablespace privilege) Now exported user1 schema as follows

exp system/<passowrd> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user1_schema_exp.log owner=user1 rows=y constraints=y triggers=y indexes=y statistics=none recordlength=65535 compress=no consistent=n grants=y

then imported in in user2 schema as follows

imp system/<password> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user2_schema_imp.log fromuser=user1 touser=user2 rows=y constraints=y indexes=y statistics=none recordlength=65535 grants=y

during import i encountered following errors for so many constraints

"ALTER TABLE "table2" ADD CONSTRAINT "constraint_name1" FOREIGN KEY ("CTR_ID") REFERENCES "table1" ("CTR_ID") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:

I found that the it happened as the primary key of table1 was not created for which error was logged in the log file

. . importing table "table1" 19441 rows imported
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "table1" ADD CONSTRAINT "T1_PK79" PRIMARY KEY ("CTR_"
"ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER1_INDEX" LOGGING ENABLE "
. . importing table "table5" 0 rows imported

However, I checked that the T1_PK79 does not exist in the user2 schema though it exists in user1 schema Neither the index for priamry key (T1_PK79) existed in user2 schema not the table <table1> existed before this import Then what could be the reason that I am getting an error "IMP-00015: following statement failed because the object already exists"?

I assume tablespace for index would not be an issue here as other indexes got created properly in user2_index tablespace during this import.

I tried this twice, once with user2 schema and then with user3 schema as well (with different tablespace), but result is the same.

There were no users connected to the database during export and no background jobs were modiying any data in schema user1 while export.

View 4 Replies View Related

Non Unique Index Using A Non Unique Field

Aug 14, 2013

Using Oracle 11g, below is the table, partitions, unique and non-unique local index: 

CREATE TABLE DOCA(  DOCA_ID  NUMBER  NOT NULL ,  DOCA_BKG_PAX_ID  NUMBER  NULL ,  ROW_PURGE_DATE  DATE  NULL ,)PARTITION BY RANGE(ROW_PURGE_DATE)INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))(  PARTITION P2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'dd/mm/yyyy')),  PARTITION P200801 VALUES LESS THAN (TO_DATE('01/02/2008', 'dd/mm/yyyy')),)  TABLESPACE T0; ALTER TABLE DOCA ENABLE ROW MOVEMENT;    

CREATE UNIQUE INDEX XPKDOCA ON DOCA(  DOCA_ID  ASC,  ROW_PURGE_DATE ASC)LOCALREVERSE TABLESPACE I0;  ALTER TABLE DOCA  ADD CONSTRAINT  XPKDOCA PRIMARY KEY (DOCA_ID);    CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA(  DOCA_BKG_PAX_ID  ASC)LOCALREVERSETABLESPACE I0;  

I would like to know the difference between the performance of the unique and non-unique local indexes?.

View 10 Replies View Related

Creating Index On Hashed Column

Sep 23, 2010

I currently have a 5 column index on a table with over 2 billion records (paritioned on created_date (weekly) that is not very effective.I am contemplating replacing this 5 key index and creating a new single column index made up by hashing of all the 5 five columns.

Is this a wise stratgey? How can I implement this so it is most effective and I dont shoot myself in the foot?

View 4 Replies View Related

Creating A Multi Column Index

Jan 24, 2012

I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.

the way to create multi_column indexes on the table?

What are the rule to define the best-position of a column in an index?

index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?

View 4 Replies View Related

PL/SQL :: Error When Creating A Table / ORA-00957 - Duplicate Column Name

Mar 23, 2013

I'm getting an error as follows .

create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
where
a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM
and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy')
and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy')
and a.SRC_SYS_DESC = 'FACET');
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
*
ERROR at line 1:
ORA-00957: duplicate column name

View 1 Replies View Related

Application Express :: Creating Link In IR Report Passing Value From Different Column

Feb 19, 2013

Version 4.0.2.00.07

I have an Interactive Report with a column that I need to create a link on but I need to pass the value of another column.

This report shows the Features of a particular Release. There is a select list for the Release that is selected and executes the report. There is a column that indicates whether the Feature has a Commitment attached to it. If there is have that a link to another report that shows all the Commitments for that Feature by passing the FEATURE_ID which is also a column on the Feature report.

Query:
SELECT
....
,CASE WHEN ft.feature_id IN (SELECT cc.feature_id
FROM customer_commitments cc
WHERE ft.feature_id = cc.feature_id
) THEN 'Yes' ELSE 'No'
END "Commitment Exists"
,ft.feature_id
....

FROM ....If "Commitment Exists" has a 'Yes', the 'Yes' is a link that will pass the FEATURE_ID to another report that will show the Commitments for that Feature. If the "Commitment Exists" has a 'No' then no link. I was also wondering if the Commitment report could open in a seperate window and make it modal?

View 7 Replies View Related

Reports & Discoverer :: Creating Data Link Using A Column Only Selected In One Group

Dec 14, 2010

I have a report with 2 groups Gheader and Glines.The report looks at PO headers and lines. I want to create a data link from the the 2 queries based on the line id in po_lines_all.However I only want to select this in the lines query so I do not get repeating records at the header query.

View 11 Replies View Related

SQL & PL/SQL :: Example Of Composite And Candidate Key

Jul 25, 2013

concept of composite and candidate keys. i have gone through all the definition part but did not understand the concept of creating candidate key for example:-

composite key

create table test(id number,pincode number(4),name varchar2(15),primary key(id,pincode));

table created with composite bcz i took pair of id and pincode.

here base on the same example want to create candidate key with the syntax.

View 24 Replies View Related







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