Hi.

I wrote some stored procedures used by a web application. As I am
fairly new to Oracle, I am missing some concepts when creating these
procedures, as a result, the application is suffering from poor
performance everytime I call these procedures. I would like your help
in order to identify potential mistakes when coding these procedures
and I am posting a package and a procedure I use very often on the
system, as an example:

CREATE OR REPLACE PACKAGE WebCursor AS
TYPE REF_CUR IS REF CURSOR;
END WebCursor;

CREATE OR REPLACE PROCEDURE appweb.GetUser (pLogin VARCHAR2, cUser OUT
WebCursor.REF_CUR)
AS
BEGIN
OPEN cUser FOR
SELECT name,login,email
FROM appweb.User
WHERE login = pLogin;
END GetUser;


Almost all the procedures return a cursor and some of them receives
input parameters. Can you help me to identify bad practices in my
code?

Thanks,
Robert Scheer

Hi Robert

1)Try calling the refcursors from sql*plus itself and see if they are
running slow

2)How does this query perform in sql plus

SELECT name,login,email
FROM appweb.User
WHERE login = pLogin;


is it fast ? Do you have a index on login column ? if no then create a
index..

3)If the query is fast then you might have too lok at other things
like your asp pages webserver as the bottleneck might not be in the
database

regards
Hrishy

rbscheer (AT) my-deja (DOT) com (Robert Scheer) wrote in message news:<cfd22ab6.0406241218.7169b8ef (AT) posting (DOT) google.com>...