Link to home
Start Free TrialLog in
Avatar of gram77
gram77Flag for India

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_param2);

      ..

      pl/sql statement100;

EXCEPTION
      WHEN OTHERS THEN
            INSERT INTO LOG VALUES (program_name,sqlerrorcode,sqlerrm,dbms_utility.format_error_backtrace);
            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('stmt1 pkg-->' || pkg ||' proc--> '||proc||' param1--> '||param1||' param2--> '||param2);
      sql statement1;

      
      dbms_output.put_line('stmt2 pkg-->' || pkg ||' proc--> '||proc);
      pl/sql statement2(v_param1,v_param2);

      ..

      dbms_output.put_line('stmt98 pkg-->' || pkg ||' proc--> '||proc|| ' variable1--> '||variable1);
      variable1: = variable2;
      
      ..
      dbms_output.put_line('stmt100 pkg-->' || pkg ||' proc--> '||proc||);
      pl/sql statement100;

EXCEPTION
      WHEN OTHERS THEN
            INSERT INTO LOG VALUES (program_name,sqlerrorcode,sqlerrm,dbms_utility.format_error_backtrace);
            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.
Avatar of Sean Stuber
Sean Stuber

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
can you turn tracing on?

exec DBMS_MONITOR.SESSION_TRACE_ENABLE(binds=>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,og=1,plh=0,tim=1319211507507618
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

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
Avatar of gram77

ASKER

but tracing needs access to trace files that developers don't have access to
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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