-
How can I manipulate the Explicit Cursor
Hi Guys
Here is problem.
We have a partition table for three (3) users that needed to be:
1) Truncated if the count(*) of the following statement returns zero value and send an email to xxxx@hotmail.com with message" Partition name 1234 successfully truncated"
2) if row count(*) is greater than zero (0) then exit and send an email to xxxx@hotmail.com with " message partition name 5678 has x number of rows that needed to be deleted first.
So I wrote an explicit cursur but don't how I can manipulte it to meet the above condition.
Any suggestion and code with be higly appreciated.
Thanks
HERE IS MY STATEMENT
==================
SELECT 'SELECT COUNT(*) from '||TABLE_OWNER||'.'||TABLE_NAME||' partition ('||PARTITION_NAME||');' FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER IN ('HMDP','DWDP','SWDR') ORDER BY TABLE_OWNER
HERE IS THE CURSOR
===============
Declare
CURSOR c_partition IS
select table_owner, table_name,partition_name from dba_tab_partitions;
v_table_owner dba_tab_partitions.table_owner%type;
v_table_name dba_tab_partitions.table_name%type;
v_partition_name dba_tab_partitions.partition_name%type;
Begin
OPEN c_partition ;
Loop
FETCH c_partition into v_table_owner,v_table_name,v_partition_name;
Exit when c_partition %NOTFOUND;
Dbms_output.put_line(v_table_owner || ', ' || v_table_name||' , '|| v_partition_name);
End loop;
CLOSE c_partition ;
End;
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
if I understand correctly yu have to use dynamic SQL
also you should not need count(*), if you want to test for rows existence
Code:
select count(*)
from dual
where exists (select null from tab1 where rownum < 2)
or
select 1
into x
from tab1
where rownum < 2
and test for sql%rowcount
-
Pando,
Could you or anyone help solve the problem?
Any suggestion is highly appreciated.
Thanks
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
Originally Posted by Ablakios
Could you or anyone help solve the problem?
Something is not clear to me... are you deleting all the rows of a partition and then truncating it?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
As I said you have to use Dynamic SQL inside PL/SQL to do what you want
-
PAVB
The condition is:
Truncate if the row count(*) returns zero value and send an email to xxxx@hotmail.com with message" Partition name 1234 successfully truncated" and
if row count(*) is greater than zero (0) then exit and send an email to xxxx@hotmail.com with " message partition name 5678 has x number of rows that needed to be deleted first.
PAND
You said that I have to use Dynamic SQL inside PL/SQL to do what you want.
Could you show me how please?
Thanks
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
-
Pando,
Thanks for the info. You know I am new in this DYNAMIC SQL/PL SQL thing. Could you please help me here by directing me as to how I should start or proceed bearing in min the condition?
Thank you beforehand.
Last edited by Ablakios; 09-17-2007 at 12:32 PM.
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
something like this?
DECLARE
CURSOR cGetTableName
IS
SELECT table_name
FROM user_tables;
nRowCount NUMBER;
BEGIN
FOR TableNameRec in cGetTableName
LOOP
EXECUTE IMMEDIATE 'SELECT count(*)
FROM '||TableNameRec.table_name
INTO nRowCount ;
DBMS_OUTPUT.PUT_LINE('Table: '||TableNameRec.table_name||' has '||nRowCount||' row(s)');
IF nRowCount > 0
THEN
DBMS_OUTPUT.PUT_LINE('email - message table '||TableNameRec.table_name||' has '||nRowCount||' number of rows that needed to be deleted first');
ELSE
DBMS_OUTPUT.PUT_LINE('email - message table '||TableNameRec.table_name||' empty - truncate for some reason');
END IF;
END LOOP;
END;
/
-
Jay
Thanks I will try and letb you know.
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|