FAQ
This one's got me scratching my head. So, before I
scratch a hole in it ...

When I execute the below query from SQL*Plus/TOAD/etc,
I have no problem getting the rows back. But, when
placed in a procedure within a package, it returns
no_data_found.

select *
from all_indexes
where 1 = 1
and owner = upper ( cp_owner )
and table_name = upper ( cp_table_name )
order by index_name

We had a similar issue querying all_part_key_columns,
but resolved it by connecting to the schema owner and
granting "alter table" to oracle (the package owner).
But doing the same for the index has not resolved it.

Any thoughts?

Thanks in advance,
Jon Knight

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Bobak, Mark at Sep 14, 2006 at 9:58 pm
    Jonathan,

    If the owner of the PL/SQL stored object has grants on
    cp_owner.cp_table_name only via a role, the query will return no rows
    from within PL/SQL because roles are disabled in PL/SQL.

    Try granting the owner of the PL/SQL object a direct grant (select is
    good enough) on cp_owner.cp_table_name.

    Hope that helps,

    -Mark

    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    "A human being should be able to change a diaper, plan an invasion,
    butcher a hog, conn a ship, design a building, write a sonnet, balance
    accounts, build a wall, set a bone, comfort the dying, take orders, give
    orders, cooperate, act alone, solve equations, analyze a new problem,
    pitch manure, program a computer, cook a tasty meal, fight efficiently,
    die gallantly. Specialization is for insects." --Robert A. Heinlein

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jonathan Knight
    Sent: Thursday, September 14, 2006 5:41 PM
    To: oracle-l_at_freelists.org
    Subject: Can't see index from package

    This one's got me scratching my head. So, before I scratch a hole in it
    ...

    When I execute the below query from SQL*Plus/TOAD/etc, I have no problem
    getting the rows back. But, when placed in a procedure within a
    package, it returns no_data_found.

    select *
    from all_indexes
    where 1 = 1
    and owner = upper ( cp_owner )
    and table_name = upper ( cp_table_name )
    order by index_name

    We had a similar issue querying all_part_key_columns, but resolved it by
    connecting to the schema owner and granting "alter table" to oracle (the
    package owner).
    But doing the same for the index has not resolved it.

    Any thoughts?

    Thanks in advance,
    Jon Knight

    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Knight at Sep 15, 2006 at 6:33 pm
    Mark,
    Your suggestion actually fixed our issue querying an
    "all_tab..." view. Now, we're facing "all_indexes".

    We've granted select, update, delete, & alter on the
    table (which has the index) to the package owner.
    We've also granted ...
    INDEX

    ALTER ANY INDEXTYPE

    EXECUTE ANY INDEXTYPE

    I know we're just shooting from the hip at the
    moment, but hoping to find the right one ...

    Thanks,
    Jon Knight

    "Bobak, Mark" wrote:
    Jonathan,

    If the owner of the PL/SQL stored object has grants
    on
    cp_owner.cp_table_name only via a role, the query
    will return no rows
    from within PL/SQL because roles are disabled in
    PL/SQL.
    Try granting the owner of the PL/SQL object a direct
    grant (select is
    good enough) on cp_owner.cp_table_name.

    Hope that helps,

    -Mark


    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    "A human being should be able to change a diaper,
    plan an invasion,
    butcher a hog, conn a ship, design a building, write
    a sonnet, balance
    accounts, build a wall, set a bone, comfort the
    dying, take orders, give
    orders, cooperate, act alone, solve equations,
    analyze a new problem,
    pitch manure, program a computer, cook a tasty meal,
    fight efficiently,
    die gallantly. Specialization is for insects."
    --Robert A. Heinlein



    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    Jonathan Knight
    Sent: Thursday, September 14, 2006 5:41 PM
    To: oracle-l_at_freelists.org
    Subject: Can't see index from package

    This one's got me scratching my head. So, before I
    scratch a hole in it
    ...

    When I execute the below query from
    SQL*Plus/TOAD/etc, I have no problem
    getting the rows back. But, when placed in a
    procedure within a
    package, it returns no_data_found.

    select *
    from all_indexes
    where 1 = 1
    and owner = upper ( cp_owner )
    and table_name = upper ( cp_table_name )
    order by index_name

    We had a similar issue querying
    all_part_key_columns, but resolved it by
    connecting to the schema owner and granting "alter
    table" to oracle (the
    package owner).
    But doing the same for the index has not resolved
    it.

    Any thoughts?

    Thanks in advance,
    Jon Knight



    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam
    protection around
    http://mail.yahoo.com
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfgang Breitling at Sep 16, 2006 at 1:42 pm
    You'll need to grant "references" on the table directly to the package owner.
    At 12:33 PM 9/15/2006, Jonathan Knight wrote:
    Mark,
    Your suggestion actually fixed our issue querying an
    "all_tab..." view. Now, we're facing "all_indexes".

    We've granted select, update, delete, & alter on the
    table (which has the index) to the package owner.
    We've also granted ...
    INDEX
    ALTER ANY INDEXTYPE
    EXECUTE ANY INDEXTYPE
    I know we're just shooting from the hip at the
    moment, but hoping to find the right one ...
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    http://www.centrexcc.com
  • Ian Cary \(C\) at Sep 18, 2006 at 1:20 pm
    Jonathan,



    Another thing you could try is setting invoker rights on the procedure
    being created as this enables the procedure use privileges granted to
    roles (something to do with process using the invokers namespace and
    privileges rather than having to know about the procedure owners
    privileges/namespace as well as the invoker).



    Anyhow as an example;



    SQL> show user

    USER is "IAN"



    SQL> create table testtab(x date);



    Table created.



    SQL> create role testrole;



    Role created.



    SQL> grant select on testtab to testrole;



    Grant succeeded.



    SQL> grant testrole to dummy;



    Grant succeeded.



    Now connect to dummy



    1 select table_name from all_tables

    2* where owner = 'IAN';



    TABLE_NAME

    TESTTAB



    SQL> create or replace procedure show_tabs as

    2 tab_count number;

    3 begin

    4 select count(*)

    5 into tab_count

    6 from all_tables

    7 where owner = 'IAN';

    8 dbms_output.put_line(tab_count);

    9 end;

    10 /



    Procedure created.



    SQL> exec show_tabs;

    0 -- as expected



    PL/SQL procedure successfully completed.



    SQL> create or replace procedure show_tabs authid current_user as

    2 tab_count number;

    3 begin

    4 select count(*)

    5 into tab_count

    6 from all_tables

    7 where owner = 'IAN';

    8 dbms_output.put_line(tab_count);

    9 end;

    10 /



    Procedure created.



    SQL> exec show_tabs

    1 -- you can see the table now



    PL/SQL procedure successfully completed.





    Hope this helps,



    Cheers,



    Ian

    .

    This email is only intended for the person to whom it is addressed and may contain confidential information. If you have received this email in error, please notify the sender and delete this email which must not be copied, distributed or disclosed to any other person.

    Unless stated otherwise, the contents of this email are personal to the writer and do not represent the official view of Ordnance Survey. Nor can any contract be formed on Ordnance Survey's behalf via email. We reserve the right to monitor emails and attachments without prior notice.

    Thank you for your cooperation.

    Ordnance Survey
    Romsey Road
    Southampton SO16 4GU
    Tel: 023 8079 2000
    http://www.ordnancesurvey.co.uk

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 14, '06 at 9:40p
activeSep 18, '06 at 1:20p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase