Inspecting a Temporary Table or Uncommitted Table rows in a Debug Session

Debugging a procedure using SQL Developer is really neat. But I ran into an issue whereby I wanted to inspect the rows of a temporary table while I was debugging a stored procedure. I was unable to browse and inspect the rows in the temporary table as the debug session is on a different session. I had to throw in some debug code into the procedure to see something useful in the debug data tab. I ended up with the little procedure below which you may find useful. Basically I query the table in question and place the result in a CLOB to view in the debug window.

1) Create this procedure in your schema

--COMPILE THIS WITH DEBUG AND STEP INTO IT IF YOU WISH TO SEE RUN TIME VALUES
CREATE OR REPLACE PROCEDURE DEBUGINFO(p_tablename VARCHAR2,p_numrowstoinspect NUMBER DEFAULT 10) AS
  v_count INT;
  v_qryCtx DBMS_XMLGEN.ctxHandle;
  v_result CLOB;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO :v_count FROM '||p_tablename INTO v_count;
  DBMS_OUTPUT.PUT_LINE('tablename='||p_tablename||' : rowcount='||v_count);
  --query table rows into XML
  v_qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM ' ||p_tablename || ' WHERE ROWNUM <='||p_numrowstoinspect);
  -- Get the result
  v_result := DBMS_XMLGEN.getXML(v_qryCtx);
  DBMS_OUTPUT.PUT_LINE('--BEGIN DEBUGINFO RESULT FOR '||p_tablename||CHR(10)||CHR(10)||v_result||CHR(10)||'--END DEBUGINFO RESULT FOR '||p_tablename);
END DEBUGINFO;
/

2) Call DEBUGINFO from within the procedure your testing.
Pass the tablename and number of rows to inspect

3) Step into DEBUGINFO during debug to view "live" values.
V_COUNT contains the number of rows in the table
V_RESULT contains the table data (for the number of rows to inspect)

4) Review the log window for DBMS_OUTPUT
Result available after the procedure finishes.