Dear All, I need to implement log file concept in oracle.
For example i have written a procedure.
If any exception come it should display in log file.How will i do it?
Please assist me for the needful. -
-
CREATE OR REPLACE PROCEDURE PR_ITS_CUSTOMER_AUTH(
-
PI_USER_NAME IN VARCHAR2,
-
PI_DATE_OF_BIRTH IN VARCHAR2,
-
PO_RET_CODE OUT VARCHAR2,
-
PO_RETSTR OUT VARCHAR2
-
)
-
AS
-
-- Authentication of customer on login name and date of birth
-
L_ENTITY_ID ENTITY_MASTER.EM_ENTITY_ID%TYPE;
-
L_STATUS ENTITY_MASTER.EM_STATUS%TYPE;
-
BEGIN
-
PO_RET_CODE:='S';
-
PO_RETSTR:='SUCCESS';
-
BEGIN
-
SELECT UM.UM_EM_ENTITY_ID
-
INTO L_ENTITY_ID
-
FROM USER_MASTER UM
-
WHERE UM.UM_LOGIN_ID=PI_USER_NAME;
-
EXCEPTION
-
WHEN NO_DATA_FOUND THEN
-
PO_RET_CODE:='E';
-
PO_RETSTR:='ERROR::LOGIN ID IS NOT AVAILABLE';
-
RETURN;
-
END;
-
BEGIN
-
SELECT EM.EM_STATUS
-
INTO L_STATUS
-
FROM ENTITY_MASTER EM WHERE EM.EM_ENTITY_ID=L_ENTITY_ID
-
AND EM.EM_DOB=TO_DATE(PI_DATE_OF_BIRTH,'DD MON YYYY');
-
EXCEPTION
-
WHEN NO_DATA_FOUND THEN
-
PO_RET_CODE:='E';
-
PO_RETSTR:='ERROR::DOB IS NOT AVAILABLE';
-
END;
-
END;
-
Thanks in adavance.
Regards
Sumanta Panda
6 2806
That can be added to the EXCEPTION block of the code.
Dear Debashish Bhai,
I agreed your advise.Could You please suggest me how to write else please chage one line in my code.
Thanks in advance.
Regards
Sumanta Panda
thy the following code. -
CREATE OR REPLACE PROCEDURE PR_ITS_CUSTOMER_AUTH(
-
PI_USER_NAME IN VARCHAR2,
-
PI_DATE_OF_BIRTH IN VARCHAR2,
-
PO_RET_CODE OUT VARCHAR2,
-
PO_RETSTR OUT VARCHAR2
-
)
-
AS
-
-- Authentication of customer on login name and date of birth
-
L_ENTITY_ID ENTITY_MASTER.EM_ENTITY_ID%TYPE;
-
L_STATUS ENTITY_MASTER.EM_STATUS%TYPE;
-
BEGIN
-
PO_RET_CODE:='S';
-
PO_RETSTR:='SUCCESS';
-
BEGIN
-
SELECT UM.UM_EM_ENTITY_ID
-
INTO L_ENTITY_ID
-
FROM USER_MASTER UM
-
WHERE UM.UM_LOGIN_ID=PI_USER_NAME;
-
EXCEPTION
-
WHEN NO_DATA_FOUND THEN
-
PO_RET_CODE:='E';
-
PO_RETSTR:='ERROR::LOGIN ID IS NOT AVAILABLE';
-
RETURN;
-
END;
-
BEGIN
-
SELECT EM.EM_STATUS
-
INTO L_STATUS
-
FROM ENTITY_MASTER EM WHERE EM.EM_ENTITY_ID=L_ENTITY_ID
-
AND EM.EM_DOB=TO_DATE(PI_DATE_OF_BIRTH,'DD MON YYYY');
-
EXCEPTION
-
WHEN NO_DATA_FOUND THEN
-
PO_RET_CODE:='E';
-
PO_RETSTR:='ERROR::DOB IS NOT AVAILABLE';
-
INSERT INTO SYSTEM_LOG(E_CODE,E_MSG) VALUES(PO_RET_CODE,PO_RETSTR);
-
COMMIT;
-
END;
-
END;
-
-
-
Dear Debashish Bhai,
Thanks a lot.Let me confirm one doubt SYSTEM_LOG table is oracle system table or we need to create a table for it.
Thanks in advance.
Regards
Sumanta Pnada
Thanks a lot Debashish Bhai......Its working....
Hi Sumanta,
Please use CODE tags when ever you paste any source code. That makes the code more readable and that would help others to suggest a solution by having a look at your sample code.
Moderator
Sign in to post your reply or Sign up for a free account.
Similar topics
by: KULJEET |
last post by:
control file not created at time of installtion of oracle database
now how we can create it
see alert file
Dump file e:\ORacle\admin\new\bdump\newALRT.LOG
Tue Sep 09 10:45:53 2003
ORACLE...
|
by: Gent |
last post by:
I have a table named Holding_Value that has several fields in it among
which are UID, fkHolding, EffDate, Units, MarketValue, AssetPrice. UID
is an identity field and fkHolding is a foreign key to...
|
by: Cherrish Vaidiyan |
last post by:
sir,
I am working on Oracle 9i on Linux. I have a simple clarifications to be made..
Is there a concept of replication in MS ACCESS ?
If yes, Any GUI Tool available for it ?
Thanking in...
|
by: Pir8 |
last post by:
I have a complex xml file, which contains stories within a magazine. The
structure of the xml file is as follows:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<magazine>
<story>...
|
by: srinivas |
last post by:
Hi,
I am a asp programmer.I am displaying the db records in the
html pages in a web page.I have 500 columns and 1000 rows in that html
table.Here i am planning to implement the "MS-Excel Freeze...
|
by: gmax2006 |
last post by:
Hi,
I am using RedHat Linux 4. and I developed an oracle 10g based
application by using cx_Oracle (cx_Oracle-4.1-10g-py23-1.i386.rpm) and
Python 2.3.4.
When I run the application through...
|
by: greenMark |
last post by:
Hi All
I need to develope a data warehouse in oracle. I was able to develope a small one using oracle enterprise manager. But my main idea is to develope the necessary GUIs using Oracle Forms 6i....
|
by: kwartz |
last post by:
I have several .sql file that I am runing from the dos prompt using echo. I want to spool the log files of each of the .sql files into one. The way I have it now, the current log file overwrites the...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |