Re: PLSQL: how to test if a table exists?

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 4 Dec 2003 18:26:52 -0500
Message-ID: <kcCdnbqibex4X1KiRVn-tw@comcast.com>

comments embedded

"Keith Jamieson" <keith_jamieson_at_hotmail.com> wrote in message news:mmOzb.3284$nm6.18203_at_news.indigo.ie...
| More code

more code to write, but actually less code to execute

+ Dynamic SQL = more work for Oracle, and as a result, slower
| code. As well as this, the use of Native Dynamic SQL outlined here does
| not use Bind Variables.

as noted, i had assumed the 'test for table exists' would typically apply to dynamic sql, since the PL/SQL would not compile if the table did not exist -- but your reference to definer's rights vs invokers rights reminded me that a static reference could compile, but the invoker may not have access to the object at runtime (see below)

|
| Personally, I would query the data dictionary, and I believe all_tables
will
| have a list of all tables that you have access to.

not views, not synonyms -- but ALL_OBJECTS would

Alternatively, you can
| declare a table with Definer Rights (Ie The code is executed as the owner
of
| the procedure, as opposed to the user who is running the procedure).

'Definer Rights' applies to stored procedures, not tables, and it's the default setting -- i suppose this discussion really is only relevant for stored procedures that have AUTHID CURRENT_USER (or was the OP referring to PL/SQL in Forms and Reports?)

so this would be the case where dynamic sql is not involved, since the invoker may not have privilege on the objects referenced in the code (which would seem like something got kludged in the design, but i can imagine there may be a case for that on rare ocassion).

but it still boils down to handling an exception vs extra access to data dictionary

|
| The Data Dictionary is supposed to be optimised for quick access, so I
would
| personally favour this approach.
|

ouch! ouch, ouch! maybe it's optimized for oracle's recursive calls, but i've never thought of the data dictionary VIEWS as optimized for quick access (see below). plus that would be adding unnecessary overhead for the normal case (when the object exists), rather than handling the exception (must be why they're called exceptions)

SQL> select count(*)
2 from all_tables
3 where table_name = 'EMP';

COUNT(*)

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS (OUTER)
   6    5             NESTED LOOPS (OUTER)
   7    6               NESTED LOOPS (OUTER)
   8    7                 NESTED LOOPS (OUTER)
   9    8                   NESTED LOOPS
  10    9                     TABLE ACCESS (FULL) OF 'OBJ$'
  11    9                     TABLE ACCESS (CLUSTER) OF 'TAB$'
  12   11                       INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-U
          NIQUE)

  13    8                   TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
  14   13                     INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  15    7                 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  16    6               TABLE ACCESS (CLUSTER) OF 'USER$'
  17   16                 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE
          )

  18    5             TABLE ACCESS (CLUSTER) OF 'SEG$'
  19   18               INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-U
          NIQUE)

  20    4           TABLE ACCESS (CLUSTER) OF 'TS$'
  21   20             INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  22    3         TABLE ACCESS (CLUSTER) OF 'USER$'
  23   22           INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  24    2       NESTED LOOPS
  25   24         FIXED TABLE (FULL) OF 'X$KZSRO'
  26   24         INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE)
  27    2       FIXED TABLE (FULL) OF 'X$KZSPR'

Statistics

          0  recursive calls
          6  db block gets
        417  consistent gets
          0  physical reads
          0  redo size
        367  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

and which EMP table?

SQL> select count(*)
2 from all_objects
3 where object_name = 'ALL_OBJECTS';

COUNT(*)

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'USER$'
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   6    5           INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
   8    7         INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
   9    2       NESTED LOOPS
  10    9         FIXED TABLE (FULL) OF 'X$KZSRO'
  11    9         TABLE ACCESS (BY INDEX ROWID) OF 'OBJAUTH$'
  12   11           INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE)
  13    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  14    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  15    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  16    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  17    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  18    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  19    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  20    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  21    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  22    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  23    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  24    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  25    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  26    2       FIXED TABLE (FULL) OF 'X$KZSPR'

Statistics

          0  recursive calls
          4  db block gets
        262  consistent gets
          0  physical reads
          0  redo size
        367  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

-- 
Mark C. Stock
mcstock -> enquery(dot)com
www.enquery.com training & consulting

Received on Thu Dec 04 2003 - 17:26:52 CST