| problem in procedure [message #125963] |
Thu, 30 June 2005 02:58 |
pooja_09
Messages: 28
Registered: June 2005
Location: Delhi
|
Junior Member |
 
|
|
dear friends..
following PROCEDURE IS updating THE TABLE but NOT showing THE updated VALUE USING 'out'.
also IN THE SQL%FOUND BLOCK it IS showing SQL%NOTFOUND RESULT.
please solve my problem..
its urgent...
PROCEDURE PROC_EMPUPDATE(VEMP_ID IN OUT VARCHAR2,
VEMP_NAME IN OUT VARCHAR2,
VEMP_FLAG IN OUT VARCHAR2,
FOUND1 OUT VARCHAR2,
U_FLAG OUT VARCHAR2) IS
EMPID_EXIST VARCHAR2(1);
CURSOR C_CHK IS
SELECT *
FROM EMPLOYEE_MASTER
WHERE TRIM(EMP_ID) = UPPER(TRIM(VEMP_ID));
NEW_CURSOR C_CHK%ROWTYPE;
BEGIN
OPEN C_CHK;
IF C_CHK%ISOPEN THEN
LOOP
FETCH C_CHK
INTO NEW_CURSOR;
EXIT WHEN C_CHK%NOTFOUND;
EMPID_EXIST := F_CHECKEMP(VEMP_ID);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(EMPID_EXIST));
IF EMPID_EXIST = 'Y' THEN
UPDATE EMPLOYEE_MASTER
SET EMP_ID = UPPER(VEMP_ID),
EMP_NAME = INITCAP(VEMP_NAME),
EMP_FLAG = UPPER(VEMP_FLAG)
WHERE TRIM(EMP_ID) = UPPER(TRIM(VEMP_ID));
COMMIT;
END IF;
IF SQL%FOUND THEN
FOUND1 := 'RECORD FOUND';
VEMP_ID := NEW_CURSOR.EMP_ID;
VEMP_NAME := NEW_CURSOR.EMP_NAME;
VEMP_FLAG := NEW_CURSOR.EMP_FLAG;
U_FLAG := 'RECORDS UPDATED SUCCESSFULLY';
ELSE
FOUND1 := 'RECORD NOT FOUND';
U_FLAG := 'RECORDS DOES NOT UPDATED...TRY AGAIN';
END IF;
END LOOP;
CLOSE C_CHK;
ELSE
DBMS_OUTPUT.PUT_LINE('This Employee ID already exist');
END IF;
IF VEMP_ID IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Employee id should not be null');
END IF;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Enter the correct value');
END PROC_EMPUPDATE;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
