SQL & PL/SQL :: Update And Delete Using Loader?
Mar 14, 2012
i have a flat file with fixed positions.Records of this file are having like empid empname deptname typeEx:
100 AAAA ADEPT I
101 BBBB BDEPT I
102 CCCC CDEPT U
103 DDDD DDEPT I
104 EEEE EDEPT D
Here value of type would be like I or U or D I have to load this file into oracle table in such a way that
if type value is I then i have to insert into table
if type value is U then i have to update this record in table
if type value is D then i have to delete this record from table.
I am using oracle10g.
View 3 Replies
ADVERTISEMENT
Jan 19, 2011
I have a SQL Loader Log File Output. I would like to delete Table Information from the output file.Following is my code.
//PATH OPENED AS TEXT BY STREAM READER
System.IO.StreamReader strm = File.OpenText(batchFileName);
//OUTPUT
[Code]....
View 8 Replies
View Related
Jan 31, 2011
I can not update and delete row from database
View -1 Replies
View Related
Apr 28, 2012
i have three tables: ot_req, ot_po_breakup, and ot_po. when a row is inserted into the ot_po table reffering to ot_req,there is a trigger which creates a seperate reocrd in ot_po_breakup with the details of ot_req ot_req.ri_sys_id as pb_ri_sys_id and ot_req.ri_qty as pb_ri_qty ,pi_sys_id as pb_pi_sys_id .upto this part is okand when i insert also the logic is okay.
i have created a trigger to update the rows in the ot_po_breakup table after insert on ot_poin order of pb_ri_sys_id and pb_pi_sys_id and try to update the values in the columns pb_ves1q, pb_ves2q, and pb_ves3q order. i am trying to take the quantities in ot_po.pi_qty and insert them into the pb_ves1q, pb_ves2q, and pb_ves3q columns of ot_po_breakup where those columns are empty (0 or null) such that the sum of those three columns for that row does not exceed the pb_ri_qty in that row.
My problem is i need to mofify my trigger to do the update and delete , that is whenever the user is updating the column ofot_po.pi_qtythe qty should in ot_po_breakup should get updated accordingly and sum of pb_ves1q,pb_Ves2q and pb_ves3q should be equal to pb_ri_qty and do the same for delete as well
CREATE TABLE OT_REQ
(
RI_ITEM VARCHAR2(20 BYTE),
RI_SYS_ID NUMBER,
RI_QTY NUMBER
[code]...
View 14 Replies
View Related
May 7, 2008
have one XL sheet Doc that has 2 tabs. One has the list of keywords for deleting the records from Oracle that has the keywords in it. The second one needs the update query. Read the first column in the XL sheet and replace it with the second column value for the records that has the first column value in it.
I never worked in the combination of XL and Oracle.
View 3 Replies
View Related
May 24, 2012
the following case is successfully done with mssql databases.
Case:
Table UserGroup
Columns id, name, handshake
When the handshake is set to 'd', this record should be deleted. I know it is bad behaviour by design.
What have I done so far:
- created an after update trigger (mutual error) Caused by trying a delete action in the update action, not possible.
- created a view in combination of instead of update trigger.
This causes also mutual error, or if ignored (PRAGMA AUTONOMOUS_TRANSACTION), an deadlock.
Code so far:
create or replace procedure Delete_UserGroup_sp(p_groupId in USER_GROUP.HMIUSERGROUPID%TYPE, p_handshake in USER_GROUP.HANDSHAKE%TYPE)
is
begin
if p_handshake = 'd' then
delete USER_GROUP WHERE HMIUSERGROUPID = p_groupId;
commit;
end if;
end;
create or replace view USERGROUP_V as select * from USER_GROUP
create or replace trigger USER_GROUP_T1
instead of update on USERGROUP_V
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
Delete_UserGroup_sp(:new.HMIUSERGROUPID, :new.HANDSHAKE);
end;
View 5 Replies
View Related
Oct 23, 2013
One of the procedures that am working on is failing with ORA-0000: normal, successful completion error.
The procedure has got several update and delete statements and have logging enabled after each step. The problem with that again is, each time the log table gets updated thereby losing the history of until what point the procedure ran successfully.I have this issue only in production environment and unable to simulate it in dev environment which limits my options of troubleshooting the procedure code. I was using SQLERRM in the code.
Is there a way I can identify the bad records/ record causing this issue? Am very new to PL/SQL and do not know how to proceed with this.How do you debug this sort of issues??(where one procedure internally invokes another one which again invokes other one etc)
View 27 Replies
View Related
Mar 13, 2012
Can I achieve the merge (update-only) and delete in a single SQL statement?
merge into table_4
using
(select table_3.col1 col1,table_3.col2 col2,
from table_1, table_2, table_3
where
[code].....
delete from table_3 where <records identified in the 'using' clause of above merge>;
i.e.
delete from table_3
where (table_3.col1,table_3.col2)
in ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 );
What I want is roughly as following :
WITH mywith as ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 )
update table_4 set col3='N' where table_4.col1=mywith.col1 and table_4.col2=mywith.col2
delete from table_3 where table_3.col1=mywith.col1 and table_3.col2=mywith.col2;
View 5 Replies
View Related
Feb 6, 2009
I am trying to write a trigger that will do an insert/delete/update into a audit table when a change has occurred on the primary table. The change will be recorded in the audit table by a incemental sequence number and the updated data.
there will be an extra column in the audit table
how to get a simplified version of this trigger.
the primary table will look like so
Id_num varchar (20)
code Integer
desc varchar(20)
sequence_num Integer
audit table
Id_num varchar(20)
code Integer
desc varchar(20)
timestamp date
sequence_num Integer
View 6 Replies
View Related
Jul 27, 2008
I'm trying to simulate a delete operation through using an update on a trigger my tables are
CREATE TABLE EMPLOYEE (
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
salary FLOAT,
dno INT NOT NULL,
vst DATE,
vet DATE,
PRIMARY KEY (Ssn));
[code]....
What I want to do is whenever there is an update on vet( valid end time) in employee, delete the values from the employee table and insert the old values from employee into the emp_history table along with the new value for vet. Here's my trigger
CREATE TRIGGER trig4
AFTER UPDATE OF VET ON EMPLOYEE
FOR EACH ROW
BEGIN
INSERT INTO EMP_HIST VALUES( : old.LNAME, : old.SSN, : old.salary, : old.dno, : old.vst, :new.vet);
DELETE FROM EMPLOYEE WHERE(SSN = :NEW.ssn AND vet IS NOT NULL);
END trig4;
//ignore the space between : and o as it makes a smily
The problem is I get an error for a mutating change, what I'd like to know is if the above trigger is possible, and if so how to implement it without giving me an error. I mean it makes sense syntactically and logically(at least to me).
View 1 Replies
View Related
Jun 14, 2013
Was 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
View 15 Replies
View Related
Dec 17, 2010
How to get all the name of tables that a user can select, insert, update or delete?
View 2 Replies
View Related
May 15, 2012
we received a design advise to add columns to track the update and delete done on each row in our tables:
- DELETED_DATE_TIME
- DELETED_BY
- UPDATE_DATE_TIME
- UPDATED_BY
In our architecture, the application can only access functions/procedures to access/modify data. Each function logs the action, the executed sql statement, oracle error, user terminal, and the user into a unified log table by using v$ tables to create a general log function that is called after execution or error.
The only advantage is that it will be easier to know the delete and last update information faster versus space and design modification.
View 1 Replies
View Related
Jul 26, 2012
For triggers, is it possible to do separate actions on insert , update and delete. For example, if insert is the case, do select; if update is the case, do select from another table and so on?
View 1 Replies
View Related
Aug 10, 2005
I have multirecord block and I want to disable Inserting/Updating/Deleting more then one record at a time.
View 32 Replies
View Related
Dec 8, 2012
I have created a simple page with a tabular form built on a view. The view is on top of a collection that is created when the page is ran. I added a tabular form column validation. I then was going to add a pl/sql process to handle the updates into the db, but before I did that I wanted to delete the ApplyMRU that was created when I first created the tabular form because it does not make sense on my view. When I went to delete the MRU it gave me an error saying "Multi Row Update processes can't be deleted as long as there are validations defined for the tabular form".
I have searched for an answer on this, but so far I have not found much. I am hoping it is something simple that I am missing. I even tried deleting the MRU first and then add the validation, but then it gave me another error about "Tabular form validations require multi-row update processes" I know this was possible in 4.1, but now I am trying it on 4.2
View 2 Replies
View Related
Sep 30, 2011
I am using a shell script to load unix content to a database.I have captured the unix data to a csv file and I am using a sql loader to inser that csv data to database. following is my ctl file contents.
[b]load data
infile data.csv
into table AVS_LOGS
fields terminated by ','
(
RUNDATETIME,
SERVER,
DIRECTORY,
FILENAME,
LASTUPDATETIMESTAMP
) [/b]
and I am using sql loader command in unix that is [b]sqlldr $CLOGIN control=control.ctl log=test.log[/b]
But this is working only if the table is empty.Now I am looking for something where I do not need to delete the data from table each time. it should update the table.
View 5 Replies
View Related
Oct 9, 2012
I have to write a procedure that accepts schema name, table name and column value as parameters....I knew that i need to use metadata to do that deleting manually.
View 9 Replies
View Related
Mar 18, 2013
I have a flat file (student.dat delimiter %~| ) using control file (student.ctl) through sql loader. Here are the details.
student.dat
student_id, student_firstname, gender, student_lastName, student_newId
101%~|abc%~|F %~|xyz%~|110%~|
Corresponding table
Student (
Student_ID,
Student_FN,
Gender,
Student_LN
)
How do i map student_newId field to student_id field in STUDENT DB table so that new id should be inserted in student_id column. How do i specify the mapping in control file. I dont want to create a new column in student table. In control file i will specify the below, Is this a best approach?. Do we have any othe way?
STUDENT_ID *(:STUDENT_NEWID)*,
STUDENT_FN,
GENDER,
STUDENT_LNAME,
STUDENT_NEWID BOUNDFILLER
View 1 Replies
View Related
May 14, 2010
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
View 4 Replies
View Related
Sep 7, 2010
I have to update 20 and 60 million records of a table. The update statement are
1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;
commit;
2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;
commit;
Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.
View 1 Replies
View Related
Aug 17, 2010
This is my query:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
View 9 Replies
View Related
May 29, 2012
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/
View 3 Replies
View Related
Mar 6, 2013
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012
View 10 Replies
View Related
May 30, 2013
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
View 5 Replies
View Related
Mar 21, 2011
I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.
Eg:-
SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
[Code]....
The above table contains 14 records. Now i would like to update the salary column with different values like
EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000
How to update above values with single update query.
View 11 Replies
View Related
Jan 25, 2012
After many tests I can't make work and update of the same table inside the same table.
Trying to avoid Mutating Table Error now I have
ORA-00036: maximum number of recursive SQL levels (50) exceeded
Sample Data :
create table test_compound (USERID VARCHAR2(10),APP VARCHAR2(15),LAST_UPDATED_ON TIMESTAMP);
insert into test_compound values ('user1','1',systimestamp);
insert into test_compound values ('user2','2',systimestamp-4);
insert into test_compound values ('user3','3',systimestamp-6);
CREATE OR REPLACE TRIGGER trigger_test
FOR UPDATE ON test_compound
COMPOUND TRIGGER
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tab t_tab := t_tab();
[code].......
When I execute :
update test_compound
set last_updated_on=systimestamp
where userid='user1' and app='1';
The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.
View 13 Replies
View Related
Jul 19, 2011
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
View 4 Replies
View Related
Jul 21, 2011
I have table test1(id,name) and table test2(id,,name)
Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.
So I wrote this trigger but its not working
create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/
View 9 Replies
View Related
Mar 27, 2007
We have a bunch of tables with FKs but we do not have the 'on delete cascade' option on these FKs. If I want to delete records in the database do I have to write delete statements for each table starting with the lowest child or is there a cascade option I can use with the delete statement?
View 1 Replies
View Related
