How to implement log file concept in oracle

[sumanta123]

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

[debasisdas]

That can be added to the EXCEPTION block of the code.

[sumanta123]

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

[debasisdas]

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;

[sumanta123]

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

[sumanta123]

Thanks a lot Debashish Bhai......Its working....

[amitpatel66]

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