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;
Last edited by mike; 03/11/09 03:05 PM.