I want to replace a word with another word in all tables data with varchar2 datatype that I have in my database .Is there a solution to update data at once instead of update all column data Separately.
View 8 RepliesI am experimenting with the 4.2 version of Apex at URL.....
I have created a tabular form (based on a view with an instead of trigger) and have restricted this form to "Update only" mode. Whenever I modify a field value and submit the change I get the error "Current version of data in database has changed since user initiated update process".
I have data as below:-
select qualifier from nv2_audit_log where qualifier like '%NXPRI%NXUPM%';
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
The question is how could i update all the qualifier from /acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910 to /acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910 where qualifier like '%NXPRI%NXUPM%' ?
Actually i want to change the NXPRI to be NXUPM only for all record with qualifier like '%NXPRI%NXUPM%'
The output must be like this:-
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
I can not update and delete row from database
View -1 Replies View RelatedI am having trouble changing a non-displayed field when a displayed field is changed.
When a user wants to un-assign a territory they want to just null the ID field (non-database item). I then need to null the PIDM field(not visible, database item) for that one record.
I tried this, but I end up putting null on all the records instead of just the one record, which ends up un-assigning all the territories not just the one.
Is there a way to assign the null to the PIDM field associated to the ID field of that particular record.
table1: url_data
page_url category
learn.web/AU/eng/AWARD/efg null
learn.web/CN/eng/AWARD/efg null
learn.web/US/eng/AWARD/efg null
learn.web/UK/eng/AWARD/efg null
learn.web/JP/eng/AWARD/efg null
learn.web/CA/eng/AWARD/efg null
[Code]..
table2: country
country_code
AU
CN
US
UK
JP
[Code]...
Now I have to update category by using /CC/eng/ where CC is the country code from country
I can use where page_url LIKE '%/__/eng/' but here the problem is the last row. I have to update by looking up from country table.
the large data FOR UPDATE in table column ?
claimClob clob:=claim; -- claim large data
v_buf varchar2(1000);
amount binary_integer:=1000;
position binary_integer:=1;
[Code]....
why the FOR UPDATE don't do nothing ?
I am preparing a query form based on one view.it will return the result set based on parameters passed.What i need is i will add 3 non database item in entry mode along with result set.Once the user inputs these 3 fields in non database fields , it should update the relevant records mapped to it in database table. How to do this.
View 19 Replies View RelatedI have to check some parameter values for an OID tuning I do not have sys access, I have only schema user access
Now how do I see the values of SGA_TARGET, db_cache_size etc parameter
I manage to see these var in init.ora file under /dbs folder but there are 2-3 spfile also having containing these attributes, not sure which reflect the actual.
Also can I alter also these value via schema user or do I need SYS access?
check my query and correct it basically I want to insert/update data from one user to other therefore I write this coding at my form button, when user press button first time its insert data successfully but if user press button again then it should update because data have been inserted in first step.
Actually it is detail table so it can have more then one record against any master. My query fails in updation, it inserts a new record instead of update.
find the attached file for checking QUERY.
I have a table named employee with 10 records. Empid is the primary key and empsal contains salary of the employees. I created backup of this table and name of backup table is empbackup.
Now empsal was updated to empsal*10 in employee table and 10 more records were also inserted in employee table. Now I want to restore the empsal column for 10 employees from empbackup(as initially there were only 10 employees in employee table when empbackup was created) table how can I do that in PL/SQL or Oracle?
SCOTT@orcl_11gR2> create or replace type nesttype as table of clob;
2 /
Type created.
SCOTT@orcl_11gR2> create table emp
2 (empnonumber,
3 enamevarchar2(1000),
4 hobbynesttype)
5 nested table hobby store as hobby_nt
6 /
Table created.
SCOTT@orcl_11gR2> insert into emp values
2 (1, 'name1', nesttype ('dancing', 'cricket','listening music','dancing'))
3 /
1 row created.
SCOTT@orcl_11gR2> insert into emp values
2 (2, 'name2', nesttype ('cooking', 'dancing','cooking'))
3 /
1 row created.
I want to update nested table record based on index suppose i want to update 3rd number of hobby for name2 employee
i have written the below query
SCOTT@orcl_11gR2>update Table(select hobby from emp
2 where empno= 2) e
3 set value(e)='new_value' where to_char(value(e)) = (select 4 to_char(tab1_element)
5 from (select rownum rn,
[Code]...
2 rows updated.
but the above query is updating 2 records but my requirement is it should update only third record
How can we update nested table based on index number
Constraint_type = UNIQUE
If I'm updating a value from 10000 to 555555. And the constraint's status is ENABLED Deferred is IMMEDIATE.
Now my question is at what stage the updating data will be checked?At the entry level or at end of the Updation statement?
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
View 3 Replies View RelatedWas going through this and found that
“AUDIT DELETE ANY TABLE BY <username> BY ACCESS” or “AUDIT UPDATE ANY TABLE<username> BY ACCESS”
enable audit for delete and updates for given username/schema. I want to enable auditing on delete and update on my entire database.
Why? And have we tested it in our any of existing setup? I am thinking of “Trigger after delete” but again this logic gets struck at individual tables. It do not work simply once and all for complete database/all users/all schemas
I am trying to develop a form consisting of a key block and a single data block. The problem is that the driving table is not the table that needs to be updated.
user wants the following layout:student Advisor
ID# name degree major Concentration ID# name
The driving table (TABLE A) will supply the first 5 fields. The advisor ID comes from (TABLE B).
The user needs to update the adviser ID# field associated with the student ID# field. The form is to be tabular listing all students. I've seen some info on using procedures to insert, delete, update, query and lock tables, but i'm just not sure if that's what is needed.
when setting up data guard, if the primary clustered database is already in archive log mode, is there a need to restart both cluster instances when we update the spfile parameters for data guard ? or we can simply add the data guard parameters to the spfile while both cluster instances are online ?
View 4 Replies View Relatedi have a tabular form select * from emp and i want to create table and store there data in goup select empono,sal,com group by dept i want to insert in another table.
how i insert the data in table by forms front end and then update also when again click the button or any change occur in form insert into a select empono,sal,com group by dept
Is there a way in oracle 10g to know when was a row inserted or updated using oracle data dictionaries.
I know we can track these by creatig a seperate date column.
I just want to know if there is built in table which shows these.
We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
View 7 Replies View RelatedWe had two tables.
Table 1: matusetrans
ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13
[Code]....
Table 2: Inventory
ITEMNUM STORELOC lastyear currentyear
AM1324 AM1 need sum(quantity) here need sum(quantity)
AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
Edit/Delete Message
optimize this code. Scenario have to update about 40 million rows to static value, I'm committing 1Million rows in one loop. The first 1 millions rows are getting updated very fast probably in a 2 minute, after that the code just hungs and i don't see increase in committed rows.
Declare
cursor c1 is
select rowid from t1
where c1 is not null;
[Code]....
I want to use a single query to update gender to male for the 4 smallest custid in my database.I tried these but got errors..
UPDATE (SELECT * FROM customer_0908288 ORDER BY custid ASC WHERE ROWNUM <= 4)
set gender ='M' WHERE ROWNUM <=4;
error is: missing right parenthesis
WITH summary AS (SELECT custid FROM customer_0908288 WHERE ROWNUM <=4 ORDER BY custid)
UPDATE summary SET gender = 'M';
error is: missing SELECT keyword
I have written a Database procedure to move data from one data to another database using Database link. When i run that procedure on Sql it is running fine, but i use same procedure in a button in form using "When-Button-Pressed" trigger it generates error, error code is ORA-02055 and ORA-02067.
View 8 Replies View RelatedI was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.
Write a PL script to populate these columns as follows:
TRAVEL_STATUS is one of three values:
GLOBETROTTER: agent has visited five or more different locations in the course of his missions
ROVER: agent has visited between one and four locations
SLOB: agent has been on no missions.
CONTACTS is the number of targets that share the agent's home location.
some of the tables and columns are
agents table: agent_id, location_id
targets table: target_id, location_id
missions_agents table: agent_id, mission_id
missions_targets: target_id, mission_id
missions table: mission_id, location_id
My code is
DECLARE
status AGENTS.TRAVEL_STATUS%TYPE;
contact AGENTS.CONTACTS%TYPE;
CURSOR loc_cur
IS
SELECT
a.AGENT_ID id,
[code].....
I'm writing a Procedure which Updates or Inserts data in Multiple tables. Selected fields of 10 tables need to be updated or Inserted. For this I created a table which comprises of fields related to all 10 tables. Then I write Procedure. Under this I create a Cursor which uploads the data from the newly created table which contains different fields of 10 tables. Then I write Update and Insert statements one by one for all 10 tables.
Sample Procedure below.
-------------------------------------------
Create or replace procedure p_proc as
spidm spriden.spriden_pidm%type;
cursor mycur is select * from mytable;
begin
for rec in mycur
[code]......
----------
Note: I created table on my server because data is coming from different server. They will upload the data in the table from there I pick and update the tables. Is updating or Inserting data in different tables one by one is correct?
I'm using few cursors to update my data and store it back to the same table. But for some reason the cursor seems to be picking obsolete data.
cursor c1
is
select distinct roles
from table1
where flag = '1';
cursor c2
is
select distinct roles
from table1
where flag = '1';
begin
for i in c1
loop
here im updating the roles im picking to to a suffix and role.
update table1
set role = suffix_role
where 'some condition';
end loop;
commit; -- committing so changes are visible for my next cursor.
for j in c2
loop
here im deleting all the roles that are not part of my comparing table.
delete from table1
where role = 'something';
But in my debugging table i see that its deleting roles present as input for first cursor, whereas it should actually pick data with suffix_roles.
I've to do a update activity for data correction. There are about 1 crore records involved in this. I want to keep a log of the activity at least Sales No. so that I can validate my data correction. Main cursor is involved for this. The problem is if I insert each record for sales number in the loop then process will take about double time. So i decided to create a nested table and insert after each session finishes.
I'm talking about session here is. The table I'm updation is partition table. So I can have many sessions and each session updating different table partition. This way there is no lock, better performance. Server has 35 processors so I can at least have 25 sessiions.
I could have used varray for this. But I want to learn nested table also. Problem here is not to take different way to complete this activity but to learn why I'm facing this issue.
CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT
(
SALES_NO VARCHAR2(24)
,sALES_SEQ NUMBER(4)
) ;
[code]...
I have my database structured to where i have my tables, constraints, and Rows populated in each table which is 4 tables in particular. for one I want to be able to List all four tables with its content and constraints all at once. What i want to with the PL/SQL is to be able to program a prompt to where the user can just enter the individual order information and the data goes to what ever appropriate column or Table it needs to go to. Now should I be mostly be using procedures, functions or triggers to make this possible.
View 9 Replies View Related11.2.0.1. Using DG4ODBC to update data from oracle to sqlserver and vice-versa using dblinks?
View 6 Replies View Related