Can insert but not update in stored procedure ?

From: Gerry West <gwest_at_skyconnect.com>
Date: 1998/01/30
Message-ID: <01bd2db2$8a0628d0$847253ce@deepwater>#1/1

Hi All,

I've attempted my first stored procedure with partial success. I can insert but not update, do I need some kind of special privilege granted ?
Also what is the difference between IS and AS in the procedure creation ?

I executed it in SQL*PLUS using the following lines:

One to insert:
EXECUTE sp_insert_user (99, 'West', 'Gerry', 15, '303444-5555', '8888' )' Commit;
Another to update the existing user:
EXECUTE sp_insert_user (99, 'Gerry', 'West', 11, '1111111111', '1111' )' Commit

Compiled Procedure:

CREATE OR REPLACE PROCEDURE sp_insert_user( userid IN NUMBER, user_name IN VARCHAR, password IN VARCHAR,

                                            security_level IN NUMBER,
paging_phone_number IN VARCHAR,
                                            pin_number IN  VARCHAR
					   )AS
BEGIN
	-- Update an existing user 
        UPDATE  usrs_users
        SET     user_name = user_name, 
		password = password, 
		security_level = security_level, 
		paging_phone_number = paging_phone_number, 
		pin_number = pin_number
        WHERE   user_id = userid;

        -- Else insert a new user
        IF SQL%NOTFOUND THEN
        	INSERT INTO usrs_users
                VALUES( userid, 
			user_name, 
			password, 
			security_level,
			paging_phone_number,	
			pin_number);
        END IF;

END sp_insert_user;
/

Thanks in advance,
Gerry Received on Fri Jan 30 1998 - 00:00:00 CST