Can't see Tempororay table data since debugging not allowed in Single Session

[iratta]

Version 7.1.5.1398

DB version:10gr2

If i try to debug in single session mode i'll get the error "debugging not possible in single session mode".

Once i've started debugging, I want to see the values from a GLOBAL TEMPORARY Table. But, one has to be in Multi-session mode to debug . Since data in GTT is session specific i can't get the data from the temporary table by opening another SQL window.

Is there a workaround for this?

 

[Marco Kalter]

There is no workaround for this. The data is only available for the session, but the session is busy executing your code.

 

[mike]

What I do is copy the global temporary table. I have a package that I call to copy the table. In my test script, I call the procedure to copy the global temporary table just after the package runs that inserts the data. Here is my code so you can modify it to suit your needs.

PROCEDURE COPY_TABLE(i_table_name IN VARCHAR2
,i_copied_table_name IN VARCHAR2 DEFAULT NULL
,i_where_clause IN VARCHAR2 DEFAULT 'WHERE 1 = 2') IS

l_copied_table_name all_tables.table_name%TYPE;
BEGIN

IF i_copied_table_name IS NOT NULL
THEN
l_copied_table_name := upper(i_copied_table_name);
ELSE
l_copied_table_name := substr(upper(i_table_name), 1, 26) || '_TST';
END IF;

zfnd_test_util.drop_table(i_table_name => l_copied_table_name);

EXECUTE IMMEDIATE 'CREATE TABLE ' || l_copied_table_name || ' AS
(SELECT *
FROM ' || i_table_name || '
' || i_where_clause || ')';

-- COMMIT;
EXCEPTION

WHEN OTHERS THEN
zfnd_debug.log('EXITING CREATE_TEST_TABLE AS EXCEPTION', 'N', 'ZFND_TEST_UTIL.CREATE_TEST_TABLE');
ZFND_DEBUG.PUT_LINE('+---------------------------------------------------------------------------+');
ZFND_DEBUG.PUT_LINE('ERROR IN ZFND_TEST_UTIL.CREATE_TEST_TABLE');
ZFND_DEBUG.PUT_LINE('ERROR: ' || SQLCODE || ' ' || SQLERRM);
ZFND_DEBUG.PUT_LINE('i_test_table: ' || i_copied_table_name);
ZFND_DEBUG.PUT_LINE('i_template_table: ' || i_table_name);
ZFND_DEBUG.PUT_LINE('+---------------------------------------------------------------------------+');
RAISE_APPLICATION_ERROR(-20010, 'Error in ZFND_TEST_UTIL.CREATE_TEST_TABLE');

END COPY_TABLE;