Bad performance calling stored procedures

From: Robert Scheer <rbscheer_at_my-deja.com>
Date: 24 Jun 2004 13:18:10 -0700
Message-ID: <cfd22ab6.0406241218.7169b8ef@posting.google.com>

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 Received on Thu Jun 24 2004 - 15:18:10 CDT