SQL & PL/SQL :: Check Constraint To Disallow Number In Varchar2 Column

Apr 21, 2010

I have table customer which contains a column CUSTOMER_FIRST_NAME

CUSTOMER_FIRST_NAME VARCHAR2(50)

What will be sql statement to add a constraint on the CUSTOMER_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers ?

View 34 Replies


ADVERTISEMENT

PL/SQL :: Check If Data Stored In Varchar2 Type Column Is Date Or Not

Jun 29, 2012

Is there a seeded function by which I can check all the rows which stored dates in varchar column.

I have a table say test (test_data varchar2(100));

Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only

INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;

View 17 Replies View Related

SQL & PL/SQL :: Is NOT NULL Needed If A CHECK Constraint Is On Column

Dec 13, 2010

In the below code, do I need the 'NOT NULL' after the 'state char(2)'? I am guessing that I do not need it since I have the CHECK constraint on the column.

CREATE TABLE employee(
id PRIMARY KEY,
first varchar(20) NOT NULL,
middle varchar(20),
[code]....

View 10 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 :: Date Check On A Number Column

May 16, 2011

I have a column defined as Number( 8 ) which is supposed to have date values. I would like to check if all the rows in that table have valid dates. We could use to_date(coulmn_name, 'YYYYMMDD') and catch the rownums for error conditions using pl/sql. I would like to know if we could just do it using sql only and return the row numbers for those that are invalid dates?

View 5 Replies View Related

VARCHAR2 (100) Changes Column To VARCHAR2 (100 Byte)

Oct 24, 2007

i am fairly new in the oracle arena, but what would cause a statement such as

ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL

to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Assertions And Check Constraint?

Feb 8, 2013

what is the difference between assertions and check constraint?

View 9 Replies View Related

SQL & PL/SQL :: Modification Of Existing Check Constraint

May 9, 2012

How can I modify the check constraint without dropping the already existing check constraint.

Example: alter table table_1 add constraint const_chk check (ATTRIBUTE7 in
('READ','UNREAD');

Want to add some TENTATIVE value for the already existing one. I could do that by dropping the constraint

ALTER TABLE table_1 DROP CONSTRAINT const_chk and modifying it later as
alter table table_1 add constraint const_chk check (ATTRIBUTE7 in
('READ','UNREAD','TENTATIVE');

Is it possible to do that with out dropping the constraint?

View 2 Replies View Related

PL/SQL :: ORA-02293- / Check Constraint Violated

Jul 5, 2012

i have a table for example :

CREATE TABLE RMD_2
(
"RMD_ID" NUMBER(10,0) NOT NULL ENABLE,
"ABB" VARCHAR2(16 BYTE),
"ACT_IND" VARCHAR2(1 BYTE) NOT NULL DISABLE,
CONSTRAINT "RMD_2_C1" CHECK (act_ind IN ('Y', 'N')) ENABLE
)

for column ACT_IND their is a check constraint RMD_2_C1 it should only accept either Y or N.

but, when ever i am inserting other values other than Y or N it is accepting it should not accept and it should have to give error message ORA-02293- : check constraint violated.

but it is not happening,it is accepting all other values even NULL value also.my requirement is this column should only accept either Y or N and it should not accept other values.

View 4 Replies View Related

How To Create Check Constraint That Act As Foreign Key

Jul 10, 2012

I have table EMP & DEPT.

Can you give me sample "check" constraint so that when I enter an EMP (with status='active') without matching DEPT entry, the process will fail?

It will allow to enter EMP with status='inactive' only even if no matching DEPT entry. So foreign_key will not apply.

View 21 Replies View Related

SQL & PL/SQL :: How To Add A CHECK CONSTRAINT To Compare Info In 2 Tables

Jan 27, 2011

I need to alter a table to check that the data in a column is contained in a similar column in another table.

I have a STORES table and a STORE_REP table. Both tables have a column REP_ID.

I need to add a CHECK constraint into the STORES table to make sure that the info entered into its REP_ID column matches an entry in the STORE_REP table.

Both have a NUMBER(5) data type.

Will it make any difference if the REP_ID column in the STORE_REP table was originally created with a VARCHAR2(5) data type and was later converted to NUMBER(5), while the REP_ID column in the STORES table was created as NUMBER(5) when that table was created?

View 2 Replies View Related

SQL & PL/SQL :: Conversion Varchar2 To Number

May 14, 2011

I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.

create table A(Col1 number);
Inert into A values(1);
Inert into A values(2);
Inert into A values(3);
Inert into A values(4);

Create table B(Col1 Varchar2(100));
Insert into b value ('1,2,3');

Select * from A where col1 in (select col1 from b)
Error: Invalid Number

Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.

View 7 Replies View Related

SQL & PL/SQL :: How To Convert Varchar2 To Number

Jan 15, 2013

How to convert varchar2 to number data?

View 8 Replies View Related

PL/SQL :: Invalid Number With VARCHAR2

Apr 3, 2013

I keep getting this error when I run my update statement. Here is what the coding looks like. I'm running Oracle 11g.

CREATE TABLE A (
SUPP_CD_EIM VARCHAR2 (20),
SUPP_CD VARCHAR2(20),
DSN_FAC_CD VARCHAR2(5));
[code]......

I want the output to look like

A1_1234
A2_2345
A3_3456

View 2 Replies View Related

Conversion Number(19) To Varchar2(19)

Sep 18, 2009

When I try to convert numeric values � number(19) p.s 111111111111111111, the to_char function returns �1111111111111110000� because the to_char functions doesn�t support precision bigger than 15.

Is there any way to solve it?

View 3 Replies View Related

PL/SQL :: Remove Spaces Between Two Number From A Varchar2?

Oct 18, 2012

I have a question respect to remove spaces from a varchar2.

The varchar2 is '7987451 1234567' and i need that string like '7987451 1234567', because the field has a length of 15.

i try with this, but does nothing

Select TRIM('7987451 1234567') from dual

Other possibilities are find several spaces and try to replace with only one, but would a heavy work (may be with sentences Loop).

This problem is now in our loading processes. Exists some function to replace spaces between numbers of varchar2?

View 2 Replies View Related

SQL & PL/SQL :: Convert Varchar2 To Date Or Number

Jun 18, 2011

I created table Contains then following columns

cheq varchar2(50)
date_due varchar2(50)

and data entry in this columns

cheq 500,1500,5000 all values numbers in this columns

date_due 1-1-2012 , 15-9-2010 all values in this columns date

i want sum the column "cheq"

when used this code but it's not working

select sum(to_number(cheq))
from table_name but the code not working

second column "date_due"

i want search between to date i used this code but also not working

select cloumn1,cloum2
from table_name
where to_char(date_due,'dd-mm-yyyy')
between to_char(date_due,'dd-mm-yyyy') and
(date_due,'dd-mm-yyyy')

but not work

View 13 Replies View Related

PL/SQL :: Automatic Rounding Off Number Datatype While Converted To VARCHAR2

May 29, 2013

I am facing issue related to Number data while it is being converted to Varchar2, it is automatically getting rounded off after 32 decimal place.My database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".

1) create table test18 ( col1 varchar2(10), val Number);
create table succeeded.

2) insert into test18 values ('First', -347026.6408499652467480885711448714129679); -- After decimal 34 digits
1 rows inserted

insert into test18 values ('Second', -347026.64084996524674808857114487141296); -- After decimal 32 digits
1 rows inserted

3) select * from test18;

