gram77
asked on
Debugging in QA for data errors without debug statements.
I access QA env. using my own login, and then access QA objects using QA. prefix to objects.
This way i only have read access to objects.
Frequently, i have errors in code.
CREATE PACKAGE BODY pkg
..
PROCEDURE proc(param1 varchar2, param2 number)
BEGIN
sql statement1;
pl/sql statement2(v_param1,v_para m2);
..
pl/sql statement100;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO LOG VALUES (program_name,sqlerrorcode ,sqlerrm,d bms_utilit y.format_e rror_backt race);
RAISE;
END;
In such a situation I am able to know which line has the error, but it is difficult to reproduce the error unless i know what were the prameter values passed to the procedure, and checking the values of variables i am interested in.
Sometimes the error is not in the code, but in the variable values such as ORA-01722: invalid number or query that selects tablespace_name and moves an index to the tablespace. and the tablespace name retrieved by
the query does not exist.
Ideally i should be able to check parameter values and values of variables i am interested in using debug statements:
CREATE PACKAGE BODY pkg
..
PROCEDURE proc(param1 varchar2, param2 number)
BEGIN
dbms_output.put_line('stmt 1 pkg-->' || pkg ||' proc--> '||proc||' param1--> '||param1||' param2--> '||param2);
sql statement1;
dbms_output.put_line('stmt 2 pkg-->' || pkg ||' proc--> '||proc);
pl/sql statement2(v_param1,v_para m2);
..
dbms_output.put_line('stmt 98 pkg-->' || pkg ||' proc--> '||proc|| ' variable1--> '||variable1);
variable1: = variable2;
..
dbms_output.put_line('stmt 100 pkg-->' || pkg ||' proc--> '||proc||);
pl/sql statement100;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO LOG VALUES (program_name,sqlerrorcode ,sqlerrm,d bms_utilit y.format_e rror_backt race);
RAISE;
END;
But, as I have only read privilege I cannot add debug statements to code.In such a situation if a bug appears in QA how do i pin point the error without reproducing the error and without knowing the parameter values passed to the proc and values of variables that caused the error.
This way i only have read access to objects.
Frequently, i have errors in code.
CREATE PACKAGE BODY pkg
..
PROCEDURE proc(param1 varchar2, param2 number)
BEGIN
sql statement1;
pl/sql statement2(v_param1,v_para
..
pl/sql statement100;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO LOG VALUES (program_name,sqlerrorcode
RAISE;
END;
In such a situation I am able to know which line has the error, but it is difficult to reproduce the error unless i know what were the prameter values passed to the procedure, and checking the values of variables i am interested in.
Sometimes the error is not in the code, but in the variable values such as ORA-01722: invalid number or query that selects tablespace_name and moves an index to the tablespace. and the tablespace name retrieved by
the query does not exist.
Ideally i should be able to check parameter values and values of variables i am interested in using debug statements:
CREATE PACKAGE BODY pkg
..
PROCEDURE proc(param1 varchar2, param2 number)
BEGIN
dbms_output.put_line('stmt
sql statement1;
dbms_output.put_line('stmt
pl/sql statement2(v_param1,v_para
..
dbms_output.put_line('stmt
variable1: = variable2;
..
dbms_output.put_line('stmt
pl/sql statement100;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO LOG VALUES (program_name,sqlerrorcode
RAISE;
END;
But, as I have only read privilege I cannot add debug statements to code.In such a situation if a bug appears in QA how do i pin point the error without reproducing the error and without knowing the parameter values passed to the proc and values of variables that caused the error.
if you can't modify the code to capture the parameters you can only guess based on the backtrace and error message.
I assume you can run ANNONYMOUS blocks.
You can include DBMS_STATEMENTS in your package specs and run it as ANNONYMOUS block
You can include DBMS_STATEMENTS in your package specs and run it as ANNONYMOUS block
can you turn tracing on?
exec DBMS_MONITOR.SESSION_TRACE _ENABLE(bi nds=>TRUE) ;
then run your procedure, in the resulting trace file you'll see the bind values associated with each sql statement
I just tested a procedure and got this ...
END OF STMT
PARSE #3:c=999,e=333,p=0,cr=0,cu =0,mis=1,r =0,dep=1,o g=1,plh=0, tim=131921 1507507618
BINDS #3:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=008665fc bln=32 avl=04 flg=05
value="test"
EXEC #3:c=1000,e=1503,p=0,cr=0, cu=0,mis=1 ,r=0,dep=1 ,og=1,plh= 2803242125 ,tim=1319
exec DBMS_MONITOR.SESSION_TRACE
then run your procedure, in the resulting trace file you'll see the bind values associated with each sql statement
I just tested a procedure and got this ...
END OF STMT
PARSE #3:c=999,e=333,p=0,cr=0,cu
BINDS #3:
Bind#0
oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=32 off=0
kxsbbbfp=008665fc bln=32 avl=04 flg=05
value="test"
EXEC #3:c=1000,e=1503,p=0,cr=0,
sdstuber, can you please attach your procedure (code) as well?
sure, but it's sort of trivial. just turn on tracing and run any procedure you already have...
ee.txt
ee.txt
ASKER
but tracing needs access to trace files that developers don't have access to
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
another option (again, it may not actually be possible for you)
create a new schema, or worst case, a new database - someplace where you DO have access. Your own pc if necessary.
make a copy of the objects and data in your new workspace.
edit your private copy to include logging
run it
create a new schema, or worst case, a new database - someplace where you DO have access. Your own pc if necessary.
make a copy of the objects and data in your new workspace.
edit your private copy to include logging
run it