| PL/SQL exception code can be improved?? [message #38182] |
Thu, 28 March 2002 14:55  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
I want to create a procedure that performs an update. If the prodid is not in the the product table then an exception handling code is performed.
Here is not a very good solution which does not work but I think code can be improved?
CREATE OR REPLACE PROCEDURE upd_prod
(v_prodid IN product.prodid%TYPE,
v_descrip IN product.descrip%TYPE)
v_no_data VARCHAR2(60);
IS
BEGIN
UPDATE product
SET descrip = v_descrip
WHERE prodid = v_prodid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_no_data :='The product id number does not exist, please try another number';
END;
/
ERROR:
Errors for PROCEDURE UPD_PROD:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PLS-00103: Encountered the symbol "V_NO_DATA" when expecting one
of the following:
; is with as
The symbol "is" was substituted for "V_NO_DATA" to continue.
5/1 PLS-00103: Encountered the symbol "BEGIN" when expecting one of
the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form
14/0 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
begin function package pragma procedure form
|
|
|
|
| Re: PL/SQL exception code can be improved?? [message #38183 is a reply to message #38182] |
Thu, 28 March 2002 15:31  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
A DML statement will never generate a no_data_found exception, instead, you either update (or delete) 0 or more rows, so you can use sql%rowcount to check this.
Also, your v_no_data parameter needs to be an OUT parameter to return the value back to the calling program.
create or replace procedure upd_prod (
v_prodid in product.prodid%type,
v_descrip in product.descrip%type)
v_no_data out varchar2(60);
is
begin
update product
set descrip = v_descrip
where prodid = v_prodid;
if sql%rowcount = 0 then
v_no_data :='The product id number does not exist, please try another number';
end if;
end;
/ |
|
|
|
| Re: PL/SQL exception code can be improved?? [message #38192 is a reply to message #38182] |
Fri, 29 March 2002 16:13 |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
I have a procedure that queries an employer table and gets couple of informations. The query below works.
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE query_emp_get_sal_job
(v_id IN emp.empno%TYPE,
v_sal OUT emp.sal%TYPE,
v_job OUT emp.job%TYPE)
IS
BEGIN
SELECT sal, job
INTO v_sal, v_job
FROM emp
WHERE empno = v_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('You have entered an erroneous employee number please try with another number.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp_get_sal_job;
/
I have tried other versions of the query. But the exception does not work, that is the v_msg works good for an employee id that exists but an blank v_msg is returned when you pass an employee id that does not exist.
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE query_emp_get_sal_job
(v_id IN emp.empno%TYPE,
v_sal OUT emp.sal%TYPE,
v_job OUT emp.job%TYPE,
v_msg OUT VARCHAR2)
IS
BEGIN
SELECT sal, job
INTO v_sal, v_job
FROM emp
WHERE empno = v_id;
v_msg :='Query Successful';
if sql%NOTFOUND then
v_msg :='The employer id number does not exist, please try another';
end if;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp_get_sal_job;
/
My question is why does the above query not result in v_msg having an error message.
Whilst similar query works in "Todd Barry " example.
Note in Todd Barrys example you can substitute %rowcount = 0 with sql%notfound and it should work.
|
|
|
|
| Re: PL/SQL exception code can be improved?? [message #38194 is a reply to message #38182] |
Sat, 30 March 2002 21:22 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
When using SELECT/INTO, you check for a NO_DATA_FOUND exception.
CREATE OR REPLACE PROCEDURE query_emp_get_sal_job
(v_id IN emp.empno%TYPE,
v_sal OUT emp.sal%TYPE,
v_job OUT emp.job%TYPE,
v_msg OUT VARCHAR2)
IS
BEGIN
SELECT sal, job
INTO v_sal, v_job
FROM emp
WHERE empno = v_id;
v_msg :='Query Successful';
exception
when no_data_found then
v_msg :='The employer id number does not exist, please try another';
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp_get_sal_job; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
