SQL & PL/SQL :: Difference Between Disable Constraint And No Validate

Aug 22, 2010

Is there any logical difference between DISABLE CONSTRAINT and DISABLE NOVALIDATE, in order to accept any new values into the table regardless of violating anything?

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Enabling Constraint With Enable No Validate?

Sep 5, 2013

Is there any way to enable the constraint with out validating for the existing information.

As We can accomplish the same functionality when we are adding the constraint for the first time.

Ex: alter table scott.emp add constraint fk_deptno foreign key(deptno) references scott.dept(deptno) enable novalidate

The above SQL statement will enable the constraint with out validating for the existing information. So there is possibility of data will be there only in the child table but not in the parent table for existing information.

But in the future it will not allow to do so,if the constraint is in ENABLE staus.So i am just trying to disable the constraint to insert only into child table & trying to enable it with NOVALIDATE option .

But the following exception is coming .

SQL> alter table scott.emp ENABLE constraint fk_deptno enable NOVALIDATE;

alter table scott.emp ENABLE constraint fk_deptno enable NOVALIDATE

ORA-00905: missing keyword

SQL> alter table scott.emp ENABLE constraint fk_deptno NOVALIDATE;

alter table scott.emp ENABLE constraint fk_deptno NOVALIDATE

ORA-00933: SQL command not properly ended

Instead of dropping & recreating the same constraint , is there any alternate way to do this ?

View 3 Replies View Related

SQL & PL/SQL :: Temporary Disable All Constraint In Particular Table?

Jan 7, 2012

i have one table HR.employees

SQL> desc hr.employees
Name Null? Type
----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SQL>

in this table i want to temporary disable all constraint in this employees table

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

Get Table Name / Constraint Name / Constraint Type With Join Processes In String Type

Dec 25, 2007

i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)

View 1 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

SQL & PL/SQL :: How To Make PK Not To Validate

Nov 21, 2011

i am writing a script for inserting rows in a table where primary key of that table consists of 4 columns. I want to add a new row where 3 columns of that table will be same but change the value of the last one. this is giving an error saying that i am violating the unique constraint. I tried to disable that constraint/PK by following statement but still this error is coming

alter table sections_budgets modify primary key disable cascade;

OR

alter table sections_budgets modify constraints sec_bdgt_pk disable cascade;

the insert statement which i am using. The first four columns are part of PK

insert into sections_budgets (SELECT sb.bdgt_cd, sb.bdgt_dpf_cd, sb.bdgt_yr, :sec_code, sb.usf_usr_id,
sb.aty, sb.comaty, sb.comytd, sb.expn_ytd, sb.sys_dt, sb.cat,
sb.start_aty, sb.expn_lmt
FROM sections_budgets sb
WHERE sb.bdgt_dpf_cd = :b_d_code AND sb.bdgt_yr = :year);

is there any other way of inserting records in this table without droping the whole table because it contains too many triggers and constraints?

View 24 Replies View Related

How To Validate SQL Scripts

May 7, 2013

How to Validate SQL scripts?

I am having set of sql files i wish to run one by one if there is any error at one file i need to notify to the user.I have created Bat file in-order to execute in a sequence.how to validate in bat file

say

01.sql ---Sucess
02.sql --- Fail -- intimate and not to execute rest of sql files
03.sql
04.sql

View 34 Replies View Related

How To Validate SQL Run On Database

Feb 26, 2013

We are looking for validating our product upgrade changes. As part of product upgrade, there will be changes to the schema.

Like addition of tables, addition of columns to existing tables, change the length of character columns, add new index, etc. We want to verify on the database, that these SQL are run successfully on the database.

Is there a table/view, that can be queried to figure out whether such sql was run and the status of the execution, after some time (day or two) after these are actually run on the database.

View 4 Replies View Related

SQL & PL/SQL :: Validate Date Format

Feb 27, 2012

How can I check if date is in correct format ? I have In parameter for function. It is date. Example:

2011-11-01

How can I check this format ?

View 3 Replies View Related

Forms :: Set Validate Item From LOV To Yes

