PL/SQL :: Updating A Column Randomly With Selected Values?
Sep 26, 2013
We've an accounts table that basically represents hotel chains & brands. an example shown below.
account_id chain_id brand_id service1
NULL NULL 111
NULL NULL 122
NULL NULL 11
Here I want to update the chain_id & brand_id which are currently NULL in order to make every row eligible for further processing.
There is another table(say chain_brand) which maintains the relationship between chain_id and brand_id. one chain_id can have multiple brand_ids eg.,
chain_id brand_id
101 2011 101
2012 102 2020
Now I need a script that could randomly pick values from chain_brand table and update the accounts table. condition is those values should be unique for an account_id eg.,
account_id chain_id brand_id service1
101 2011 111 101
2011 122 102 2020 11
so each account can be attached to only one chain_id and one brand_id.
View 2 Replies
ADVERTISEMENT
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
Jul 9, 2013
I am working on Oracle forms 10g (Windows 7 OS).I have got one requirement to show one LOV for COLOR table. Say i have three colors BLACK, RED, BLUE in color table. Suppose in a multi record block if a user selects BLACK from a lov for one record then for the next record lov it should show only RED and BLUE. How to filter the COLOR from a LOV record group query which are already selected?
View 1 Replies
View Related
Apr 16, 2013
I am learning oracle trigger, i have one query.
Can i increment the old column value in trigger.
eg: :new.cid := :old.cid+1;
is this is correct.
View 4 Replies
View Related
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
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
Feb 16, 2011
The requirement I have is :
I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)
update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))
It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.
View 1 Replies
View Related
Nov 5, 2011
I have a table(PSUSEROBJTYPE) with a long field(PTCUSTFORMAT) containing a row value value in the form:
#1|0|0|0|0|#2|1|0|0|1|#3|1|0|0|0|#4|0|0|0|0
Here, I want to update the above field value to a value in the form:
#2|0|0|0|0|#3|1|0|0|1|#4|1|0|0|0|#5|0|0|0|0
This is nothing but finding each occurrence of (#n) in the above string and replacing it by (#n+1). (i.e #1 is replaced by #2,#2 is replaced by #3).
View 4 Replies
View Related
Jul 15, 2013
I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.
The block has 5 items as follows:
1) RECORD_STATUS = a non-base table column which is a checkbox.
2) ITEM_TYPE = a text-item which has an LOV attached.
3) ITEM_TEXT = a text-item which is free format text.
4) LAST_UPDATE_DATE a date column
5) STATUS = a text item either 'Open' or 'Closed'
The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.
I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.
Consider this scenario...
The block has 3 records.
record 1 has 'Closed' status so no updates are allowed.
record 2 has 'Open' status so updating of Item_Text is allowed.
record 3 has 'Open' status so updating of Item_Text is allowed.
I check the RECORD_STATUS checkbox on record2.
(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).
I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.
I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.
(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)
How can i stop this from happening?
View 1 Replies
View Related
Jun 26, 2013
I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data
MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT;
I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?
View 4 Replies
View Related
Aug 27, 2013
I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.
View 1 Replies
View Related
Jun 7, 2013
I am working on a simple form which will get the user to fetch required columns of employee master.
For that i have created a form which will display the column names and select option. When user clicks on query, the form will display the selected columns in a block. After going thru if required the user can download it to excel.
As of now I do not have filtering option. For this I thought of creating view based on the selection and then fetch records from the view and display it in form.
To achieve this I would like to know How I create or replace a view dynamically based on values selected. I remember doing this sort of thing long back, but could not recollect it.
View 3 Replies
View Related
Jul 2, 2011
how to display check box selected records in one column of form when push button is pressed.
View 1 Replies
View Related
Jan 16, 2012
I am trying to find a way to update all of the rows in a table for a column EXCEPT for the very first row. I am not sure if this can be done while I enter my SET parameter or not. I have also thought about using an EXCEPTION in a stored procedure. For example, say I have the table listed below:
SQL> select * from example1;
CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ---------------
A 100 A 200 John 20111225
A 100 A 300 John 20111225
A 100 A 500 John 20111225
A 100 A 400 John 20111225
A 100 A 250 John 20111225
A 100 A 700 John 20111225
A 100 A 800 John 20111225
A 100 A 900 John 20111225
A 100 A 1000 John 20111225
A 100 A 1150 John 20111225
A 100 A 1275 John 20111225
A 100 A 3000 John 20111225
12 rows selected
I am wanting to update the table so that if there were more than 3 J_id's on the table for the same I_id then it will set all of the code1's and code2's to a C except for the very first one. Such as:
SQL> select * from example2;
CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ----------------
A 100 A 200 John 20111225
C 100 C 300 John 20111225
C 100 C 500 John 20111225
C 100 C 400 John 20111225
C 100 C 250 John 20111225
C 100 C 700 John 20111225
C 100 C 800 John 20111225
C 100 C 900 John 20111225
C 100 C 1000 John 20111225
C 100 C 1150 John 20111225
C 100 C 1275 John 20111225
C 100 C 3000 John 20111225
12 rows selected
I have done some searches and haven't seen any results.
View 12 Replies
View Related
Aug 7, 2012
I had created a table which have 100s' of entries in it.
create table reg_user
(
USERNAME VARCHAR2(50),
PASSWORD VARCHAR2(20)
)
***
[Code]..
1. In this table, how to update the Nth row, how can I do it
2. Now I need to add USERID in this table,which will get value from 1 to max no. of rows. I do not want to drop the table and again re create it adding USERID or update each row manually. Is there any other way to add USERID and have IDs from 1 to max IDs.
View 30 Replies
View Related
Apr 12, 2013
I created a table with a column "id" and values for this column is attached a sequence. And now i need, if any value deleted from the table the column "id" will need to be sequence.
ex:
id name
-- -------
1 xxxx
2 yyyy
3 zzzzz
4 pppp
5 rrrrrr
if i delete
delete from test where id=4;
then automatically.. "id" column values will again in sequence... like this
id name
-- -------1 xxxxx
2 yyyyy
3 zzzzzz
4 rrrrrr
note: in the above if i delete the id=4 from the table again it will have be in sequence and if i inserted the again it has to take the next value continue to sequence....
ex : insert to test values(seq_name.nextval,'tttt');
id name
-- -------
1 xxxxx
.
.
.
4 rrrrr
5 ttttt
View 5 Replies
View Related
Nov 1, 2011
I am updating a table column which is xml datatype and am getting above error.Below is the process what i did. since the xml is too large i split them into small chunks.
DECLARE
conditionXML CLOB;
ls_xml_2 Clob;
ls_xml_3 clob;
ls_xml_4 Clob;
ls_xml_5 Clob;
ls_xml_6 clob;
ls_xml_7 Clob;
[code]...
View 1 Replies
View Related
Mar 11, 2012
I am using Forms 6i,From my Form, user selects "sno" and upload BLOB image through OAF Upload utility. It stores the rowid of "SNO" and image...into Table "t1"...Now, i want to update the BLOB image into the original table"t2" using rowid..For this , i wrote the rowlevel trigger on Table "t1", in the trigger, i called a Procedure...In that procedure .. i am trying to update the BLOB column..It is not updating the BLOB column..But it is updating the Other values.. except BLOB column.
View 1 Replies
View Related
Aug 1, 2012
I need to update column of a table with +,- 70,000,000 records.
If I perform an update it lasts......too much and does not finish!
View 6 Replies
View Related
Oct 7, 2013
I have learnt that indexes slow down the DML operations. My question is specific to an update statement. Is it going to be slower if Im trying to update an indexed column on my table or it is slower overall (even when a non-indexed column is getting updated in the table) How does it behave in case of inserts & delete operation.
View 4 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Aug 31, 2010
I have a task to update one of the rows in a table (having only 2 columns, number and long) which is long data type. We are on Oracle 10g. Not sure how to use update for a long data type column.
I have tried using dbms_metadata_util.long2varchar, but still not getting what I want.
View 2 Replies
View Related
Dec 22, 2011
I need to update the current column with sum of the previous column values. Following are the creation scripts
DROP TABLE TEST_LOG;
CREATE TABLE TEST_LOG
(
[Code]....
Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.
I tried by using the following query
UPDATE TEST_LOG T SET SUM_PRE_COLS =
( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0)
FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);
View 5 Replies
View Related
Sep 5, 2011
I create the sample for master/detail form. In detail for prdcode,rate,qty,amount is there. When select prdcode it fetching prdcode,rate in a record and if you type the qty the amout will come based on formula(property) :qty*:rate.
It is available on screen. But when i store the data, in backend table the amount is be a null.
View 9 Replies
View Related
Aug 9, 2012
I am trying to update all rows of 100 column of a table with '0'. The column name is sequentially increasing one like EMP_1,EMP_2,EMP_3, etc. I tried using the below code but I am getting ora-06550 and ora-00927 error's.
begin
FOR i in 1..100 loop
UPDATE EMP_DETAILS SET EMP_'||i||' =0
END LOOP;
COMMIT;
END;
View 6 Replies
View Related
Jun 19, 2013
I need function to pick the record from DB random manner.For example, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5
View 12 Replies
View Related
Jul 5, 2010
I have a non-base-table item which I want to update in the pre-update trigger of the current block.
If the content of the field exceeds 4000 characters, i get the error message
ORA-01461: can bind a LONG value only for insert into a LONG column.
The code is
update tab set long_col = :formsblock.long_col
where tab.tabpk = :formsblock.foreign_tabpk;
Workarounds would be,
1.) to delete the old dataset and insert the new one:
delete from tab
where tab.tabpk = :formsblock.tabpk;
insert into tab (tabpk, long_col) values
(:formsblock.foreign_tabpk, :formsblock.long_col);
or 2.) to change the Item from a non-database item to a database item and use the internal update of the forms-module, but both workarounds are not very satisfying.
Do you know another way to update the LONG-column within the pre-update trigger (or any other PL/SQL part of forms)?
View 2 Replies
View Related
Jul 25, 2013
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies
View Related
Jun 19, 2013
I need function to pick the record from DB random manner.
For example
, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5
If user inputted value is 10 means, it should display one record between 1 to 10.
View 1 Replies
View Related
Oct 10, 2012
I don't know, if this is the intent behavior of oracle or not. But i noticed, my queries Execution plan randomly changes after statistics collection. Several tables are truncated after the daily run at 8AM and statistics gathered for all the tables in that schema.
However execution plans for 2-3 sql statements always changes after this and performance is brought back to normal by executing the procedure by explicitly calling it from the command line with arguments instead of bind variables.
View 3 Replies
View Related
