Solved

PL/SQL Stored Procedure EXCEPTION not working

Posted on 2008-10-12
3
1,274 Views
Last Modified: 2013-12-18
Hi Experts,

I have a stored procedure I am writing for an on-line class and cannot figure out what I am doing wrong. I need the procedure to write to an error table "order_errors" for any error that occurs while trying to execute it.

The package is called "Process_Orders" and the procedure is called "Add_Order". There are three parameters for Add_Order: cno, eno, and received. A call with valid parameters works fine, but a call with "bad data" text where a date was expected and so on does not trigger the exception handler.

Here is the output from SQL PLUS when the number 23 is entered where a date should be:
SQL> execute Process_Orders.Add_Order(12,14,'23');
BEGIN Process_Orders.Add_Order(12,14,'23'); END;

                                                         
ERROR at line 1:
ORA-01840: input value not long enough for date format
ORA-06512: at line 1
CREATE OR REPLACE PACKAGE Process_Orders AS
	PROCEDURE Add_Order(p_cno IN orders.cno%TYPE,
			p_eno IN orders.eno%TYPE,
			p_received IN DATE);
			p_creation_date DATE;
			p_last_update_date DATE;
			p_created_by VARCHAR2(10);
			p_last_update_by VARCHAR2(10);	
			p_date DATE;
			error_message VARCHAR2(100);
END Process_Orders;
/
show errors
set echo on
 
 
CREATE OR REPLACE PACKAGE BODY Process_Orders AS
  -- Add a new order for the specified class.
  -- Order_number_seq should be used to populate the order number (ONO) column.
 
 
	PROCEDURE Add_Order(p_cno IN orders.cno%TYPE,
			p_eno IN orders.eno%TYPE,
			p_received IN DATE) AS
 
	BEGIN
  	INSERT INTO orders (ono,cno,eno,received,creation_date,created_by,last_update_date,last_update_by)
  	SELECT Order_number_seq.NextVal,p_cno,p_eno,NVL(p_received,CURRENT_DATE),CURRENT_DATE,
	USER,CURRENT_DATE,USER FROM DUAL;
  	COMMIT;
 
 	EXCEPTION
	WHEN OTHERS THEN
	error_message := sqlerrm(sqlcode);
	DBMS_OUTPUT.PUT_LINE (error_message);
	INSERT INTO order_errors (ono,transaction_date,message) 
	VALUES (Order_number_seq.NextVal,CURRENT_DATE,error_message);
	COMMIT;
	END Add_Order;
 
END Process_Orders;
/
show errors

Open in new window

0
Comment
Question by:k_smee
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 22698840
the problem is that the error occurs even before you enter the procedure.
the value '23' cannot be converted to date, hence the error.

if you want the procedure to handle the error:

CREATE OR REPLACE PACKAGE Process_Orders AS
      PROCEDURE Add_Order(p_cno IN orders.cno%TYPE,
                  p_eno IN orders.eno%TYPE,
                  p_received IN VARCHAR2);

Open in new window

0
 

Author Comment

by:k_smee
ID: 22698879
Thank you so much!  I see what you mean. So  p_received was the issue here since it expected a date you changed it to VARCHAR2 os it would accept pretty much anything
0
 

Author Closing Comment

by:k_smee
ID: 31505500
Thanks for the quick response! This assignment is killing me..
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 72
how to replace '&' and '()' in sql query for oracle using regex 8 74
passing parameters to sql script oracle 4 41
Oracle Insert not working 10 28
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question