May 29, 2011

i have a table called equipment containing engine_type_id.in the equipment form that is based on the equipment table, i added a non database item called engine_type.

i added an LOV for the engine type field, so that the user can choose the engine type from the the egine types table. i made the engine type id hidden. and set the validate item from LOV to yes.on post query trigger, i selected the engine type into the nondatabase field.

the form is working properly.

when i push CTRL F11 (query) and scroll, even if i didnt change anything the form builder asks me , do u want to save changes.

View 2 Replies View Related

SQL & PL/SQL :: Validate Sentence Syntax?

Dec 29, 2010

Is there some way to validate the syntax of sql sentence without actually executing it? I dont want to check if the objects exist or not, just want to check the syntax.

View 6 Replies View Related

Forms :: Validate From List

Mar 25, 2009

I have a non-database text item (name: profession_temp) on a block and it is a also return item for an lov.

When I change the property "validate from list" of the item(name: profession_temp), to YES, and closing the form without inserting, updating, deleting any record , a message is displayed "do you want to save the changes?".

When I change the above property back to "No", the message is not displayed in this case. I wonder why this message appears while there is no change occurred in the underlying records?

View 13 Replies View Related

XML DB :: Create XML Based On Xsd And Then Validate?

Jul 31, 2013

 I want to create an XML based on xsd and then  i want to validate the same xml with xsd ,

View 1 Replies View Related

PL/SQL :: How To Validate Table Data

Aug 13, 2012

I have a table data like this

"VchrNo" "Credit/Debit type" "Amount"
101 1 100.00
101 2 10.00
101 2 10.00
101 2 51.00
101 2 29.75

This data is wrong. Actual DAta should be in any form. But Sum of VchrNo 101 with Credit/Debit Type 1 should be 100 and VchrNo 101 with Credit/Debit Type 2 should be 100 Now this data is begin entered from so many forms.

Is there any way to stop this and raise any error at database level so that culprit form can be found.

View 12 Replies View Related

SQL & PL/SQL :: Add Index Enable No Validate

Nov 1, 2011

I have a table, it has 3 terabytes of data. We are sure whatever the data we have in that table is valid. I need to add the index to table it should be enabled and need not to be validated for existing data.

I am using following command

CREATE INDEX U_IXN_MTRS_ORGIDMTRNOMTRTYPEID ON MTRS (nlssort(METERNO), METERTYPEID) TABLESPACE OLTP_IDX_TS ENABLE NOVALIDATE;

it is giving me problem

ORA-02158: invalid CREATE INDEX option error

View 4 Replies View Related

How To Validate A Sum Of Some Records In A Table To Be Exact Value

Apr 9, 2008

How to validate a sum of some records in a table to be exact value?

I want to guarantee that sum(val_column)=100 at database level. Check constraints out of question. Trigger before update statement disable any update to that column when incrementing one row and decrement another... (unless with for all...)

View 3 Replies View Related

Forms :: Use Of Validate From List Property

Jul 19, 2013

What is the uses of "Validate From List" Property(Yes/No) in LOV's Properties.

View 3 Replies View Related

SQL & PL/SQL :: Write A Trigger To Validate V_status For Each ID

Dec 30, 2010

I want to write a trigger to validate the v_status for each ID. below is the example

SEQ_NO ID V_STATUS

11 C
21 C
31 F
42 C
52 F
63 C
7 3 F
8 3 C

For each unique id there will be only one F .. if some one tries to update the 2nd F to the same ID it will show the error message that ID is already F.

SEQ_NO ID V_STATUS

11 C
21 C
31 F
42 C
52 F
63 C
7 3 F
8 3 F

View 12 Replies View Related

Forms :: How To Validate List Item

Nov 22, 2010

I am working on form 6i..

I have developed a form with some text items,a list item and a image item...

list item......account_list(values....company,individual)
text_item..... company_name..
text_item.....image_path...

i have attached a pop up menu to image item... Add image and upload image...

When i click on 'Add image' the image path is getting enabled.. it is fine but when i am changing the account_list i need enable and disable the 'Company_name' text item... How can i achieve this??

