hi friend
can any on let me know how i can check the query which i write in the procedure....
for exa.
CREATE OR REPLACE PROCEDURE SP_EMP_LVTYPE_UPDATE(
in_elmLtmcode IN VARCHAR2 DEFAULT NULL,
in_elmEmcode IN VARCHAR2 DEFAULT NULL,
in_elmCmmcode IN VARCHAR2 DEFAULT NULL,
in_elmTotdays IN VARCHAR2 DEFAULT NULL,
in_elmBaldays IN VARCHAR2 DEFAULT NULL,
in_elmEncashdays IN VARCHAR2 DEFAULT NULL,
in_emcode2 IN VARCHAR2 DEFAULT NULL,
in_qryId IN NUMBER DEFAULT NULL,
retype OUT NUMBER)

AS
elmLtmcode VARCHAR2(500);
elmEmcode VARCHAR2(20);
elmCmmcode VARCHAR2(20);
elmTotdays VARCHAR2(500);
elmBaldays VARCHAR2(100);
elmEncashdays VARCHAR2(100);
qryId NUMBER(10,0);
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
LTMcode VARCHAR2(20);
LPos NUMBER(10,0);
Totdays VARCHAR2(20);
Tpos NUMBER(10,0);
Baldays VARCHAR2(20);
Bpos NUMBER(10,0);
Encashdays VARCHAR2(20);
Epos NUMBER(10,0);
Epos1 NUMBER(10,0);
count1 NUMBER(10,0);
count2 NUMBER(10,0);
encdays VARCHAR2(20);
lvtaken VARCHAR2(20);
elmEncashdays1 VARCHAR2(50);
ResigRecords number(10,0);
totEncashdays NUMBER;
emcode2 VARCHAR2(100);
BEGIN
SP_EMP_LVTYPE_UPDATE.elmLtmcode := SP_EMP_LVTYPE_UPDATE.in_elmLtmcode;
SP_EMP_LVTYPE_UPDATE.elmEmcode := SP_EMP_LVTYPE_UPDATE.in_elmEmcode;
SP_EMP_LVTYPE_UPDATE.elmCmmcode := SP_EMP_LVTYPE_UPDATE.in_elmCmmcode;
SP_EMP_LVTYPE_UPDATE.elmTotdays := SP_EMP_LVTYPE_UPDATE.in_elmTotdays;
SP_EMP_LVTYPE_UPDATE.elmBaldays := SP_EMP_LVTYPE_UPDATE.in_elmBaldays;
SP_EMP_LVTYPE_UPDATE.elmEncashdays := SP_EMP_LVTYPE_UPDATE.in_elmEncashdays;
SP_EMP_LVTYPE_UPDATE.emcode2:=SP_EMP_LVTYPE_UPDATE .in_emcode2 ;
SP_EMP_LVTYPE_UPDATE.qryId := SP_EMP_LVTYPE_UPDATE.in_qryId;
SP_EMP_LVTYPE_UPDATE.retype := 0;
IF SP_EMP_LVTYPE_UPDATE.qryId = 1 THEN
BEGIN
NULL;
SP_EMP_LVTYPE_UPDATE.elmLtmcode := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmLtmcode)) || ',';
SP_EMP_LVTYPE_UPDATE.LPos := INSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode,',',1);
SP_EMP_LVTYPE_UPDATE.elmTotdays := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmTotdays)) || ',';
.
.
.
.
.

UPDATE EMP_LVTYPE_MAP
SET
ELMLTMCODE = CAST(SP_EMP_LVTYPE_UPDATE.LTMcode AS VARCHAR2(20)),
ELMEMCODE = SP_EMP_LVTYPE_UPDATE.elmEmcode,
ELMCMMCODE = SP_EMP_LVTYPE_UPDATE.elmCmmcode,
ELMTOTDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Totdays AS VARCHAR2(20)),
ELMBALDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Baldays AS NUMBER(5,2)),
ELMENCASHDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Encashdays AS NUMBER(5,2)),
UPDATEDBY = SP_EMP_LVTYPE_UPDATE.emcode2
WHERE ELMLTMCODE = CAST(SP_EMP_LVTYPE_UPDATE.LTMcode AS VARCHAR2(20))
and ELMEMCODE = SP_EMP_LVTYPE_UPDATE.elmEmcode
and ELMCMMCODE = SP_EMP_LVTYPE_UPDATE.elmCmmcode;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
END;
END;
END IF;

