linked server problem

hi,
when trying to read view from oracle 9 DB with sql 2005 linked server i get null (there is no error but no data )  but when i run the select on a table i get the data .
 
thx
 
Hazera_Genetics_ITAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
as you cannot run this nd_client_info.set_org_context(83); via the linked server, you cannot run the view part via linked server neither, as visibly, the view is basing itself on that.

you need to check with the (oracle) DBA if he sees an alternative..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please show the query you run.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and also the relevant table structure from your oracle database
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Hazera_Genetics_ITAuthor Commented:
for the table : select *  FROM [test2]..[APPLSYS].[FND_OAM_CHARTS]

for the view is : select *  FROM [test2]..[APPLSYS].[xx_orders_for_qv_v ]
                         where a.order_number='106179'
what do you mean  table structure ?                      
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>where a.order_number='106179'

if the field in the oracle table is CHAR, this could be the reason.
if the field is numerical, try:
where a.order_number= 106179

>what do you mean  table structure ?                
the columns with the data types (DESC tablename in oracle)
0
 
HainKurtSr. System AnalystCommented:
do you get any data for this

select *  FROM [test2]..[APPLSYS].[xx_orders_for_qv_v ]
0
 
HainKurtSr. System AnalystCommented:
or any data for this:

select * FROM [test2]..[APPLSYS].[xx_orders_for_qv_v ] where a.order_number=106179

or for this

select * FROM [test2]..[APPLSYS].[xx_orders_for_qv_v ] where trim(a.order_number)='106179'


0
 
Hazera_Genetics_ITAuthor Commented:
i think i found the problem . something to do with Initialize the org ID .
i run the select on a view that dosnt need the  Initialize  and its working fine.

do you have any idea ?
0
 
HainKurtSr. System AnalystCommented:
what does this mean "Initialize the org ID"
0
 
Hazera_Genetics_ITAuthor Commented:
in plsql we need to run ascript if we want to open view .
/*Begin

--  Fnd_Global.APPS_Initialize(user_id => xxxx, resp_id => xxxxx, resp_appl_id => xxx);

--  Dbms_application_info.set_client_info (83);

  fnd_client_info.set_org_context(83);

End;*/

we need to do the same in ms sql but the syntex is not the same .
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I fear you cannot do this via the linked server ...
you need to create some "wrapper" for the view, on oracle side, or some view that does not need to have this context set .
0
 
Hazera_Genetics_ITAuthor Commented:
do you mean that i need to run it on the oracle DB ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.