Re: Detecting the existence of remote table.

Well I figured it out so, dont bother replying. And since I am guilty of premature posting I will share my code with anyone interested.

/*

• Code detects for the presence of a table on a remote machine.
• It attempts to select the sysdate from the table. If the table is
• empty, does not exist, or any other error occurs while trying to access the
• remote table the OTHERS exception fires. Make sure you have the rownum = 1
• statement in the where clause or the select statement will return a sysdate
• for every record in the table. We only need to check the first record to
• determine if the table exists and data is present. */

DECLARE
  datex date;
BEGIN
  select sysdate into datex from your_table_at_remote_database.world where rownum = 1;

  /* SUCCESS - the table exists 
   * 
   * Do whatever code you like here....
   *
   */
  

EXCEPTION
  when OTHERS then

  /* FAILURE - the table does not exist or no data exists in table.
   *
   * Do whatever code you like here...
   *  
   */
    

END;
/

Mark Reichman wrote:

> 
> In PL/SQL I would like to detect the existence of
> a table on a remote machine. (i.e. the remote machine
> is alive and the table exists.)  If it does exitst, I would
> like to truncate my local copy and copy the data from the
> remote machine into my table. If it doesnt exist, I want
> to leave my local table alone and retry later. I can do
> most of this off the top of my head.  I cannot figure out
> a way to detect the existance of the remote table before I
> truncate and populate my local table.  All database links
> already exist and work. Thanks