i write this query.and passing the value of paramter of this procedure by my java application by the right click of procedure name and click test meenu and passing the value in the procedure.
this show that ur prcedure is working properly but when i m direct run this procedure through the application the updation query is not working....
can any one suggest me how i can check this updation query...

there is any method to check the query such like dbms_output.put_line().

??

you should post the error message also. may be user privileges to update the table, table lock, or privileges to CREATE/REPLACE procedure. there can be several cause.

A frequent problem involving PLSQL, "not working" is that
privileges acquired via ROLE do not apply within named PL/SQL procedures.

We need actual error code & message plus the line number where the error is thrown.

I am quite certain there is no error because of that exception hiding section (EXCEPTION WHEN OTHERS) which is masking all ones. However, I would say the procedure is working as designed - whichever exception is raised, fill some local variables (which are lost after procedure end) and pretend that everything ended correctly.

Removal of that EXCEPTION section might be the first step for understanding what is the reason for "not working".

Probably not without changing the interface. In PL/SQL, you may implement logging as shown in this thread on AskTom: http://asktom.oracle.com/pls/asktom/...66400346817259
(maybe it would be good renaming LOG_ERROR to more descriptive one avoid confusion and then use it wherever you want for storing whatever you need)

hi sir

thanks for ur reply...

actually friend i want to know that there is any method or function or way to check the query which i write in the procedure....
in this problem, the procedure is compile without any error and when i test this procedure by the right click of procedure name and click the test menu then other window is open.
here is test script
begin
-- Call the procedure
sp_emp_lvtype_update(in_elmltmcode => :in_elmltmcode,
in_elmemcode => :in_elmemcode,
in_elmcmmcode => :in_elmcmmcode,
in_elmtotdays => :in_elmtotdays,
in_elmbaldays => :in_elmbaldays,
in_elmencashdays => :in_elmencashdays,
in_emcode2 => :in_emcode2,
in_qryid => :in_qryid,
retype => :retype);
end;

and passing the parameter value in this procedure by hard codded which is coming from my application. and click the dbms output tag it show all the dbms_output and update the table.(means procedure is working properly (i think)).
but when i direct run this procedure through my application and passing the value of parameter by the application, it (procedure) not working and it will not update the table

CREATE OR REPLACE PROCEDURE SP_EMP_LVTYPE_UPDATE(
in_elmLtmcode IN VARCHAR2 DEFAULT NULL,
in_elmEmcode IN VARCHAR2 DEFAULT NULL,
in_elmCmmcode IN VARCHAR2 DEFAULT NULL,
in_elmTotdays IN VARCHAR2 DEFAULT NULL,
in_elmBaldays IN VARCHAR2 DEFAULT NULL,
in_elmEncashdays IN VARCHAR2 DEFAULT NULL,
in_emcode2 IN VARCHAR2 DEFAULT NULL,
in_qryId IN NUMBER DEFAULT NULL,
retype OUT NUMBER)

