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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
Jul 19, 2013
What is the uses of "Validate From List" Property(Yes/No) in LOV's Properties.
View 3 Replies
View Related
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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
May 26, 2011
I am trying to write a procedure using when-validate-record that will clear all fields, display a message and ignore all commits if user is not authorized to have access to make changes to the form.
I have tried to use Clear_form(no_validate)and clear_record and neither works
View 6 Replies
View Related
Mar 1, 2012
I am using when validate trigger in the item level for baserate, markup, and rate my requirement is
1.if markup and rate are null then all the trigger should fire which means baserate value should show calculating entered markup in the rate field.
2.if the rate is change markup should get change as per the entered rate
3. when second time the baserate is changed only markup should change not the rate.
View 3 Replies
View Related
Feb 16, 2011
want to validate a field to be a valid email id.
View 1 Replies
View Related
Mar 6, 2012
i need to delete a record from table but it showed a error for foreign key constraint so i disabled the constraint and again deleted, now the row is deleted.
Again inserted another values instead the deleted value. after that i tried to alter the constraint but i received the error ORA-02298 cannot validate - Parent keys not found
alter table t1 enable constraint FK_T1;
But i am not able to enable the key. what to do?
View 6 Replies
View Related
