Re: !!!Need help with stored pros

Nikita (nikita_at_remus.rutgers.edu) wrote:

: I am having a problem using stored procedures.
 

: I created a simple stored procedure that selects a count(*)
: from a table using a where clause. I then tested the value of
: the count to see if there is data or not. I am using SQL Plus
: to test the procedure. If the table has data or not,
: the procedure executed successfully.
 

: The only problem is why the value of the count is not equal
: to zero when there is no data.
 

: e.g Select count(*) into X from table1 where field1 = input
 

: if X = 0 then
: raise_application_errors ( -0001,'message');
: end if;
 

: I would greatly appreciate assistance of any kind!!!
: e-mail: nikita_at_remus.rutgers.edu
: Phone#: (908) 719-4416 (O)
: Fax#: (908) 781-4959 (Att:SECC)x
: uit

Nikita,

You CANNOT use -0001 as an error number in a raise_application_error. Although your procedure will STORE without any errors, it WILL give a run-time error. (ORA-21000). I am not sure if that is your problem.

It is hard to believe that "X" would not be 0, if there are no rows in "table1".

Let me know if you still face problems.

Regards.

--
-----------------------------------------------------------------------------
Kannan 
Email: skannan_at_fs.com

The above are my own comments and opinion. They do not purport that of 
anybody else.
-----------------------------------------------------------------------------