DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: How can I manipulate the Explicit Cursor

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374

    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #3
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote 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.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    As I said you have to use Dynamic SQL inside PL/SQL to do what you want

  6. #6
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    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.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

  8. #8
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    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.

  9. #9
    Join Date
    Sep 2007
    Posts
    36
    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;
    /

  10. #10
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    374
    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
  •  


Click Here to Expand Forum to Full Width