this is my trigger..SQL> create or replace trigger drop_trigger
2 before drop on database
3 declare
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT INTO ddl_log
7 SELECT ora_sysevent,ora_dict_obj_type, ora_dict_obj_owner,
8 ora_dict_obj_name,USER,SYSDATE,to_char(sysdate,'hh24:mi:ss')
9 FROM dual;
10 commit;
11 raise_application_error(-20090,'you can not drop');
12 end;
13 /
DÚclencheur crÚÚ. when a user enter a "drop table" for exemple, the trigger is run but we have this error message
SQL> drop table fg;
drop table fg
*
ERREUR Ó la ligne 1 :
ORA-00604: une erreur s'est produite au niveau SQL rÚcursif 1
ORA-20090: you can not drop
ORA-06512: Ó ligne 9
I'm writing PRO C program with oracle 8i. My process first connect to the database with using ora_connect(), proceed the task then diconnect with using ora_disconnect(). This would always in a loop, recently after few cycle of connect and disconnect, when the next issue connect statement, it was unable to connect and return me ora-00604 and the process end. Even i restart the process, after few cycle also the same return me ora-00604 during the connect.
I am using Oracle 10g Database as back end and Developer 6i as front end.I have a procedure which is called upon SAVE. Means before COMMIT;This procedure holds few updates and delete statments.
This prcedure is throughing sql error as follows: ORA-00604: error occurred at recursive SQL level
The error occur when an delete statment is issued which as follows: DELETE FROM DUMMY_TAB001;
I issue the same statement from SQL it is running fine. But when my from runs on this delete statment encounter it through the above oracle error ORA-00604.
Oracle Version :- 11.2.0.2 I found a error in a trigger(Statement Level) ORA-00604:error occurred at recursive SQL level string. Before Finding this issue,Once the DB Response was slow . Will this be the issue Of DB Slow response. The Above trigger fires for each entry in an transaction table. The code is Patched and was executed . The above issue was found during another issue and not the DB Slow response. My Doubt is Whether DB response slow issue would be because of this. Now after fixing this Slow response was not reported.
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.
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?
what is the error in this procedure.I am getting error where s.msg_id = Tbl_Repo_Salo (i.msg_id));
Create or replace Procedure Proc_Stg_Clear(P_Err_Code OUT NOCOPY number, P_err_msg OUT NOCOPY varchar2) as v_count number; Type Repo_Salo is table of Tbl_Tml_msg_Salo_Stg%ROWTYPE; Type Repo_Smb is table of Tbl_Tml_msg_SMB_Stg%ROWTYPE;
i have one question that why oracle not set high water mark when we delete data from a table and commit it, on the other hand it set in case of truncate.both of these statement release physical structure(in case of delete after commiting)
i have a problem that i have created a table named PROJECT and structure is like- CREATE TABLE PROJECT ( id VARCHAR2(250 BYTE) NOT NULL, NAME VARCHAR2(250 BYTE) NOT NULL, COMPID NUMBER NOT NULL,
[code]...
and this table is created in some number of different database then i want to delete the not null constraint of "id" column and there is 3 not null constraint in my table and i didnot gave name at the time of creation, so we cant delete it by its name in the script because there will be different name in another database for the same constraint. i want to make a script to delete the not null constraint because it is the part of my script.
how should i get proper information for deleting the constraint. and i used "user_constraints" but it shows same information for the all three "not null" constraint.
My Question is, since this 3 indexes has two columns "Bus_unit and Md_code", which index can i delete. Will they effect on database performance, if i delete one of them?
From plan I would expect the delete command to be processed very quickly, however it does huge amounts of db block gets. I assume this is because of deleting the row from other structures (indexes, foreign keys in tables etc.) How can I find out which object scan caused the db block gets ?
SQL> delete from messages2 where id = 11004240718; 1 row deleted. Elapsed: 00:00:03.79 Execution Plan ---------------------------------------------------------- Plan hash value: 140582024
, we have used alter table to change the constraint in on delete cascade. there is two option. what is different between both ofthem. Example:1)Alter table t3 add constraint t2_F foreign key (id) references t2(id,column2,column3) on delete cascade ;or2)Alter table t3 add constraint t2_F foreign key (id) references t2 on delete cascade ;
know first one that particular column will set to on delete cascade.What is the use of second one in this case we are not using column name in "foreign key (id) references t2 on delete cascade ;"
I can delete the old archives with extension arc. Today, when I consult V$RECOVERY_FILE_DEST, there are files with today date. Can I delete these files, without danger the database?
I'm attempting to write some SQL to delete a record where it doesn't exist in one of three other tables. My SQL is below:
SELECT COUNT(*) FROM GAZETTEER_ADDRESSES WHERE NOT EXISTS ( SELECT MEDICALS.*, REVIEWS.*, ADDRESS_HISTORIES.* FROM MEDICALS INNER JOIN REVIEWS ON MEDICALS.GADDRE_IDENTIFIER = REVIEWS.GADDRE_IDENTIFIER JOIN ADDRESS_HISTORIES ON REVIEWS.GADDRE_IDENTIFIER = ADDRESS_HISTORIES.GADDRE_IDENTIFIER WHERE GAZETTEER_ADDRESSES.IDENTIFIER = MEDICALS.GADDRE_IDENTIFIER);
When I attempt to run this I receive the following error: ORA-00600: internal error code, arguments: [qcopcovm2], [0], [], [], [], [], [], []
I know it's not generally a good idea to delete records but in this case a large number of cases are incorrect and need to be reinserted and this is the quickest way of doing it.
I am trying to run following sql query,but it is throwing following error.
SQL> delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CON NECTION_ID_TEMP=COMPLEMENTS.feature_conn_id 2 WHERE a.current_designation is null and a.current_low is null and a.current _high is null;
delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CONNECTI ON_ID_TEMP=COMPLEMENTS.feature_conn_id * ERROR at line 1: ORA-00933: SQL command not properly ended
2)select client_id, count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id having count(*) > 40 order by 2 desc client_id count(*) ----------- --------- 16205601785128
3) select client_id,clnt_notification_pack_tid -- bulk collect into v_client_id,v_notif_tid from (select clnt_notification_pack_tid, client_id, clnt_notification_pack_typ_tid, crte_dt,
[code]....
4) Iam using the below proc to delete the rows from table, except the 4 rows returned above
declare v_clnt_notification_pack_tid TCLIENT_NOTIFICATION_PACK.CLNT_NOTIFICATION_PACK_TID%type; tYPE t_client_id is table of TCLIENT_NOTIFICATION_PACK.client_id%type; tYPE t_notif_tid is table of TCLIENT_NOTIFICATION_PACK.clnt_notification_pack_tid%type; v_client_id t_client_id; v_notif_tid t_notif_tid;
[code]....
5) After running this procedure, i shud see 5124 records, but i see zero records.
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 facing a problem while trying to delete an OS file using PL/SQL in Oracle8i v8.1.7.I have tried to use UTL_FILE.fremove but it is not supported in Oracle8i.
how can I delete a file Test.txt from C:Test location?
I want to delete the duplicate rows in a table. there is no column which can be used to differentiate between the rows.
SELECT column_name1 FROM [table] WHERE column_name2 = cond GROUP BY column_name1 HAVING COUNT (column_name1) > 1
from the above query i can find the duplication in the table and can delete through it. But what i want one record of each duplication not to be deleted most probably the record added last to the table.
I am selecting around 10000 records in a recordset using an sql query.I need to delete these 10000 records from 10 different tables as each of the table have these records.I can use a FOR Loop for each record one by one to delete these 10K records from each tabel but i was wondering if i can delete there 10K in one go so that the program would Loop Only once rather thatn 10K times.
Mine Delete code looks like:
if x.first is not null then for i in x.first..x.last loop delete tbl_A where trade_id = x(i).trade_id; delete tbl_B where trade_id = x(i).trade_id;
[code]...
Where x is the recordset having around 10K records.Is there any way i can run the loop only once rather than one by one for 10K times.
I want to delete the duplicated record using following methods, it delete the records if all the record are matched according to condition, but if all records are not matched then it display error messages.
SQL> SELECT * FROM XYZ;
NAME FNAME ADDRESS JOB -------------- -------------------- -------------------- ----------------------------------------- Bilal Khan Wali your rehman name district abbottabad student Bilal Khan Wali your rehman name district abbottaba student Bilal Kh Wali your rehma name district abbotta studen Bilal K Wali your rehm name district abbotta studen Bilal Khan Wali your rehman name istrict abbottaba tudent lal Khan i your rehman name strict abbottaba tudent [code]....