AS
elmLtmcode VARCHAR2(500);
elmEmcode VARCHAR2(20);
elmCmmcode VARCHAR2(20);
elmTotdays VARCHAR2(500);
elmBaldays VARCHAR2(100);
elmEncashdays VARCHAR2(100);
qryId NUMBER(10,0);
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
LTMcode VARCHAR2(20);
LPos NUMBER(10,0);
Totdays VARCHAR2(20);
Tpos NUMBER(10,0);
Baldays VARCHAR2(20);
Bpos NUMBER(10,0);
Encashdays VARCHAR2(20);
Epos NUMBER(10,0);
Epos1 NUMBER(10,0);
count1 NUMBER(10,0);
count2 NUMBER(10,0);
encdays VARCHAR2(20);
lvtaken VARCHAR2(20);
elmEncashdays1 VARCHAR2(50);
ResigRecords number(10,0);
totEncashdays NUMBER;
emcode2 VARCHAR2(100);
BEGIN
SP_EMP_LVTYPE_UPDATE.elmLtmcode := SP_EMP_LVTYPE_UPDATE.in_elmLtmcode;
SP_EMP_LVTYPE_UPDATE.elmEmcode := SP_EMP_LVTYPE_UPDATE.in_elmEmcode;
SP_EMP_LVTYPE_UPDATE.elmCmmcode := SP_EMP_LVTYPE_UPDATE.in_elmCmmcode;
SP_EMP_LVTYPE_UPDATE.elmTotdays := SP_EMP_LVTYPE_UPDATE.in_elmTotdays;
SP_EMP_LVTYPE_UPDATE.elmBaldays := SP_EMP_LVTYPE_UPDATE.in_elmBaldays;
SP_EMP_LVTYPE_UPDATE.elmEncashdays := SP_EMP_LVTYPE_UPDATE.in_elmEncashdays;
SP_EMP_LVTYPE_UPDATE.emcode2:=SP_EMP_LVTYPE_UPDATE .in_emcode2 ;
SP_EMP_LVTYPE_UPDATE.qryId := SP_EMP_LVTYPE_UPDATE.in_qryId;
SP_EMP_LVTYPE_UPDATE.retype := 0;
IF SP_EMP_LVTYPE_UPDATE.qryId = 1 THEN
BEGIN
NULL;
SP_EMP_LVTYPE_UPDATE.elmLtmcode := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmLtmcode)) || ',';
SP_EMP_LVTYPE_UPDATE.LPos := INSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode,',',1);
SP_EMP_LVTYPE_UPDATE.elmTotdays := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmTotdays)) || ',';
SP_EMP_LVTYPE_UPDATE.Tpos := INSTR(SP_EMP_LVTYPE_UPDATE.elmTotdays,',',1);
SP_EMP_LVTYPE_UPDATE.elmBaldays := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmBaldays)) || ',';
SP_EMP_LVTYPE_UPDATE.Bpos := INSTR(SP_EMP_LVTYPE_UPDATE.elmBaldays,',',1);
SP_EMP_LVTYPE_UPDATE.elmEncashdays := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmEncashdays)) || ',';
SP_EMP_LVTYPE_UPDATE.Epos := INSTR(SP_EMP_LVTYPE_UPDATE.elmEncashdays,',',1);
dbms_output.put_line('hello1'||SP_EMP_LVTYPE_UPDAT E.emcode2);
IF ( ( REPLACE(SP_EMP_LVTYPE_UPDATE.elmLtmcode, ',', '') IS NOT NULL) and ( REPLACE(SP_EMP_LVTYPE_UPDATE.elmTotdays, ',', '') IS NOT NULL) and ( REPLACE(SP_EMP_LVTYPE_UPDATE.elmBaldays, ',', '') IS NOT NULL) and ( REPLACE(SP_EMP_LVTYPE_UPDATE.elmEncashdays, ',', '') IS NOT NULL)) THEN
BEGIN
dbms_output.put_line('hello2'||SP_EMP_LVTYPE_UPDAT E.emcode2);
<<i_loop1>>
WHILE ( SP_EMP_LVTYPE_UPDATE.LPos > 0 and SP_EMP_LVTYPE_UPDATE.Tpos > 0 and SP_EMP_LVTYPE_UPDATE.Bpos > 0 and SP_EMP_LVTYPE_UPDATE.Epos > 0) LOOP
BEGIN
dbms_output.put_line('hello3'||SP_EMP_LVTYPE_UPDAT E.emcode2);

SP_EMP_LVTYPE_UPDATE.LTMcode := LTRIM(RTRIM(SUBSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode , 0,SP_EMP_LVTYPE_UPDATE.LPos - 1)));
SP_EMP_LVTYPE_UPDATE.Totdays := LTRIM(RTRIM(SUBSTR(SP_EMP_LVTYPE_UPDATE.elmTotdays , 0,SP_EMP_LVTYPE_UPDATE.Tpos - 1)));
SP_EMP_LVTYPE_UPDATE.Baldays := LTRIM(RTRIM(SUBSTR(SP_EMP_LVTYPE_UPDATE.elmBaldays , 0,SP_EMP_LVTYPE_UPDATE.Bpos - 1)));
SP_EMP_LVTYPE_UPDATE.Encashdays := LTRIM(RTRIM(SUBSTR(SP_EMP_LVTYPE_UPDATE.elmEncashd ays, 0,SP_EMP_LVTYPE_UPDATE.Epos - 1)));
IF ( SP_EMP_LVTYPE_UPDATE.LTMcode IS NOT NULL and SP_EMP_LVTYPE_UPDATE.Totdays <> '0' and SP_EMP_LVTYPE_UPDATE.Baldays <> '0' and SP_EMP_LVTYPE_UPDATE.Encashdays <> '0') THEN
BEGIN
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;

