by 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.