Re: SQL and PL/SQL: different results?

From: MarkP28665 <markp28665_at_aol.com>
Date: 1997/01/09
Message-ID: <19970109231300.SAA22356@ladder01.news.aol.com>#1/1

>> From original note --

Hi,

        I'm sure there's something I'm missing.

	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.

<<
A reply stated >>
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. <<

Which is true and is I think the answer, but I would also like to mention the the data dictionary tables (views) that start with ALL_ only show those objects that the querying user has access too. It may be desirable to have 'sys' issue a couple of direct grants on dictionary table, v$ tables, etc., to a production or special id that then uses the dba_xx tables. This way all objects are available and problems like the above are avoided..  

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Thu Jan 09 1997 - 00:00:00 CST