How to set DBMS_APPLICATION_INFO.SET_CLIENT_INFO in linked ORACLE server from SQL 2000

• Freeman_74

  Old Hand

  Points: 325

  More actions

  March 6, 2009 at 6:25 am

  #200572

  I could manage to create the linked server from SQL 2000 to Oracle 9i like this:

  EXEC sp_addlinkedserver

  @server = 'Oracle',

  @srvproduct = 'Oracle',

  @provider = 'MSDAORA',

  @datasrc = 'server:port/SID'

  GO

  EXEC sp_addlinkedsrvlogin Oracle, FALSE, sa, apps, apps

  GO

  When I run this (in order to test the connection) I get a list of the tables in Oracle

  EXEC sp_tables_ex ORACLE

  GO

  The problem now is that now, when I try to run queries I get no data... I know this is because I need to set ORG_ID before trying to execute the queries...

  I don't know (and couldn't find in the internet) how to set run this (which I execute in TOAD to before running queries in Oracle):

  BEGIN

  DBMS_APPLICATION_INFO.SET_CLIENT_INFO('136')

  END;

  And the other thing I think I'm missing is to set NLS_LANGUAGE for the session

  "ALTER SESSION SET NLS_LANGUAGE = 'LATIN AMERICAN SPANISH';"

  How can I achieve these two things from a SQL2000 Stored Procedure??

  Any help will be greatly appreciated...

  JP

  Juan P. Realini
  Buenos Aires, Argentina
  Web Developer, .Net Developer,
  Windows Applications, SQL Server 2000
  "A man's gotta do what a man's gotta do... that is, do all his wife says... "

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  May 4, 2009 at 4:05 pm

  #988458

  Since an Oracle package is nothing but a collection of storedprocs example next applies, please check this http://blogs.objectsharp.com/cs/blogs/matt/archive/2005/06/13/2221.aspx

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.