Results 1 to 7 of 7

Thread: procedure

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

    Unanswered: procedure

    CREATE OR REPLACE PROCEDURE FIFTEEN AS
    V_COUNT NUMBER(5);
    BEGIN
    SELECT COUNT(TABLE_NAME) INTO V_COUNT
    FROM ALL_CONSTRAINTS
    WHERE R_CONSTRAINT_NAME ='CNS_EMPMSTR_EMPID_PK';
    DBMS_OUTPUT.PUT_LINE(V_COUNT);
    END;

    if enter above query in sql it is returning only login users count tables?
    but if you run individual query i mean (SELECT COUNT(TABLE_NAME) INTO V_COUNT
    FROM ALL_CONSTRAINTS
    WHERE R_CONSTRAINT_NAME ='CNS_EMPMSTR_EMPID_PK';
    DBMS_OUTPUT.PUT_LINE(V_COUNT);
    it is giving all users count why?
    How to found dependent tables on one table through procedure
    parameters are table name,column name?
    can you help me?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    privileges acquired via ROLE do NOT apply within named PL/SQL procedures.

    you can replicate behavior by doing as below
    SQL> SET ROLE NONE
    SQL> SELECT COUNT(TABLE_NAME) FROM ALL_CONSTRAINTS WHERE R_CONSTRAINT_NAME ='CNS_EMPMSTR_EMPID_PK';
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2011
    Location
    Hyderabad
    Posts
    18
    Thank you ...my sir given one assignment
    that is if you enter table name the procedure should calculate the relationships with other tables.(not in one user for hole users.. In my project 5 users are there )...
    Can you help me...thanks for your feed my back my dear..

  4. #4
    Join Date
    Sep 2011
    Posts
    75

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

    vithal reddy

    how to achieve it

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how to achieve it
    I do not understand to what "it" refers.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by vithalreddy View Post
    how to achieve it
    I also do not know what "it" is. However, I would not use ALL_CONSTRAINTS in this case; using DBA_CONSTRAINT (or any other DBA_* system static view for different piece of information) would make much more sense. Of course, before creating it, SELECT privilege on this object has to be directly granted to the owner of that procedure (whoever it is).

Posting Permissions

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