sql server linked server to oracle returns no data found when data exists -


i have linked server setup in sql server hit oracle database. have query in sql server joins on oracle table using dot notation. getting “no data found” error oracle. on oracle side, hitting table (not view) , no stored procedure involved.

first, when there no data should 0 rows , not error.
second, there should data in case.
third, have seen ora-01403 error in pl/sql code; never in sql.

this full error message:
ole db provider "oraoledb.oracle" linked server "om_oracle" returned message "ora-01403: no data found".
msg 7346, level 16, state 2, line 1 cannot data of row ole db provider "oraoledb.oracle" linked server "om_oracle".

here more details, not mean since don’t have tables , data.
query problem:

select *    eopf.batch b join eopf.batchfile bf                  on b.batchid = bf.batchid           left outer join [om_oracle]..[om].[document_upload] du                  on bf.referenceid = du.documentuploadid;




can’t understand why “no data found” error. query below uses same oracle table , returns no data don’t error - no rows returned.

select * [om_oracle]..[om].[document_upload] documentuploadid = -1



query below returns data. removed 1 of sql server tables join. removing batch table not change rows returned batchfile (271 rows in both cases – rows in batchfile have batch entry). should still joining same batchfile rows same oracle rows.

select * eopf.batchfile bf    left outer join [om_oracle]..[om].[document_upload] du       on bf.referenceid = du.documentuploadid;



, query returns 5 rows. should same 5 original query. ( can’t use because need data batch , batchfile table). 

       select *    [om_oracle]..[om].[document_upload] du    du.documentuploadid    in    (    select bf.referenceid    eopf.batch b join eopf.batchfile bf                  on b.batchid = bf.batchid);

has experienced error?

today experienced same problem inner join. creating table valued function suggested codechurn or using temporary table suggested user1935511 or changing join types suggested cymorg no options me, share solution.

i used join hints drive query optimizer right direction, problem seems rise nested loops join strategy remote table locally . me hash, merge , remote join hints worked.

for remote not option because can used inner join operations. using following should work.

select * eopf.batch b join eopf.batchfile bf   on b.batchid = bf.batchid left outer merge join [om_oracle]..[om].[document_upload] du   on bf.referenceid = du.documentuploadid;

Comments

Post a Comment

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

i'm learning java , have question regarding reading file want read numbers file contains strings well. here example of file: 66.56 "3 java 3-43 5-42 2.1 1 and here coding: public class test { public static void main (string [] args){ if (0 < args.length) { file x = new file(args[0]); try{ scanner in = new scanner( new fileinputstream(x)); arraylist<double> test = new arraylist<>(); while(in.hasnext()){ if(in.hasnextdouble()){ double f=in.nextdouble(); test.add(f);} else {in.next();} } catch(ioexception e) { system.err.println("exception during reading: " + e); } } my problem add 66.56,2.1 , 1 doesn't add 3 after "3 or ignores 3-43 , 5-42 can tell me how skip strings , add doubles here? thanks all said 3 ie; "3, 3-43 , 4-42 strings either u read string , split , check number @ " , - or put in space between characters , integers. jvm after compilation tre...
Read more

rewrite - Trouble with Wordpress multiple custom querystrings -

i'm working on adding multiple querystring , doesn't seem work. i've found codes seems work others might i'm missing something. i want like: http://mydomain.com/board/?getyear=2013&getsometext2=sometext to http://mydomain.com/board/2013/sometext/ i added code below functions.php file. doesn't seem work should though. this works. echoing querystring gets both variable without problem. http://mydomain.com/board/2013/sometext/ but doesn't. tested querystring vis isset() seems nothing being set/get http://mydomain.com/board/2013/ function add_query_vars($vars) { $vars[] = "getyear"; $vars[] = "getsometext2"; return $vars; } add_filter('query_vars', 'add_query_vars'); function add_rewrite_rules($arules) { $anewrules = array('board/([^/]+)/([^/]+)/?$' => 'index.php?pagename=board&getyear=$matches[1]&getsometext2=$matches[2]'); $arules = $a...
Read more