I have tried it in when-validate-trigger on account_list...

if account_list=individual then
set_item_property('company_name',enabled,property_false);
else
set_item_property('company_name',enabled,property_true);
end if;

But no use of this....

how can i achieve it??

View 5 Replies View Related

Forms :: Trigger When Validate Item

Dec 18, 2012

i need to pop up an alert using a trigger to test the data type of a field per example if the input is not a number an alert will popup "Please insert a number"

View 5 Replies View Related

SQL & PL/SQL :: How To Validate Given String For Alphanumeric Character

Feb 14, 2011

To built the Pl/SQL function or SQL query to validate the given string for alphanumeric character.

I would pass the string of size 10 character, we need to validate first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.

I will pass the each row value to the function, it need to return "T" or "F" based on the condition,first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.

Here is the DML and DDL.

[code]

create table abc ( classid varchar(10));

insert into abc values ("abcde1234f");
insert into abc values ("abcde12345");
insert into abc values ("ab1de1234f");
insert into abc values ("abcde1234f");
insert into abc values ("abcd21234f");

[code]

Output:

Input : abcde1234f Output : T
Input : abcde12345 Output : F
Input : ab1de1234f Output : F
Input : abcde1234f Output : T
Input : abcd21234f Output : F

View 6 Replies View Related

Forms :: How To Validate Primary Key Fields In 6i

Mar 5, 2011

i have a emp block with multiple rows in which col1,col2,col3,col4,col5,col6 are the columns...In emp block col1,col2,col3 are the primary key fields.. i need to validate the primary key columns such that it should not allow duplicates values in the primary key col's with out checking the database values with out using enforce primary key property

View 2 Replies View Related

Forms :: Validate After Write In Textitem?

Jun 30, 2011

how to Validate after write every character in textitem to do event ?

exmple :

to make filter on lov from this textitem

when write M ---> lov list all name start with M
when write O ---> lov list all name start with MO
when write H ---> lov list all name start with MOH
os ....

View 3 Replies View Related

Forms :: Validate Time On Field

Aug 22, 2010

I have a varchar field and user will enter the time in that field as per the format HH:MI HOURS:MINUTES.Actually there is no control on this field and user is making mistakes like instead of HH:MI he is entering HH;MI.

View 1 Replies View Related

Forms :: How To Validate Salary Regarding To Its MIN And MAX Value From Another Table

Jun 16, 2012

I have a created data block using employees table so am trying to validate item salary using max_salary and min_salary from table jobs how to do this kind of validation.

[How can i validate input number into a Field of type char in oracle form?]

View 1 Replies View Related

Forms :: Validate Item Trigger?

Aug 22, 2011

i have a text field where i am putting values from an LOV and i want to check for wrong entries like if the user enters a wrong code it should display the message "wrong code".so this is the code i am using in the when validate item trigger:

begin
declare
pc number;
begin
select count(*)

[code]...

But it doesn;t work, it acepts the wrong code also.

View 6 Replies View Related

Forms :: Triggers - Key Next And When Validate Item?

Jan 18, 2006

Suppose if I have defined two triggers Key-next and When-validate-item on the same item.

If my key-next is going to fire first and When-validate-item laterhow can the validation happen if I write a code like go_item('XXXX') in my Key-next.Does it mean that if u write a code in key-next to jump to some other item, no validation takes pLace?

View 14 Replies View Related

SQL & PL/SQL :: How To Validate Dynamic Hours Value Using Decode

Nov 1, 2013

I want to validate dynamic hours value using decode in sql...

If value is greater than 24PM it should display some statement...

if value is less than 0AM it should display some statment...

View 10 Replies View Related

Same Name Db_links Exist How To Validate Db_link

Nov 6, 2013

We use oracle 11g.We have same name db_link(e.g somedblink).I tried to validate link by select * from dual@somedblinkBut, it return ORA-02019 connection description for remote database not found. It need shema name like select * from dual@shema.somedblink ?Or, tnsnames .ora got wrong ?

View 3 Replies View Related







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