Re: SELECT... INTO... problem

How do you know that "p_count is getting set"? The only way the code you provided tests p_count is with the if...0 condition, and it is apparently finding a zero value.

When I suggested the user may not own any objects, I had in mind that you are testing the query as a particular user who owns the objects. But when the procedure is executed, is it run in such a way that the actual, or effective user is different?

In other words, your 'into' and 'if' statements appear 100% correct. (The 'into' is verified by turkbear's example.) That's why I suggest you look elsewhere for a count of zero.

<pete_karanikas_at_hotmail.com> wrote in message news:8gkanm$uv$1_at_nnrp1.deja.com...
> TurkBear,
> Actually, you're right. The p_count is getting set. I'm trying to
> write a procedure that will print out an error if there are no objects
> with the desired object_name found... ie. p_count=0.
> It goes a little "something" like this:
>
> CREATE or REPLACE PROCEDURE countit
> object_search_string varchar2(25) default NULL;
> is
> p_count integer;
> BEGIN
> select count(a.object_name) into p_count
> from user_objects a,
> where a.object_name like '|| object_search_string ||';
>
> IF p_count = 0 THEN
> htp.p( 'Your search returned no results, try again' );
> END IF;
>
> ...
>
> END;
>
> The count seems to work... but the problem is that the IF statement
> ALWAYS executes... so I just assumed that the p_count wasn't getting
> set. Is my IF statement screwed up?
>
> Thanks,
> Pete
>
> In article <392d8be0.25070088_at_news.news-ituk.to>,
> See Message body for real address wrote:
> >
> > Please post your procedure - it should work:
> >
> > I built the following
> >
> > ------------------------------------------------------------
> > Create or Replace procedure Countit is
> > nbr integer;
> > begin
> > select count(*) into nbr from my_employees;
> > dbms_output.put_line('There are '||nbr||' employees in the table');
> > end;
> >
> ------------------------------------------------------------------------
> ------
> >
> > This returns the following
> >
> > SQL> Exec Countit
> >
> > There are 790 employees in the table
> >
> > pete_karanikas_at_hotmail.com wrote:
> >
> > >Why won't this work? (it's within a procedure)
> > >
> > >select count(*) into p_count
> > >from user_tables;
> > >
> > >The p_count has been declared as an INTEGER and is always returned
 as a
> > >value of 0.
> > >
> > >Any ideas?
> > >
> > >Pete
> > >
> > >
> > >Sent via Deja.com http://www.deja.com/
> > >Before you buy.
> >
> > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri May 26 2000 - 00:00:00 CEST