dbms_output.put_line('hello4'||SP_EMP_LVTYPE_UPDAT E.emcode2);
dbms_output.put_line('hello5'||(SP_EMP_LVTYPE_UPDA TE.LTMcode) );




UPDATE EMP_LVTYPE_MAP
SET
ELMLTMCODE = CAST(SP_EMP_LVTYPE_UPDATE.LTMcode AS VARCHAR2(20)),
ELMEMCODE = SP_EMP_LVTYPE_UPDATE.elmEmcode,
ELMCMMCODE = SP_EMP_LVTYPE_UPDATE.elmCmmcode,
ELMTOTDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Totdays AS VARCHAR2(20)),
ELMBALDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Baldays AS NUMBER(5,2)),
ELMENCASHDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Encashdays AS NUMBER(5,2)),
UPDATEDBY = SP_EMP_LVTYPE_UPDATE.emcode2
WHERE ELMLTMCODE = CAST(SP_EMP_LVTYPE_UPDATE.LTMcode AS VARCHAR2(20))
and ELMEMCODE = SP_EMP_LVTYPE_UPDATE.elmEmcode
and ELMCMMCODE = SP_EMP_LVTYPE_UPDATE.elmCmmcode;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
END;
END;
END IF;
SP_EMP_LVTYPE_UPDATE.elmLtmcode := SUBSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode, SP_EMP_LVTYPE_UPDATE.LPos + 1, LENGTH(SP_EMP_LVTYPE_UPDATE.elmLtmcode));
SP_EMP_LVTYPE_UPDATE.LPos := INSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode,',',1);
SP_EMP_LVTYPE_UPDATE.elmTotdays := SUBSTR(SP_EMP_LVTYPE_UPDATE.elmTotdays, SP_EMP_LVTYPE_UPDATE.Tpos + 1, LENGTH(SP_EMP_LVTYPE_UPDATE.elmTotdays));
SP_EMP_LVTYPE_UPDATE.Tpos := INSTR(SP_EMP_LVTYPE_UPDATE.elmTotdays,',',1);
SP_EMP_LVTYPE_UPDATE.elmBaldays := SUBSTR(SP_EMP_LVTYPE_UPDATE.elmBaldays, SP_EMP_LVTYPE_UPDATE.Bpos + 1, LENGTH(SP_EMP_LVTYPE_UPDATE.elmBaldays));
SP_EMP_LVTYPE_UPDATE.Bpos := INSTR(SP_EMP_LVTYPE_UPDATE.elmBaldays,',',1);
SP_EMP_LVTYPE_UPDATE.elmEncashdays := SUBSTR(SP_EMP_LVTYPE_UPDATE.elmEncashdays, SP_EMP_LVTYPE_UPDATE.Epos + 1, LENGTH(SP_EMP_LVTYPE_UPDATE.elmEncashdays));
SP_EMP_LVTYPE_UPDATE.Epos := INSTR(SP_EMP_LVTYPE_UPDATE.elmEncashdays,',',1);
END;
END LOOP;
SP_EMP_LVTYPE_UPDATE.retype := 1;
END;
ELSE
BEGIN
SP_EMP_LVTYPE_UPDATE.retype := 0;
END;
END IF;
END;
END IF;

this is my whole procedure.
check it sir...

There are not many things you may do from database as it does not see the application data and its interface quite limited. One way would be implement logging into a new table in autonomous transaction, as I posted in my previous post, and check the table content after running it. Did you have any problem with reading the link describing it? If so, maybe it is time to hire Oracle developer/consultant.

You may try to read DBMS_OUTPUT buffer in the application using GET_LINE function and display it wherever you want. You may find short description with code samples e.g. here: http://psoug.org/reference/dbms_output.html
Note, that DBMS_OUTPUT.PUT_LINE does not *write* anything anywhere - it just fills the buffer and it is on the caller (application) to display its content after running that code. SQL*Plus does it after setting serveroutput on. Toad does it in a special tab after enabling it. In fact, I have never seen any real application (not tool like the above mentioned ones) implementing it, but you may give it a try.

eliminate/delete/remove all/every/100% EXCEPTION handler code.