Re: SQL and PL/SQL: different results?

Daniel Pinyol wrote:
> If I launch
> select count(*) from ALL_SOURCE;
> from sqlplus, I get 37703.
>
> If I do it from a pl/sql procedure,
> select count(*) into num from all_source;
> dbms_output.put_line(num);
> I get 3769.
>

Here's one for you. Try executing the select in sqlplus as an anonymous PL/SQL block. What results do you get? I would bet 37703. If you do this tells me that your explicit privileges vs. those granted via roles are different. Remember pl/sql packages and sub-programs run with the privileges explictly granted to the create/owner. Also they run with those privileges that are granted at the moment you run them, not those that may have existed at creation time.

-- 

Ed Bruce
<Bruce_at_ha.hac.com>
<ebruce_at_iquest.com>