Home » SQL & PL/SQL » SQL & PL/SQL » need help in exception handling
need help in exception handling [message #273567] Wed, 10 October 2007 22:55 Go to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
Hi everyone,

I am new to PL/SQL. I have written a package with 10 procedures.
Each procedure has 2 insert and 2 updates in it. what kind of exception handling can be written for it.
Please advice.

[Updated on: Wed, 10 October 2007 22:57]

Report message to a moderator

Re: need help in exception handling [message #273568 is a reply to message #273567] Wed, 10 October 2007 22:58 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines (above).
You can find many fine coding examples at http://tahiti.oracle.com; including exception handling.
Re: need help in exception handling [message #273581 is a reply to message #273567] Thu, 11 October 2007 00:44 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
You get full documentation of PL/SQL User's Guide and Reference

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm

Specially read section 7.

[Updated on: Thu, 11 October 2007 00:47]

Report message to a moderator

Re: need help in exception handling [message #273584 is a reply to message #273567] Thu, 11 October 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Application Developer's Guide - Fundamentals
PL/SQL User's Guide and Reference

Please read OraFAQ Forum Guide.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: need help in exception handling [message #273730 is a reply to message #273584] Thu, 11 October 2007 09:08 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
My version of Oracle used is 9.2.0.1.0.
I did read the references but yet I am still not sure what kind of execption handling I should include for the package.
Would really appreciate any help.
Re: need help in exception handling [message #273731 is a reply to message #273567] Thu, 11 October 2007 09:12 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Exactly what are you unsure of ?

Re: need help in exception handling [message #273734 is a reply to message #273730] Thu, 11 October 2007 09:23 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
I am also having trouble with
update bb stg
set source_tier_id = source_contract_id || source_product_id;

oracle version 9.2.0.1.0

SQL> update bb stg
2 set source_tier_id = source_contract_id ||source_product_id;
set source_tier_id = source_contract_id || source_product_id
*
ERROR at line 2:
ORA-01722: invalid number

the table structure

SQL> desc bb;
Name Null? Type
----------------------------------------- -------- ---------------------
BIS_TIER_ID NUMBER
SOURCE_TIER_ID NUMBER
BIS_PRODUCT_ID NUMBER
SOURCE_PRODUCT_ID VARCHAR2(32)
BIS_CONTRACT_ID NUMBER(10)
SOURCE_CONTRACT_ID VARCHAR2(12)
BIS_CONTRACT_PRICE_GROUP_ID NUMBER
SOURCE_CONTRACT_PRICE_GROUP_ID NUMBER
PRODUCT_TIER_START_DATE DATE
PRODUCT_TIER_END_DATE DATE
MARKET_SHARE_TIER_REQUIRED NUMBER(22,6)
PRICE NUMBER(22,6)
REIMBURSEMENT NUMBER(22,6)
DISCOUNT_PERCENT NUMBER(22,6)
PRICE_CAP_PERCENT NUMBER(22,6)
BASIS_DISCOUNT_PERCENT NUMBER(22,6)
PRICE_PROTECTION_PERCENT NUMBER(22,6)
SERVICE_FEE_PERCENT NUMBER(22,7)
SOURCE_SYSTEM_ID NUMBER
DB_TIMESTAMP DATE
SOURCE_PRICE_BASIS_CODE VARCHAR2(10)
Re: need help in exception handling [message #273745 is a reply to message #273567] Thu, 11 October 2007 09:49 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Please do use code tags.

You are trying to update a number column with a concatenation of 2 columns, neither of which are defined as a number.

[Updated on: Thu, 11 October 2007 09:51]

Report message to a moderator

Re: need help in exception handling [message #273755 is a reply to message #273745] Thu, 11 October 2007 10:18 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
Yes i Do know that the datatypes are different. Still is there a way to make it work.
Re: need help in exception handling [message #273758 is a reply to message #273567] Thu, 11 October 2007 10:20 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
Exactly what are you unsure of ?

What execptions can I raise?
all i can think of is


EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate value.');

WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting values.');
Re: need help in exception handling [message #273759 is a reply to message #273755] Thu, 11 October 2007 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Convert to same datatype.

Regards
Michel
Re: need help in exception handling [message #273770 is a reply to message #273567] Thu, 11 October 2007 11:07 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
I tried this and i have a error.

SQL> update bb
2 set source_tier_id = (select
3 to_number(source_contract_id) || to_number(source_product_id)
4 from bb);
set source_tier_id = (select
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
Re: need help in exception handling [message #273772 is a reply to message #273567] Thu, 11 October 2007 11:20 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You do not need a sub-query.

 update bb 
set source_tier_id = 
to_number(source_contract_id) || to_number(source_product_id) 



Do you need to take nulls into account?
Re: need help in exception handling [message #273779 is a reply to message #273770] Thu, 11 October 2007 12:09 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
colorado_girl wrote on Thu, 11 October 2007 12:07


to_number(source_contract_id) || to_number(source_product_id)



No, you TO_NUMBER the final value, not each separate value. You concatenate strings, not numbers.
Re: need help in exception handling [message #273789 is a reply to message #273567] Thu, 11 October 2007 12:31 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
I tried that it still isnt working. Same error message

ORA-01427: single-row subquery returns more than one row
Re: need help in exception handling [message #273794 is a reply to message #273567] Thu, 11 October 2007 12:45 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
YES NULLS HAVE TO BE TAKEN INTO ACCOUNT.
Re: need help in exception handling [message #273815 is a reply to message #273789] Thu, 11 October 2007 15:03 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
colorado_girl wrote on Thu, 11 October 2007 13:31

I tried that it still isnt working. Same error message

ORA-01427: single-row subquery returns more than one row


Impossible if you removed the sub-query.
Re: need help in exception handling [message #273880 is a reply to message #273758] Fri, 12 October 2007 01:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
These exceptions:
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate value.');

WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting values.'); 
are pretty much a waster of time.
The DUP_VAL_ON_INDEX does nothing except change the error message that is displayed, and the OTHERS actively makes things worse by hiding the details of the errors that happened.

The only point in putting exception handling in at this level is to deal with errors that you expect to get and have a way of dealing with.
Anything unexpected should (IMVHO) be passed up to the client interface.
Re: need help in exception handling [message #274005 is a reply to message #273880] Fri, 12 October 2007 12:24 Go to previous message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
thanks that makes sense.

[Updated on: Fri, 12 October 2007 12:25]

Report message to a moderator

Previous Topic: Trigger 'SCOTT.SALARY_ADJUST' is invalid and failed re-validation
Next Topic: Some About "VIEW"
Goto Forum:
  


Current Time: Fri Dec 02 22:48:28 CST 2016

Total time taken to generate the page: 0.08772 seconds