Solved

linked server problem

Posted on 2011-03-15
12
263 Views
Last Modified: 2012-05-11
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
 
0
Comment
Question by:Hazera_Genetics_IT
  • 5
  • 4
  • 3
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138681
please show the query you run.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138686
and also the relevant table structure from your oracle database
0
 

Author Comment

by:Hazera_Genetics_IT
ID: 35138781
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138801
>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
 
LVL 51

Expert Comment

by:HainKurt
ID: 35138886
do you get any data for this

select *  FROM [test2]..[APPLSYS].[xx_orders_for_qv_v ]
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35138909
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Hazera_Genetics_IT
ID: 35139554
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 35140084
what does this mean "Initialize the org ID"
0
 

Author Comment

by:Hazera_Genetics_IT
ID: 35140888
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35141478
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
 

Author Comment

by:Hazera_Genetics_IT
ID: 35145966
do you mean that i need to run it on the oracle DB ?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35146139
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now