Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: count from ALL_DIRECTORIES differs

    Could someone explain the discrepancies in the count(*)?

    Code:
    SQL> connect forbesc@prod
    Enter password: ******
    Connected.
    SQL> create or replace function shared.count_dir return integer
      2  as
      3    cnt number;
      4  begin
      5    select count(*) 
      6    into cnt
      7    from all_directories;
      8    
      9    return cnt;
     10  end;
     11  /
    
    Function created.
    
    SQL> select shared.count_dir from dual;
    
     COUNT_DIR
    ----------
            65
    
    SQL> connect shared@prod
    Enter password: ******
    Connected.
    SQL> select count(*) from all_directories;
    
      COUNT(*)
    ----------
           100

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The shared schema has access to more directory objects then the forbesc directory.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    But I just used my login to create a PACKAGE in the SHARED schema:

    Code:
    create or replace function SHARED.count_dir 
    ...
    So, that PACKAGE should be running under SHARED's privileges, right?
    --=Chuck

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


    execute below
    Code:
    SQL> SET ROLE NONE
    select count(*) from all_directories;
    & post results here
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chuck_forbes

    So, that PACKAGE should be running under SHARED's privileges, right?
    Schema is just a name; the function owner is what matters if the function is created with definer rights, which is the case. You could have created your function in the SYS schema, given enough permissions, and it would still only see objects available to "forbesc".
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    So then, I'm getting confused. From what you're saying, would you expect the following

    Code:
    SQL> connect forbesc@prod
    Enter password: **********
    Connected.
    SQL> select shared.count_dir from dual;
    
     COUNT_DIR
    ----------
            65
    
    SQL> select count(*) from all_directories;
    
      COUNT(*)
    ----------
           100
    
    SQL> connect shared@prod
    Enter password: ********
    Connected.
    SQL> select shared.count_dir from dual;
    
     COUNT_DIR
    ----------
            65
    
    SQL> select count(*) from all_directories;
    
      COUNT(*)
    ----------
           100
    Directory objects which are granted READ,WRITE to public aren't showing up in the list from ALL_DIRECTORIES when run from the FUNCTION. --=cf

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    See anacedent's response.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    So, maybe I'm just leaving out the information I'm really trying to figure out. We have a LAN drive mounted to our LINUX server, where files are read into & out of Oracle. So, the directory_path stored in Oracle doesn't match the LAN definition that the user's are used to seeing. So, I wrote a program which translates the directory_path into a LAN path. But, directory objects which are being created identically, are not all showing up

    Code:
    SQL> create or replace function shared.dir_exists (p_dir varchar2) return varchar2
      2  as
      3    l_dir varchar2(30);
      4  begin
      5    begin
      6      select directory_name
      7      into l_dir
      8      from all_directories
      9      where directory_name = p_dir;
     10    exception
     11      when no_data_found then
     12     l_dir := 'NOT THERE';
     13    end;
     14    
     15    return l_dir;
     16  end;
     17  /
    
    Function created.
    
    SQL> select shared.dir_exists('BCDLIC_IN_D') as DIR from dual;
    
    DIR
    --------------------------------------------------------------------------------
    BCDLIC_IN_D
    
    SQL> select shared.dir_exists('PADS_IN_D') as DIR from dual;
    
    DIR
    --------------------------------------------------------------------------------
    NOT THERE
    But this is how they were both created

    Code:
    CREATE OR REPLACE DIRECTORY 
    BCDLIC_IN_D AS 
    '/mount1/bcdlic/';
    
    
    GRANT READ, WRITE ON DIRECTORY  BCDLIC_IN_D TO FORBESC WITH GRANT OPTION;
    
    GRANT READ, WRITE ON DIRECTORY  BCDLIC_IN_D TO PUBLIC;
    
    CREATE OR REPLACE DIRECTORY 
    PADS_IN_D AS 
    '/mount1/pads/';
    
    
    GRANT READ, WRITE ON DIRECTORY  PADS_IN_D TO FORBESC WITH GRANT OPTION;
    
    GRANT READ, WRITE ON DIRECTORY  PADS_IN_D TO PADS;
    Plus, in DEV, these 2 are being found just fine.

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    No READ or WRITE grants provided to PUBLIC for PADS_IN_D. Don't know why it took me so long to see it. --=cf

Posting Permissions

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