database link problem based on size of data

From: Larry Pettit <larry.pettit_at_ps.net>
Date: Tue, 14 Sep 1999 16:43:19 -0600
Message-ID: <v7AD3.578$ee7.24529@news.uswest.net>

Database link problem based on size of data.

It works for a query on a state like 'WA' with 762 rows in zipcode but it doesn't work on 'CA' with 2950 rows. Could there be a problem with the index?

Thanks

(database NEWDB) table user1.m_state

 Name                            Null?    Type
 ------------------------------- -------- ----
 STATE_SEQ                NOT NULL NUMBER(3)
 NAME                            NOT NULL VARCHAR2(30)
 CODE                            NOT NULL CHAR(2)

(database OTHERDB), table user2.zipcode

 Name                            Null?    Type
 ------------------------------- -------- ----
 ZIP                                  NOT NULL VARCHAR2(10)
 CITY                               NOT NULL VARCHAR2(40)
 STATE                                               CHAR(2)


*************************************************

(run from sql *plus, logged into NEWDB)

SQL>  select  B.zip,
  2           B.city,
  3           A.state_seq
  4   from   user1.m_state A,
  5          user2.zipcode_at_otherdb B
  6          where A.code = B.state and B.state = 'CA';
        where A.code = B.state and B.state = 'CA'
                                             *

ERROR at line 6:
ORA-00604: error occurred at recursive SQL level ORA-02063: preceding line from otherdb

THIS ONE WORKS!

SQL>  select B.zip,
  2          B.city,
  3          A.state_seq
  4   from   user1.m_state A,
  5          user2.zipcode_at_otherdb B
  6          where A.code = B.state and B.state = 'WA';

ZIP        CITY                                     STATE_SEQ
---------- ---------------------------------------- ---------
98001      AUBURN                                          59
98796      SEATTLE                                         59
"   "      "                                      "         "
99200      SPOKANE                                         59
99257      SPOKANE                                         59
99259      SPOKANE                                         59

762 rows selected. Received on Tue Sep 14 1999 - 17:43:19 CDT