Results 1 to 3 of 3

Thread: get object type

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: get object type

    What query woudl retrieve an objects TYPE:

    SQL>select type from dba_objects where user = 'HR' and objname = 'EMPLOYEES'

    type
    TABLE

    -cf

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: get object type

    A simple "DESC dba_objects" would have shown the answer!

    select object_type from dba_objects where owner = 'HR' and object_name = 'EMPLOYEES';

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I would like to incorporate it into a stored procedure so that I can do something like the following (I'm still learning so consider this pseudocode). Can you use desc in order to retrieve this information within a procedure?

    CREATE PROCEDURE dba_table_privs (obj_name IN VARCHAR2) IS

    grant_str VARCHAR2(500);
    get_val_sql VARCHAR2(500);
    obj_type VARCHAR2(30);
    user VARCHAR2(30);
    role_name VARCHAR2(30);

    BEGIN

    user := GetProcedureOwner() --still haven't figured this one out
    get_val_sql := 'SELECT type FROM dba_objects WHERE name = '''||obj_name||''' and user = '''||user||''''

    obj_type := EXECUTE IMMEDIATE get_val_sql

    IF obj_type = 'TABLE'
    THEN
    role_name := user||'_SELECT_ROLE'
    grant_str := 'GRANT SELECT on '||obj_name||' to '||role_name;
    EXECUTE IMMEDIATE grant_str;
    ELSEIF obj_type = 'PROCEDURE'
    role_name := user||'_EXECUTE_ROLE'
    grant_str := 'GRANT EXECUTE on '||obj_name||' to '||role_name;
    EXECUTE IMMEDIATE grant_str;
    END IF;

    END;

    Thanks,
    Chuck

Posting Permissions

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