SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted
Feb 21, 2011
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....
However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.
I am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.
My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.
Somebody deleted records from 10,12 tables in one of the schema. I found in one of the forum that table can be restored also if it exists in the recycle bin. I checked by querying:
select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;
> select type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin WHERE TYPE='TABLE' and droptime like '%2012-07-31%'; PHONE TABLE YES YES 2012-07-31:10:23:08 TABLE YES YES 2012-07-31:10:23:08 TABLE YES YES 2012-07-31:10:23:08 TABLE YES YES 2012-07-31:10:23:08 TABLE YES YES 2012-07-31:10:23:08 - - - - I created a test table and dropped it and restored from recycle bin using following flashback table test query....but in my case tables are not dropped...these tables are shoing 0 records. How can I recover only records from recycle bin tables? SQL> flashback table test to before drop;
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.
I have the following table. In this table i'm trying to get the count of records by month when its value (SIGNAGE_ONE_LANE_ROAD_AHEAD) is 'Yes' and its giving me the wrong count.
We have table, which maintain log record of gl table. I don't know how much data exist in that table but problem is taking too much time while counting whole records.
To fetch the count using the above query , it takes 6 mins. There are no other processes running in the DB. The fragmentation % on this table is showing 16.3%
I tried finding the fragmentation using the below query.
select table_name, round((blocks*8),2) "table size kb", round((num_rows*avg_row_len/1024),2) "actual data in table kb", round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb", ((round((blocks*8),2)- round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented" from dba_tables where table_name like ITEM_LIST%' and owner = 'TEST_USR' and blocks != 0 order by 4 desc
TABLE_NAME table size kb actual data in table kb ------------------------------ ------------- ----------------------- wasted space kb percent fragmented --------------- ------------------ ITEM_LIST 5163360 4318302.27 845057.73 16.3664306
I am reading the above file and need to return the total no. of distinct transaction id from the file . How can i acheive this? In the above case distinct transaction id count is 4
We have a front end that is polling the database for some set of data.That set of data is returned by opening a ref cursor and passing it back to the calling environment.Now the problem they also want the count of total number of records that will be fetched by my select statement.One option is execute the select statement once ,get the count and pass it.But in that case i will be executuing the query twice once for count other time while openimng for the ref cursor .
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.
Sqty is sold qty , rqty is return qty , sdate is sold date , vno is billno .
When we enter sdate , this will fetch the products sold on the given date , and total sold qty .
If some products are returned which are entered in rqty , then this should delete all the records of the product on that sdate , and these products as spread along several billno's .
I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.
This is the pre-existing query:
SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER) FROM CPMA.CPMA CPMA WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate') GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM') ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.
/* Formatted on 22/01/2013 19:32:50 */ CREATE OR REPLACE PROCEDURE test_rdm_miles ( p_ref_cursor OUT SYS_REFCURSOR p_success NUMBER) IS BEGIN OPEN p_ref_cursor FOR SELECT 5168 mem_uid,
[code]....
I have a Procedure with out parameters as a REF CURSOR and response message as p_success.This ref cursor will be returned to the calling service. Is there a way in oracle by which we can identify whether the Ref cursor holds data without actually fetching it. Since if i choose to fetch the data, i will lose one row when i return the ref cursor back to the calling service Or else is there way i can retrieve the row i lose during fetch.
Other alternative what have been suggested is create an object type ,fetch the ref cursor values in object type. Then i can use the ref cursor to return the data by table casting.
one more solution is
OPEN FETCH CLOSE OPEN (AGAIN) { this will lead to redundancy)
My aim is to delete duplicates out of above data, with the below condtions.
1) first record with value 30 and then with value 0. 2) if there are 3 duplicate records ex: ID is 1 and Group KK, then i have to delete both 30 & 0 qty records. 3) If there are more than 3 duplicate records ex: ID is 3 and Group is AA, the i have to delete all the records with qty value either 30 or 0 and.
I have written a query like below.
SELECT id, unit, RANK () OVER (PARTITION BY id, unit ORDER BY id, unit) num FROM temp;
with the above query, i am unable to mark this dynamic duplications.
I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.
What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.
ORA-29876: failed in the execution of the ODCIINDEXDELETE routine ORA-20000: Oracle Text error: DRG-50857: oracle error in textindexmethods.ODCIIndexDelete ORA-20000: Oracle Text error: DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA DRG-50857: oracle error in drexrdml ORA-01031: insufficient privileges [code]....
