SQL & PL/SQL :: Trigger Fires On Deletion Of Uncommitted Data And Changes Saved
Dec 26, 2012
Maybe you can tell me if you think it's right or wrong for Oracle to behave like that. What basically happened is that I've set up a trigger to capture deleted rows from testtab into testtab_del.
I have inserted and immediately after - deleted a row from testtab. Then I inserted another row, committed, and checked my testtab_del table.
I've seen that val1 was inserted and committed into testtab_del. This happened in spite of the fact that this row never existed as a *committed row*.
What do you think about this behavior in this scenario? Works as designed or not?
SQL> show user
USER is "ANDREY"
SQL> col tcol for a10
SQL> drop table testtab;
[Code]....
View 17 Replies
ADVERTISEMENT
Aug 30, 2012
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.
CREATE TABLE ot_ins_item (it_ins_no NUMBER,it_no NUMBER,it_grade VARCHAR2(12),
it_code VARCHAR2(12),it_qty NUMBER,it_flex_01 VARCHAR2(12),
it_01_qty NUMBER,it_flex_02 VARCHAR2(12),it_02_qty NUMBER,it_flex_03 VARCHAR2(12),
it_03_qty NUMBER);
create sequence s_it_no start with 1 ;
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]....
View 5 Replies
View Related
May 23, 2011
I have a situation where one form calls another. I am opening the other form(say B) using the below code.
declare
temp_form formmodule;
begin
temp_form := FIND_FORM ('D:RSSQ2TestingpurposesMODULE3_MULTIPLE');
[code]...
On Form B there is an Exit button on click of which it closes the Form B and returns to Form A
begin
close_form('MODULE3_MULTIPLE') ;
end;
In my scenario on Exit from FORM B i need to enable a button on FORM A.I am not able to capture the trigger that fires when I Press the Exit
View 7 Replies
View Related
Mar 8, 2013
I have a RAC system with DR set-up, this is a test environment and it doesn't have any backup, why DR is required but it exist. Since this is a test a lot or archives gets generated and deleting the archives has become a daily job for this server manually.
I want have a script to delete archive logs which is in non-ASM (i.e. filesystem) after ensuring that the archive log has been applied in standby database. If this can done only by RMAN.
View 6 Replies
View Related
Feb 4, 2013
I am facing Deadlock issue in my transaction when record is been deleted in the same table in parallel from a PLSQL package. The package is been called from the Java code.The example and the table structure is given below.
Col1 of tab1 is foreign key to col1 of tab2.
Commit will not be done until all the below dml scripts are executed in both environment.
1st session:
Delete from tab1 where col1=1001;
Delete from tab2 where col1=2001;
Result: Deletion successful
2nd session:
Delete from tab1 where col1=1002;
Delete from tab2 where col1=2002;
Result: Deletion successful
1st session:
Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;
Result: Deletion successful
2nd session:
Delete from tab1 where col1=1004;
Delete from tab2 where col1=2004;
Result: Query is executing for a longer time.
1st session:
Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;
Result: Query is not executed and throws Deadlock in the back end.
View 3 Replies
View Related
Oct 4, 2012
i have a datablock with a list item and display item.and after selecting the list item (registration number),corresponding data gets filled into the display item.but when I click the save button(ok) data doesn't go into the database,
View 1 Replies
View Related
Jul 10, 2012
Consider a situation:
There are two users 'A' and 'B'
account number of A: 001234
account number of B: 002678
Suppose, if A wants to transfer $1500 to B's account
update cust_acc
set balance = balance - 1500
where
account_no = '001234';
[code]....
Assume that, oracle crashes somewhere between first commit and second commit.In this case, A's total money is deducted by $1500..But $1500 is not yet transferred to B's account.How will oracle keep track of these information?
View 19 Replies
View Related
Nov 16, 2012
I have a DR setup with the following configuration
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY
I dont want to backup the STDBY DB but I want the ARC files to be removed when applied so my flash area does not fill up. Is there some command(rman or not) that can fire off this policy?
View 6 Replies
View Related
Jun 29, 2010
i design one master detail block and one command button for saving the data....as i click on save button data get saved and after that if i want to close form then at this time it again asking me for save changes window..
View 8 Replies
View Related
Aug 8, 2011
I have a query in reports.My requirement is that report should fire everyday in a year.When an order is cancelled/copied and also that when an order is cancelled then the line amount should be set to zero and order_status should automatically set cancelled.
EX:
order_no amt product_name quote_no order_status
45434545 200 dell 10022 active
when order cancelled then
order_no amt product_name quote_no order_status
45434545 0 dell 10022 cancelled
View 2 Replies
View Related
Apr 3, 2013
I have a form on a table that allows me to edit, delete, or create records. If I choose to edit a record from the report the form get populated correctly and edits or deletes work fine. My problem is when I choose to create a new record. I get a blank form which is what I want, but once I fill it out and click the create button, the automated row fetch fires again and gives me a no data found error. How do I get it to not fire when the create button is pressed?
View 2 Replies
View Related
Apr 11, 2013
I want to delete archivelogs from ASM and want to schedule a script for same in crontab.
Also i removed archives manually from asmcmd.But when i runned below command,i didn't observed any change in the free_space.
select total_mb,free_mb,(total_mb-free_mb) used from v$asm_diskgroup;
View 6 Replies
View Related
Jun 15, 2012
How can we clean a tablespace(delete all the contents) without dropping the tablespace.
View 3 Replies
View Related
Apr 21, 2012
how to write the package for deletion?
View 1 Replies
View Related
May 6, 2010
Form has insert and delete enabled.
I would like to prevent the deletion of a record based on some column value.
Something like:
IF (COLA value < 10) THEN
SHOW ALERT;
RECORD IS NOT DELETE
ELSE
DELETE RECORD AS NORMAL
END IF;
I want this to happen even before the record is marked for deletion, but I don't know where to put it. I tried a pre-delete trigger, but it does not work they way I want.
View 8 Replies
View Related
Nov 6, 2011
When i opened the form and after querying the records if I press CTL+ Down Arrow the record is deleted.
This is the normal runtime form behavior. When ever pressing CTL+ Down arrow I don't want to delete the record, I tried with KEY-DELREC but it is not happening.
View 3 Replies
View Related
Nov 25, 2010
We had an escalation wherein one of team members accidentally deleted an LDAP entry for a database. We use Oracle Net Manager to add/delete the connect descriptor.
Are there any logs using which we can find out as to who deleted the entry.
View 1 Replies
View Related
Sep 4, 2012
As far as I know, in APEX 4.0 no other user other than the one who created a Public report can delete it. Can this behavior be changed somehow?
View 3 Replies
View Related
Mar 8, 2012
My record is getting saved twice every time I push my button to commit changes to my DB.I have already records from my table and when I click for that record to update the grade. the same record is getting saved twice.I have put my queries in on-update trigger to update the grade of my record. I check my query, don't have errors. I don't know what's wrong with this.
View 24 Replies
View Related
Jan 21, 2011
I was carrying out an experiment in order to crash the database and recover it.
The database was running, I moved the control file to another location and to my surprise the database was still running. I tried issuing checkpoint and transaction but it didn't affect database operations. I tried doing log switch. It completed successfully. According to my understanding and Oracle Certification books database was supposed to crash. But it didn't.
I tried this not only on RHEL, Windows XP but also in Solaris 5.10. The database version is Oracle 10.2.0.4 Standard Edition.
View 1 Replies
View Related
Oct 1, 2012
I have deleted a user with CASCADE option.
After deletion, the size of tablespace should be reduced.
But the tablespace size is same as before deletion of user.
Currently , that USER is deleted and not present in the database . Still space is not released after user deletion.
View 4 Replies
View Related
Nov 13, 2009
One question reg Materialized views.
If as part of housekeeping of the Source database we delete some records (older records), will the materialized view also be updated with the deletion? I believe the answer is yes. In that case can we ensure that this delete does not happen?
Is there anyway we can prevent MView refresh from deleting the records that is once inserted even if we delete the same records in source DB?
View 3 Replies
View Related
Oct 4, 2012
I'm on a Windows server 2003 R2 64 bit, database is 11.1.0.7. From RMAN connected to the target and the catalog, this is a show all;
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'F:oracleadmin
[code]....
I'm running incremental backups, a level 0 on sunday and a level 1 the other days of the week, this is the ctl file:
LEVEL 0:
connect target /@ORCL
connect catalog rman/rmanpw@rmancat
RUN
{
[code]....
The level 0 does delete the archive logs (because of the delete input). Each backupset has two copies of the archive logs (which is what I want), and I expected the logs to be deleted after being backed up 2 days (each day is twice, 2 days = 4 times). But it's not deleting the logs, even after 6 runs.
View 4 Replies
View Related
Dec 2, 2010
we are testing the oracle developer tools 11g and we have the followed issue.
If we run a form saved on the local disk, it's running properly but if we save the form on a local disk we get FRM-40010 Cannot read form XXXX.
Example:
TEST.fmx on C:Temp (Local disk) is running
TEST.fmx on T: (Network disk) FRM-40010
View 4 Replies
View Related
Jan 27, 2010
Why showing at the status bar "3 records applied and saved" instated of "2 records applied and saved". 2 means 1 for Header and 1 for Detail. How I change status bar.
View 7 Replies
View Related
Jun 25, 2012
Once you've created several default reports for an Interactive Report, can you change the order in which they display?
View 3 Replies
View Related
Jul 27, 2012
When users are selecting a saved report on a given Interactive Report, they must search the entire list of saved reports for that IR because the choices are not sorted. Is there a way to improve the order that the choices are displayed in?
View 2 Replies
View Related
May 7, 2013
I am using APEX 4.0 and have a task of implementing the old tabs (older versions of APEX) for the IRR when they are saved. My users are not a fan of the select list when the IRRs are saved. I have located a site [URL] .....
that has this implemented but the tabs are not displaying within my application on apex.oracle.com, only the saved report names with no spacing. getting the tabs to display and/or possibly adding a dash (-) between each saved report name?
I added a Report Region as a 'PL/SQL Dynamic Content' and added code below in the Source. Am I missing anything to get the tabs to display like the example?
DECLARE
CURSOR cur_saved_ir (p_app_id IN NUMBER, p_page_id IN NUMBER, p_user IN VARCHAR2)
IS
SELECT '<span id="'||REPORT_ID||'" onclick="gReport.pull('''||REPORT_ID||''')">'||
[code]...
View 5 Replies
View Related
Jul 25, 2011
I have 2 blocks, block 1 which is the master and block 2 which is the detail. Both block 1 and block 2 are multi record blocks. For my form I want to display a warning message to the user if they create a master block without also creating a detail.
View 2 Replies
View Related
Jul 31, 2010
We are building a custom form extension with our EBS R12 where against certain parameters we are inserting and updating records in a table through a PL/SQL procedure.This PL/SQL doesn't use a commit exclusively, instead the user must click the Save button after verifying the data (new records are inserted, existing ones are updated and after the insert and update routines, execute_query is fired to fetch the latest data)
Everything works fine, however, when the user clicks on the Save button form status bar says "FRM-40401: No changes to save"
We would like to alter this message with our own custom message stating how many records were inserted and hows many were updated (We have counters counting this information)
Under which particular trigger we should write the code to enhance this requirement?
View 2 Replies
View Related
