Home » SQL & PL/SQL » SQL & PL/SQL » Hetergenous services (Oracle 10g)
Hetergenous services [message #338411] Mon, 04 August 2008 22:30 Go to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
I am connecting to a SQL Server 2000 database from Oracle 10g using hetergenous services successfully.
But,i have a problem .some of the column names in the SQL Server 2000 are more than 30 characters which are creating problems in Oracle 10g as I am getting "Idenifier too long" error.

Is there any way that I can select columns whose length is more than 30 characters in Oracle ?
I have to fetch all the rows (based on certain conditions) from SQL Server 2000 and insert it into Oracle 10g tables ?

Regards

Re: Hetergenous services [message #338412 is a reply to message #338411] Mon, 04 August 2008 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Here is a complete guess which may not be worth the electrons to hold it.

On SQLServer can you create a view with column names 30 characters or fewer & SELECT against the view?
Re: Hetergenous services [message #338413 is a reply to message #338411] Mon, 04 August 2008 23:05 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Thanks anacedent.

The SQL Server 2000 database is not under our control and we have only "Select" privilege on the database.

So,I cannot create any view on the source system.

Regards
Re: Hetergenous services [message #338775 is a reply to message #338411] Tue, 05 August 2008 22:39 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Got the solution.

Using DBMS_HS_PASSTHROUGH.

[Updated on: Tue, 05 August 2008 23:46] by Moderator

Report message to a moderator

Re: Hetergenous services [message #338799 is a reply to message #338775] Tue, 05 August 2008 23:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the valuable feedback, can you explain more?

By the way, I added a link on your package name.

Regards
Michel

[Updated on: Tue, 05 August 2008 23:46]

Report message to a moderator

Re: Hetergenous services [message #338812 is a reply to message #338411] Wed, 06 August 2008 00:39 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi Michel,
First of all ,let me give you a brief overview of my project.
We have a Server A (OS-UNIX,Oracle 10g) and a Server B (OS-Win 2000,Oracle 8i).
On the Server B , all the Heterogenous setting to SQL Server are done.
On Server B,Synonyms are created on the actual SQL Server tables using Database Link which uses DSN to connect to SQL Server.

On Server A , we have created a Database Link to Server B and getting data using synonyms on Server B.

In our previous implementation ,we were not able to refer to some columns whose length is more than 30 as Oracle does not support names with more than 30 characters.
So,we used syntax of DBMS_HS_PASSTHROUGH to write a procedure to fetch the data.
The example can be :
DECLARE
   val  VARCHAR2(100);
   c    INTEGER;
   nr   INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@SQLSERVER_DBLINK; 
  DBMS_HS_PASSTHROUGH.PARSE@SQLSERVER_DBLINK(c, 
    'select top 10 column_with_more_than_thirty_charatcers from Table A');
  LOOP
    nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@SQLSERVER_DBLINK(c);
    EXIT WHEN nr = 0;
    DBMS_HS_PASSTHROUGH.GET_VALUE@SQLSERVER_DBLINK(c, 1, val);
    Insert Into Temp values (val);
  END LOOP;  

  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@SQLSERVER_DBLINK(c); 

END;



By using this procedure data can be inserted into a local temp. table and Synonyms can be created on the Temp. tables.

regards
Re: Hetergenous services [message #338817 is a reply to message #338812] Wed, 06 August 2008 00:54 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks again for this valuable post.

Regards
Michel
Previous Topic: Bulk insert is very slow
Next Topic: Analytics functions help
Goto Forum:
  


Current Time: Sat Dec 10 04:57:10 CST 2016

Total time taken to generate the page: 0.10681 seconds