SQL & PL/SQL :: Procedure Has Got Several Update And Delete Statements
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)
Why large delete statements, with no where clauses, could be using up temp tablespace segments? I thought temp tablespace was just for sort operations,joins, etc.
We have a weekly job that inserts rows into a staging table, deletes, with commit, the a few minutes later and repeats the process. Each delete is using some of the temp tablespace. Eventually we run out of space (ora-01652). That's another thing. I would have thought the space would be released for the next process, but it's now.
We will probably switch to truncate statements, but this situation really puzzles me. I've tried view the docs but don't see any of delete statements using temp tablespace.
It started out pretty simple where I had to update about 40 contacts in the database and I would have 40 separate update statements I would run. The task has jumped to about 300 contacts and I don't want to run 300 update statements. I would like to run this all at once. For example:
update contact set name = 'Name1' where row_id = 'row_id1'
update contact set name = 'Name2' where row_id = 'row_id2'
update contact set name = 'Name3' where row_id = 'row_id3'
Inside procedure,I need to validate all the columns (Ex:col1 should not accept more than 40 chars) and update status(containing error or not) of these columns into another column in the same table.For this,I mentioned only 'UPDATE' statements.So 'WHERE' clause of some update statements not using Primary keys.
In that table composite primary key was created.This procedure is successfully complied & executed now.But this procedure took more than 10 mins to execute.I need to reduce the time to less than a min.
connect the following concepts/information I've been collecting. This is not my field but I'm interested in filling some of mine conceptual/technical gaps.
From a JDBC perspective, one of the benefits of Prepared (and so Callable) statements have over the regular ones is that the statement is "compiled"(*) once and then reused (performance gain).
(*) for SQL statements: building of parse tree and exec.plan
In which way can this notion be extrapolated to invocation of Oracle Stored Procedures through CallableStatements? (After clearing my doubts, I may end concluding that the only relevant feature of CallableStatements is their capacity to deal with stored procedure invocations)
According to procedure's precompiled execution plan SQL compilation implies execution plans generation PL/SQL compilation implies P-code generation and, SQL statements (from PLSQL code) are treated no differently by Oracle than SQL from Java or C/C++. These SQLs will be parsed and execution plans for those SQLs created. ... When the PL code executes the SQL statement, only then does the SQL engine receive the SQL, parse it, and create an execution plan for it.
Therefore, even when the stored procedure can be parsed and cached in SGA (through the OracleConnection.preparedCall("proc") invocation), the SQL statements won't be effectively compiled until they are executed, right? And going deeper, will those SQL statements be cached to be reused in future invocations of the containing stored procedure? Is this a characteristic of the regular stored procedure execution in Oracle? or is it due to the CallableStatement "origin"?
I have an table 'tbcontrol' where for each row I can have one or more sql statements (DML or DDL) in an column.
Example:
task | sql_scripts 1 | create table t1 (c1 number); insert into t1 (c1) values 100; 2 | create table t2 (c2 number); alter table t1 modify c1 not null default 0; 3 | alter table t2 modify c2 not null default 0; alter table t1 add c10 varchar2(10); create table t3 (c3 number not null default 0;
I need to create a procedure where I can pass an task number to execute the sql statements which are in the column 'sql_scripts'. Something like this:
create or replace sp_run_scripts (v_task number) is v_scripts varchar2(2000); begin select sql_scripts into v_scripts from tbcontrol where task = v_task execute immediate v_scripts; end;
But, here I'll have some problems:
- The script will have semicolons (EXECUTE IMMEDIATE doesn't support); - If I put a begin/end like a PL/SQL script, I can only run DML statements because DDL statements have implicit commit. - I can't use a cursor for execute each statement at a time because the scripts have multiples-rows. Even if I search for semicolons to 'separate' the scripts in the cursor (using SUBSTR and INSTR functions), I'll have problems with the semicolons between '' (quotations marks).
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 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
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.
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;
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;
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
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).
“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 create procedure to delete data from audit table but don't know where to start with..
1. Procedure will run on first Saturday of the month (not sure is it possible via pl/sql or have to create separate job) 2. Delete data older than 2 months from Mon - Sat and date shouldn't be 1st of the month. (i.e. leave Sunday & 1st of the month data older than 2 months) e.g.{code}
Procedure delete_log IS BEGIN delete from audit_logs where created >= trunc(sysdate - 60) and created < trunc(sysdate) and created != (Sunday) and created != (First of the month);
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.
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?
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
i have one table ot_ins_item where user will enter the details of item, grade,item qty , later on user will go and update the same table the details of different grades received for the same item in different columns with qty breakup in 3 different fields it_qty_01 , it_qty_02,it_qty_03 respectively with different grades , what i need is i want is whenever he updates this table with different grades based on data entered in 3 different fields , a procedure or trigger should delete the initial record saved and insert three different rows based on newly updated values , it may be 3 or it may be 2 sometime depending upon input values that many records should be inserted same time controlling the qty's entered in breakup not exceeding the main qty.
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'A','ITEM1',NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'B','1TEM2',NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.nextval,'C','ITEM3',NULL,NULL,NULL,NULL,NULL,NULL); SELECT * FROM OT_INS_ITEM; [code]....
PROCEDURE split_name ( full_name IN VARCHAR2, name1 OUT VARCHAR2, name2 OUT VARCHAR2, name3 OUT VARCHAR2, name4 OUT VARCHAR2, name5 OUT VARCHAR2 )
the above is the procedure declaration. i want to use this function in updating the records in a table. this procedure is working fine in forms and it is splitting the names perfectly and i dont know how to use this in update statement as it is not returning anything like functions do.
In procedure "update_emp", i am updating a row based on p_empno and if it is not present i.e. SQL%ROWCOUNT = 0, then I am inserting that row into emp table.
where as in procedure "update_emp1" , first I am checking whether any row with that p_empno is present or not,if presentthen update the row, else raise an exception to insert the row.
In both procedure, I am doing the same thing, But I am unable to understand which one is good and why
create or replace procedure update_emp( p_empno int) is begin update emp set ename='raj' where empno=p_empno;
