Debug and Global Temporary Tables

[zjah02]

I have been using PL/SQL Developer for several years and now and am looking into using the debug features. What I would like to know if I am in a debug session and I want to view the contents of a Global Temporary table how do I do this? I usually run Multi Session Mode, but I switched to Single Session Mode thinking that I would be able to see the data, but this doesn

 

[mike]

This is what I do. I basically copy the global temporary table in the test script to a regular table. After it runs, I use a SQL window to view the data. I drop the copied table after I'm done looking at it.

I created a test package (zfnd_test_util) with procedures to drop and copy tables. I call the copy table procedure from the test script just before it exits. Here's the guts of the copy table procedure:

PROCEDURE COPY_TABLE(i_table_name        IN VARCHAR2
                       ,i_copied_table_name IN VARCHAR2 DEFAULT NULL
                       ,i_where_clause      IN VARCHAR2 DEFAULT 'WHERE 1 = 1') 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;
   END;

 

[zjah02]

That is basically what we do; I was looking something more intrinsic in the product. This way I could stop and preview the data on the fly if the feature existed.

 

[Claus Pedersen]

Nice idea. Please observe, though, that using DDL (Data Definition Language, CREATE, ALTER, DROP etc.) inside PL/SQL code causes implicit commits, even if no commit is explicitly given in the code (as in this case). If the global temporary table is set to delete data on commit, this approach could have adverse effects. Also, if you do not want to commit test data, this approach could be potentially dangerous.

 

[zjah02]

I agree this is only for Debug process.