[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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.
0
gram77
Asked:
gram77
  • 5
  • 2
1 Solution
 
sdstuberCommented:
if you can't modify the code to capture the parameters you can only guess based on the backtrace and error message.

0
 
ajexpertCommented:
I assume you can run ANNONYMOUS blocks.

You can include DBMS_STATEMENTS in your package specs and run it as ANNONYMOUS block
0
 
sdstuberCommented:
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

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
ajexpertCommented:
sdstuber, can you please attach your procedure (code) as well?

0
 
sdstuberCommented:
sure,  but it's sort of trivial.  just turn on tracing and run any procedure you already have...



ee.txt
0
 
gram77Author Commented:
but tracing needs access to trace files that developers don't have access to
0
 
sdstuberCommented:
I'm simply providing some options.  
I have no idea what you or other readers may or may not have access to.

If the restriction is "you can't look at anything" then you're stuck.  You'll just have to guess.

if you don't have access, either get access, or have the dba send you the files you need after tracing.

if it's information you need and you're not getting cooperation from those that have access, then escalate to their management.
0
 
sdstuberCommented:
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
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now