COL1 VAL
---------- ----------------------
First -347026.6408499652467480885711448714129679
Second -347026.64084996524674808857114487141296

4) As per the requirement, all the columns would need to be concatenated as a single string along with comma delimiter

select col1 || ',' || val as record_string
from test18;

RECORD_STRING
---------------------------------------------------
First,-347026.64084996524674808857114487141297
Second,-347026.64084996524674808857114487141296

"First" string got rounded off to 97 (last 2 digits) instead of 9679 but for "Second" record it holds the actual value.Only thing which I could figure out while the number is getting type casted to String, it is getting rounded off to 32 decimal place.throw off some light on it and provide the solution how the original record can be kept intact without rounding off.

Expected Output_
RECORD_STRING
---------------------------------------------------
First,-347026.6408499652467480885711448714129679
Second,-347026.64084996524674808857114487141296

View 10 Replies View Related

Table Partitioning - Range On Datatype Number (21, 7) And Varchar2

Nov 15, 2013

Database Version : DB : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionOS : HP-UX nduhi18 B.11.31 U ia64 1022072414 unlimited-user licenseAPP : SAP - ERP I have to RANGE partition on UPDATED_ON  or PROFILE  either one table which is having below

structure :   Name                Null?    Type
-------------------- -------- --------------------------------
MANDT                NOT NULL VARCHAR2(9) MR_ID                NOT NULL VARCHAR2(60) PROFILE              NOT NULL VARCHAR2(54) REGISTER_ID          NOT NULL VARCHAR2(30) INTERVAL_DATE        NOT NULL VARCHAR2(24) AGGR_CONSUMPTION     NOT NULL NUMBER(21,6) MDM_VERS_NO          NOT NULL VARCHAR2(9) MDP_UPDATE_DATE      NOT NULL VARCHAR2(24) MDP_UPDATE_TIME      NOT NULL VARCHAR2(18) NMI_CONFIG           NOT NULL VARCHAR2(120) NMI_CONFIG_FLAG      NOT NULL VARCHAR2(3) MDM_DATA_STRM_ID     NOT NULL VARCHAR2(6) NSRD                 NOT NULL VARCHAR2

