Home » SQL & PL/SQL » SQL & PL/SQL » Error in Stored Procedure
Error in Stored Procedure [message #36746] Mon, 17 December 2001 07:05 Go to next message
Srinivas S
Messages: 4
Registered: November 2001
Junior Member
below is a procedure which i m yet to call in a ASP frontend. Basically what id does is takes a alphabet as parameter like A or B and it displays the list of users, their firstname starting from that alphabet.
when i write this oracle displays a message, procedure invalid.

could someone tell me where i have gone wrong.


Procedure sp_acs_gallery_getusers(v_alphabet in varchar2, io_cursor out t_cursor)
is begin
open io_cursor for
Select * from users where status <> 0 and firstname like "'%" || v_alphabet || "%'" or firstname like "'%" || to_upper(v_alphabet) || "%'";

end sp_acs_gallery_getusers;

Re: Error in Stored Procedure [message #36903 is a reply to message #36746] Wed, 02 January 2002 19:38 Go to previous message
Raju B
Messages: 1
Registered: January 2002
Junior Member
Hi Srinivas,

I believe this procedure which you are writing is a part of a package. If not it will not work because the datatype t_cursor has to be declared first and then only it's type can be used to declare io_cursor out parameter of the procedure.

You will have to create a package specification and package body, within which you will have to create procedure specification and procedure body respectively. Moreover, the datatype for the cursor should be defined in the package specification's declare section and then it can be inheritated by the out parameter of the procedure.

Try this out....this should solve more than half the problem. Once this is done, we shall look at the other half.

Previous Topic: Problem with explicit cursor
Next Topic: How to send mail to more than one people from plsql
Goto Forum:

Current Time: Sat May 25 04:12:00 CDT 2024