-
Package not working Not giving any error
Hi all
I have a package that update an error table and set to 'E' if there is an update the dbms.output .put package set the flag to 'E'. The package is not giving any error but the table is not updated. Any idea?
Thanks
********************************************
v_dbuser VARCHAR2(30);
v_osuser VARCHAR2(30);
l_sqlcode VARCHAR2(10);
l_sqlerrm VARCHAR2(150);
BEGIN
v_dbuser := SYS_CONTEXT('userenv','current_user');
v_osuser := SYS_CONTEXT('userenv','os_user');
/* Retrieve Oracle return code and message */
l_sqlcode := SUBSTR(SQLCODE,1,8);
l_sqlerrm := SUBSTR(SQLERRM,1,150);
/* Insert record into table that is used for tracking errors */
transaction_debug('Insert My_error_table....');
DBMS_OUTPUT.PUT_LINE('ABLAK Start');
UPDATE mytable SET FACT_EXTRACT='E'
WHERE TRANSEQNUM = p_transeqnum
AND TRANSENDTIME = p_transendtime
AND CUSTOMERID = p_customerid;
COMMIT;
Last edited by Ablakios; 09-06-2007 at 03:16 PM.
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Pavb
Could you be more explicit?
Thanks
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
if update does not work it means no row matches your predicate; look at your data.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I will double check
Thanks
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
PAVB
I am new to this and I checked and see no difference ..Could please direct me as to what I should cross check?
Thanks for your help
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
-
1- Add variable x_rowcount NUMBER; after l_sqlerrm VARCHAR2(150);
2- Alter your update section to look like...
Code:
DBMS_OUTPUT.PUT_LINE('ABLAK Start');
select count(*)
into x_rowcount
from mytable
WHERE TRANSEQNUM = p_transeqnum
AND TRANSENDTIME = p_transendtime
AND CUSTOMERID = p_customerid;
DBMS_OUTPUT.PUT_LINE(x_rowcount);
UPDATE mytable SET FACT_EXTRACT='E'
WHERE TRANSEQNUM = p_transeqnum
AND TRANSENDTIME = p_transendtime
AND CUSTOMERID = p_customerid;
COMMIT;
3- Compile and Execute your package.
If you see a zero after the 'ABLAK Start' literal that means no row in your table matches your predicate.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
PAVB,
Merci Beaucoup Cher ami. I will try it and let you know if it successful or not.
Thanks
A
Arsene Lupain
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|