[Code]....

 As per my knowledge, RANGE is better suited for DATE or NUMBER. and INTERVAL partition is possible on DATE or NUMBEr . Column PROFILEIts is of VARCHAR2 datatype. I know still I can partition as Oracle internally convert varchar2 to number while inserting data. But INTERVAL is not possible.  How to RANGE partition on PROFILE ? Column CREATED_ON :It is of NUMBER with decimal

View 0 Replies View Related

SQL & PL/SQL :: Change Column Datatype From CLOB To Varchar2

Jun 22, 2012

I have to change the datatype of a column from CLOB to varchar2, without changing the order of the columns. The table has no data.
I could find any other way other than dropping the CLOB columns and then adding new columns with varchar2 datatype. But this changes the order of the columns in the table.

View 4 Replies View Related

SQL & PL/SQL :: Multipurpose Varchar2 Column Error When Converting To Date

Aug 16, 2011

i have a varchar2 column containing string values that can be converted to date i.e. ('31-JUL-11') and that column also contains text strings in it. i.e. ('Some string data...')

records whose column value can be converted to date are extractable via where clause (i.e. those rows are associated with some fix number / flag)

now when i try to use to_date function i get the error that

" ORA-01858 a non-numeric character was found where a numeric was expected "

in sql i have added a where clause to only pick rows with flag, but even then it gives the error.

using a subquery in the from clause eliminates the error, but when i create it in a view it again gives the same error.

View 8 Replies View Related

SQL & PL/SQL :: Column Of Table With Data Type Varchar2 - Corresponding Index

Jan 10, 2013

I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column

like '%abc%'
like 'abc%'
like '&abc'

Will be the corresponding index will be for those cases?

View 3 Replies View Related

PL/SQL :: How Rownum Works For The Column Having Data Type As Varchar2

Jan 29, 2013

bookshelf_test table structure

title     varchar2(100)     y          
publisher     varchar2(20)     y          
categoryname     varchar2(20)     y          
rating     varchar2(2)     y

my query is,
select ROWNUM AS "Rank",title,publisher from (select rating,title,publisher from bookshelf_test order by rating desc ) where ROWNUM <=3

returns result ,

1     1     MY LEDGER     KOCH PRESS
2     2     TO KILL A MOCKINGBIRD     HARPERCOLLINS
3     3     THE MISMEASURE OF MAN     W.W. NORTON        

But inner query (select rating,title,publisher from bookshelf_test order by rating desc ) returns,

