Database link problem based on size of data.

Database link problem based on size of data.

Post by Larry Petti » Sun, 31 Dec 1899 09:00:00



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,

  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,

  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.