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.
“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 track an employee's department changes by adding a Department change counter which is set to 1 whenever the department changes otherwise it is set to a 0. How can I accomplish this update of the counter? Below is the table creation sql and sample data for insert.
CREATE TABLE EMPL_DEPT (EMPLID VARCHAR2(11) NOT NULL, EFFDT DATE, EFFSEQ SMALLINT NOT NULL, EMPL_RCD SMALLINT NOT NULL, DEPT_ID VARCHAR2(12) NOT NULL, [code]..........
I am using 'Novell Sentinel Log Manager' to collect/fetch logs from my Oracle 11g R2.To enable auditing, first I did following:
login as sys, then SQL> create user testuser identified by "testuser"; SQL> grant connect to testuser SQL> grant dba to sharf SQL> grant CREATE SESSION to testuser; SQL> grant select on v_$session to testuser; SQL> grant select on v_$version to testuser; SQL> grant select on SYS.DBA_AUDIT_TRAIL to testuser; SQL> grant select_catalog_role to testuser; SQL> grant select any dictionary to testuser;
Now logon/logof of user 'testuser' are logged , as well as if testuser drops a table or creates a table, its also logged . but when 'testuser' insert a new record, this information does not logged ;( while I need to know exactly what was added SQL> insert into emp (empid, name, salary) values (10002, 'Ron', 6000)
likewise if 'testuser' modify/update an existing record it also does not logged. SQL> update emp set salary=700 where empid=10001;
which sql statements I have to execute to start auditing 'insert' and 'update', so that I know what was added/inserted and exactly what was updated/ changed/modify by user 'testuser'.
I have one form where I have let's say 2 items that I want to manage. One is a List Item and the other Text Item. Both are based on one table. The List Item is not updatable (Update Allowed is set to NO) and there I have some types of houses for example (T -> tall house, B -> Big house, S -> Small house it's not important so much) and the Text Item is for counting the types for example we have 4 houses of type T and if we add another one in the textItem we will see 5, so it's not counter for all houses but it counts types of houses and that item is not updatable nor insertable (Update allowed and Insert allowed are set to NO) so it's only purpose is just to show how many hauses we have of particular type and automatically increases the number if we insert new house. Beside this two items I have another Items that conatin some informations about the house (How many flats, doors, windows, and so on..). you've got the picture of my form and table.
Now I want to add one button with trigger WHEN-BUTTON-PRESSED that will do this. If I want to change the type of the house after I inserted it and save it in the table, I have to just change the type in the List Item (choose another type) and press the button to save the same house with the same properties I would say (How many flats, doors, windows, and so on..) but with another type. First when I press the button it must ask me if I am sure that I want to make that change (I'll make an allert fot that) and if I press OK or Yes my house will be saved just with the different type and the previous house with the previous type must be erased. And in the textItem 'the counter', the number of the new type must encrease for one and the previous type must decrease for one.
I hope you understand what I want to do and all I need is that trigger to manage this problem of mine. I know it's possible to do but I am quite new in DB programming and I am not very good with programming in PL/SQL.
I've been trying to write some code to add a column if it does not exist as the code will be run numerous times and will be parameterized in other software to run across multiple tables.
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.
I want to add column to table which has huge amount of data and fill with data from another table. What is the best way to do it? Is it faster to use CTAS instead of ALTER TABLE ADD COLUMN?
I want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
i have 2 data block (maintenance & maintenanceparts) in one form and i will like to add the values of the attribute "LineCost" in the maintenanceparts datablock and put the sum into 'Totalcost' attribute in the maintenance datablock.
I tried use the code
"begin select sum(maintenanceparts.LineCost)into :maintenance.totalcost from MaintenanceParts; end;"
But it ended up adding all the records that was save in that attribute and putting it in totalcost. I am new to oracle
1) How to add a new column to the existing table's particular position, instead of atlast.
2) I created a table without mentioned the datatype size as below Create table dummy (name char, age number). Then what is the default size will be allocated for those column's?
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;
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)
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).
I go to Report Attributes, then I click Add Column Link in the "Tasks" right menu, it adds me a column link, I just add some text for the link and a page to go to. Then I run the report and I get :
report error: ORA-01403: no data foundTested with several classic reports on multiple pages.
Debug mode shows me :
0.43816 0.00240 ...Execute Statement: select distinct [...] order by 3,11 ,4 0.44056 0.00162 print column headings 0.44218 0.04816 rows loop: 25 row(s) 0.49037 0.00141 report error: ORA-01403: aucune donnée trouvée 0.49175 0.00078 Computation point: After Box BodyWhen I run the query in my favorite tool, I get expected results.
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
AVDF current version 12.1 not support External/SAN storage. my question is, if customer get a huge number of Audit log and DBFW event records, then how max size can Audi Vault server support for online data (not archive data)? and can I use a Hardware server with multiple HDDs for AV Server?
CREATE TABLE "COMPDB"."PRODUCTS" ( "PRO_ID" NUMBER NOT NULL ENABLE, "PRO_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, "COMPETITOR" NUMBER NOT NULL ENABLE, "CATEGORY" NUMBER NOT NULL ENABLE,
[Code]..
Now my problem:
In want to delete the blob column(The Content) in Products and the columns, which belong to this column(Filename,Mimetype,Last_update_date). EMPTY_BLOB() doesn't work
There are four columns as follows, but I need to delete those rows from the third column only where the second letter of the word appears as vowel. For example, I want to delete the rows having the words, 'Ramu' and 'Ravi' only.
A B C D xx y Ramu xx yu ut Ameer uui rtt iw Ravi iwoow fgsg isd Intel jjuiw