1     5     WONDERFUL LIFE     W.W.NORTON
2     5     THE MISMEASURE OF MAN     W.W. NORTON
3     5     TO KILL A MOCKINGBIRD     HARPERCOLLINS
4     5     MY LEDGER     KOCH PRESS
5     4     TRUMAN     SIMON SCHUSTER
6     4     GOSPEL     PICADOR
7     4     HARRY POTTER AND THE GOBLET OF FIRE     SCHOLASTIC
8     4     INNUMERACY     VINTAGE BOOKS
9     4     JOHN ADAMS     SIMON SCHUSTER
10     4     JOURNALS OF LEWIS AND CLARK     MARINER
11     4     LETTERS AND PAPERS FROM PRISON     SCRIBNER
12     4     PREACHING TO HEAD AND HEART     ABINGDON PRESS
13     4     THE SHIPPING NEWS     SIMON SCHUSTER
14     4     THE GOOD BOOK     BARD
15     4     THE DISCOVERERS     RANDOM HOUSE
16     3     THE COST OF DISCIPLESHIP     TOUCHSTONE
17     3     SHOELESS JOE     MARINER
18     3     KIERKEGAARD ANTHOLOGY     PRINCETON UNIV PR
19     3     EMMA WHO SAVED MY LIFE     ST MARTIN'S PRESS
20     3     EITHER/OR     PENGUIN
21     3     CHARLOTTE'S WEB     HARPERTROPHY
22     3     BOX SOCIALS     MARINER
23     3     ANNE OF GREEN GABLES     GRAMMERCY
24     3     WEST WITH THE NIGHT     NORTH POINT PRESS
25     3     UNDER THE EYE OF THE CLOCK     ARCADE PUB
26     3     TRUMPET OF THE SWAN     HARPERCOLLINS
27     2     COMPLETE POEMS OF JOHN KEATS     VIKING
28     1     POLAR EXPRESS     HOUGHTON MIFFLIN
29     1     GOOD DOG, CARL     LITTLE SIMON
30     1     MIDNIGHT MAGIC     SCHOLASTIC
31     1     RUNAWAY BUNNY     HARPERFESTIVAL

why final queries top 3 rows r different than inner query ?

View 3 Replies View Related

SQL & PL/SQL :: Create Materialized View With Clob Column Based On Varchar2?

Feb 9, 2011

I need to create a materialized view with a clob column based on a varchar2 column of a table.This is because in the mv the clob column data gets appended one after another.

View 2 Replies View Related

Add Constraint Default Value For Table Column With Name?

Oct 26, 2009

I would like to add a constraint "default value" for a table column, with a name.

I know how to do it for a constraint "not null" : ALTER TABLE tablename MODIFY columnname CONSTRAINT constraintname NOT NULL;

But I don't know how to do it for a constraint "default value".How can I do ?

View 2 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

PL/SQL :: How To Get Constraint Name / Table And Column Names

Sep 12, 2012

I am a junior DBA. I want to check how many columns are under PRIMARY KEY constraint.

I used dba_constraints view. Please find below details.

SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints
2 where TABLE_NAME='DSET_PRODUCT_S';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------------------------------
SCOTT SYS_C10202456 C EMPLOYEE
SCOTT SYS_C234576 C DEPT

View 6 Replies View Related

PL/SQL :: Regular Expression To Check Number With At Least One Decimal Point?

Nov 18, 2013

I would like to use the REGEX_LIKE to check a number with up to two digits and at least one decimal point: Ex.10.11.1112 This is what I have so far.

if regexp_like(v_expr, '^(d{0,2})+(.[0-9]{1})?$') t

View 3 Replies View Related

SQL & PL/SQL :: Entering Values As Not Null Constraint To Column In Oracle Without Disturbing Old Records

Jul 1, 2013

previously i set null constraint to the column and creating some rows and need to change new entering values as not null constraint to the column in oracle without disturbing the old records. how can I do that.

View 5 Replies View Related

PL/SQL :: Insert Number Into Column With Type  NUMBER(10,0)

Sep 28, 2012

my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?

View 6 Replies View Related







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