Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Location
    Hyderabad
    Posts
    18

    Unanswered: subprogram(vithalreddy)

    1.Design A Sub Program Which Can Take The Audit of Number of Records And Number of Effected Tables Whenever A Record is Deleted OR Updated In The Master Table?

    2.Design A Sub Program Which Can Take The Audit of Number of Tables That Are Expected With Relational Data When One Record is Inserted Into The Master Table?

    SELECT COUNT(TABLE_NAME)
    FROM ALL_CONSTRAINTS
    WHERE R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
    FROM ALL_CONSTRAINTS
    WHERE TABLE_NAME=&TABLENAME AND CONSTRAINT_TYPE IN('P','U'))
    /
    this query returns...how many tables are depending on this table?
    it is giving all tables in my project...example...15 tables are depending
    if you implement in subprogram it is giving present user tables count only why?

    CREATE OR REPLACE PROCEDURE SIXTEEN(P_TABLE VARCHAR2)
    AS
    V_TABLE VARCHAR2(30):=P_TABLE;
    V_COUNT NUMBER(3);
    BEGIN
    SELECT count(TABLE_NAME) INTO V_COUNT
    FROM ALL_CONSTRAINTS
    WHERE R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
    FROM ALL_CONSTRAINTS
    WHERE TABLE_NAME=V_TABLE
    AND CONSTRAINT_TYPE IN ('P','U'))
    AND R_OWNER=(SELECT OWNER
    FROM ALL_TABLES
    WHERE TABLE_NAME=V_TABLE AND OWNER LIKE '%EWH%' );
    DBMS_OUTPUT.PUT_LINE(V_COUNT);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('THERE IS NO SUCH TABLE OR IT IS MASTER TABLE');
    END;
    /
    Last edited by vithalreddy; 11-02-11 at 06:28.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I'm sorry, we will help with homework after an effort has been made. We won't do it for you. Try and show us what you tried.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is really silly, with all these DASP requirements ... you were told several times that this is not a way to ask for assistance.

  4. #4
    Join Date
    Sep 2011
    Location
    Hyderabad
    Posts
    18

    vithal reddy

    SELECT COUNT(TABLE_NAME)
    FROM ALL_CONSTRAINTS
    WHERE R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
    FROM ALL_CONSTRAINTS
    WHERE TABLE_NAME=&TABLENAME AND CONSTRAINT_TYPE IN('P','U'))
    /

    do you know what this query result?
    i need this in subprogram. you don't know leave it. Don't discourage people.
    ....

  5. #5
    Join Date
    Sep 2011
    Location
    Hyderabad
    Posts
    18

    Vithal

    Actually...i am not going to ask such questions but this question
    is killing me from 5 days?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    if you implement in subprogram it is giving present user tables count only why?
    Because your procedure includes
    Code:
    AND OWNER LIKE '%EWH%'
    What happens if you remove this line?

    Besides, code you posted wouldn't work as you are trying to store TABLE_NAME into a local variable whose datatype is a NUMBER. So, maybe you should post code that actually works (doesn't matter if it won't produce result you expect, but don't let it fail because of such an error).

  7. #7
    Join Date
    Sep 2011
    Location
    Hyderabad
    Posts
    18
    Quote Originally Posted by Littlefoot View Post
    Because your procedure includes
    Code:
    AND OWNER LIKE '%EWH%'
    What happens if you remove this line?

    Besides, code you posted wouldn't work as you are trying to store TABLE_NAME into a local variable whose datatype is a NUMBER. So, maybe you should post code that actually works (doesn't matter if it won't produce result you expect, but don't let it fail because of such an error).
    sorry i forgotten to write count there?

    in my database upto 5 user are there like %ewh% but it is current user tables count.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by vithalreddy View Post
    this query returns...how many tables are depending on this table?
    it is giving all tables in my project...example...15 tables are depending
    if you implement in subprogram it is giving present user tables count only why?
    You already got the answer in your previous thread: http://www.dbforums.com/oracle/1671250-procedure.html
    I am afraid, that even after creating another thread with the same question, the answer will not change.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •