PL/SQL (MOSC)

MOSC Banner

incorrect query result using remote read-only database

edited Aug 19, 2018 5:01AM in PL/SQL (MOSC) 8 commentsAnswered

hi all

i need some help in solving strage behaviour of select statement based on local and remote database

note

- dblink @remotedb looks to read-only database (standby)

- if dblink looks to other database which is in read-write mode, then such problem i don't see

may be problem in read-only mode of database ?

BANNER                                                                               CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0PL/SQL Release 12.1.0.2.0 - Production                                                    0CORE 12.1.0.2.0 Production                                                                  0TNS for Linux: Version 12.1.0.2.0 - Production                                            0NLSRTL Version 12.1.0.2.0 - Production                                                    0 (for all databases)/*create table table_of_ids(id  varchar2(15))/insert into table_of_ids values('ID-1');commit;create table table_needs1(id  varchar2(15))/*/declareprocedure proc1isbegininsert into table_needs1 (ID)with t as(select  --+ driving_site (T1)  T1.idfromtable_of_ids  join   T1@remotedb  on table_of_ids.id=T1.id)select * from t;commit;      dbms_output.put_line('SUCCESS proc1');exception   when others then      dbms_output.put_line('ERROR proc1');end proc1;procedure proc2isa varchar2(15);begin--select * into a  from dual@remotedb;--commit;with t as (select  ID from table_needs1unionselect '123' from dual)select --+ driving_site(T2)T2.IDinto afrom   t,    T2@remotedb   where t.id=T2.PARENT_ID;commit;      dbms_output.put_line('SUCCESS proc2 a='||'value');exception   when others then      dbms_output.put_line('ERROR

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center