Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Systimestamp format

CharlovApr 6 2009 — edited Apr 6 2009
Hi All,

I am trying the below

SELECT to_timestamp('02-12-09 12:12:35.1243', 'DD-MM-YY HH:MI:SS.FF4') FROM DUAL

This is working and is ok.
But it is not exactly what I need. I need to convert the following timestamp _'02-12-09 12:12:35.1243 AM'_

and
SELECT to_timestamp('02-12-09 12:12:35.1243 AM', 'DD-MM-YY HH:MI:SS.FF4') FROM DUAL

does not work._

Help :)
This post has been answered by 21205 on Apr 6 2009
Jump to Answer

Comments

mxallen-Oracle

Hello,

My first question is did you actually install Database Gareway for SQL Server (DG4MSQL) on two different machines (Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (64-bit) on Windows Server 2008R2 SP1 and Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (32-bit) on Windows Server 2003R2 x32 SP2).?

If so, did you use the 64-bit version on the 64-bit server and the 32-bit version on the 32-bit server?

Also, DG4MSQL is licensed separately from the Oracle Database.  This is to simply let you know.

As far as the information in the configuration files, the glaring error is with the tnsnames.ora file entry in the Oracle Database homes.

You have this:

SQLLAB.world=(DESCRIPTION =

        (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP )(HOST = msvo-tg4msql)(PORT = 1521))

        )

        (CONNECT_DATA =

        (SID = tg4msql))

        (HS = OK))


The gateway listener shows this:


  1. SID_LIST_LISTENER= 
  2.    (SID_LIST= 
  3.       (SID_DESC=  
  4.          (SID_NAME=dg4msql) 
  5.          (ORACLE_HOME=F:\oracle) 
  6.          (PROGRAM=dg4msql) 
  7.       ) 
  8.    ) 
  9. LISTENER = 
  10.   (DESCRIPTION_LIST = 
  11.     (DESCRIPTION = 
  12.       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.48.104.18)(PORT = 1521)) 
  13.     ) 
  14.   ) 
  15. ADR_BASE_LISTENER = F:\oracle 

The SID values do not match nor the host values (although the ip address of msvo-tg4msql might be
10.48.104.18)

With that said, modify the tnsnames.ora entry in the Oracle DB home to read:

SQLLAB.world=(DESCRIPTION =

        (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP )(HOST =10.48.104.18)(PORT = 1521))

        )

        (CONNECT_DATA =

        (SID = dg4msql))

        (HS = OK))


Test and see if this works.


Regards,

Matt

FrAGarcher 
mxallen wrote:




Hello,




My first question is did you actually install Database Gareway for SQL Server (DG4MSQL) on two different machines (Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (64-bit) on Windows Server 2008R2 SP1 and Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (32-bit) on Windows Server 2003R2 x32 SP2).?




If so, did you use the 64-bit version on the 64-bit server and the 32-bit version on the 32-bit server?




.

Yes I did so. During my test there were two separate installations.

First, I installed all 64-bit versions: both Database Gareway for SQL Server and MS SQL Server 2008R2. Each application were instaled on its own dedicated Windows-server.

Second I installed all 32-bit versions: both Database Gareway for SQL Server and MS SQL Server 2005 SP4. Each application were instaled on the same Windows-server.

And yes, I know about licensing model.

mxallen wrote:






SQLLAB.world=(DESCRIPTION =


        (ADDRESS_LIST =


        (ADDRESS = (PROTOCOL = TCP )(HOST =10.48.104.18)(PORT = 1521))


        )


        (CONNECT_DATA =


        (SID = dg4msql))


        (HS = OK))





Test and see if this works.





Regards,


Matt

No, still not works...

Actually we already have similar system installed in our production. But slightly different. If I'm not wrong: It is the  Transparent Gareway for SQL Server that was distributed with Oracle 10g database server. Gateway is installed alongside MS SQL Server 2005 SP4 x32 on the same Windows Server 2003R2 SP2 x32.

Production complex is running normally. Initially I tried to copy .ora cofig files from the production server to the test server, but of course failed.

mxallen-Oracle

What is the error you are seeing?

- Matt

FrAGarcher

When I try tor perform some SELECT command I get an error like this: [1]: (Error): ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message 12564 ORA-02063: preceding 2 lines from ETWEBTEST

http://i58.tinypic.com/6qckyt.jpg

Kgronau-Oracle

ORA-28545 is a configuration issue.

Could you please post:

1. Gateway listener,

2. lsnrctl status output for the gateway listener. Open a DOS command line Window and execute: F:\oracle\bin\lsnrctl status

3. from the same command line window: F:\oracle\bin\dg4msql

4. tnsnames.ora entry from the Oracle database

5. from the Oracle database machine execute: tnsping SQLLAB.world

- Klaus

FrAGarcher

kgronau,

1. Gateway listener - sorry, Ididn't quite undestand what should I do? Should I post Gateway listener config file? If so - I posted it at the start of the topic:

listener.ora  - on Oracle Database Gateway

  1. # listener.ora Network Configuration File: F:\oracle\network\admin\listener.ora 
  2. # Generated by Oracle configuration tools. 
  3. SID_LIST_LISTENER= 
  4.    (SID_LIST= 
  5.       (SID_DESC=  
  6.          (SID_NAME=dg4msql) 
  7.          (ORACLE_HOME=F:\oracle) 
  8.          (PROGRAM=dg4msql) 
  9.       ) 
  10.    ) 
  11. LISTENER = 
  12.   (DESCRIPTION_LIST = 
  13.     (DESCRIPTION = 
  14.       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.48.104.18)(PORT = 1521)) 
  15.     ) 
  16.   ) 
  17. ADR_BASE_LISTENER = F:\oracle 

2. I have a problem with it: NLS_LANG is set to RUSSIAN_RUSSIA.CL8MSWIN1251 therefore I get a number of strange symbols instead of normal list. Ah.. I understood Since I have changed NLS_LANG  to AMERICAN_AMERICA.CL8MSWIN1251   I get readable rsponse

Here it is:

C:\Documents and Settings\Administrator>F:\oracle\bin\lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-JUL-2014 09:42:17

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.104.18)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production

Start Date                18-JUL-2014 09:39:52

Uptime                    0 days 0 hr. 2 min. 25 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   F:\oracle\network\admin\listener.ora

Listener Log File         f:\oracle\diag\tnslsnr\msvo-tg4msql\listener\alert\log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.48.104.18)(PORT=1521)))

Services Summary...

Service "dg4msql" has 1 instance(s).

  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

C:\Documents and Settings\Administrator>

3. from the same command line window: F:\oracle\bin\dg4msql

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>F:\oracle\bin\dg4msql


Oracle Corporation --- FRIDAY    JUL 18 2014 09:45:44.890

Heterogeneous Agent Release 11.2.0.1.0 - Production  Built with
   Oracle Database Gateway for MSSQL

4. tnsnames.ora - on Oracle Database server

  1. SQLLAB.world=

(DESCRIPTION =

        (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP )(HOST = msvo-tg4msql)(PORT = 1521))

        )

        (CONNECT_DATA =

        (SID = tg4msql))

        (HS = OK))



5. tnsping SQLLAB.world


bash-3.2$ tnsping SQLLAB

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 18-JUL-2014 09:36:59

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/11.2.0.3/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.48.104.18)(PORT = 1521))) (CONNECT_DATA = (SID = tg4msql)) (HS = OK))

OK (0 msec)

Kgronau-Oracle
Answer

You didn't correct your database tnsnames.ora file - you still have the wrong SID entry in there- see the tnsping:

(CONNECT_DATA = (SID = tg4msql)) (HS = OK))

=> according to your listener.ora file the SID is dg4msql.

So please open the tnsnames.ora file on your Oracle database server and change the SQLLAB alias to:

SQLLAB.world=(DESCRIPTION =

        (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP )(HOST =10.48.104.18)(PORT = 1521))

        )

        (CONNECT_DATA =

        (SID = dg4msql))

        (HS = OK))


Marked as Answer by FrAGarcher · Sep 27 2020
1 person found this helpful
FrAGarcher

Yes You were right!

After I've changed SID I got my data from my test MS SQL database! OMG... just one wrong symbol and two days to dance around ((((

mxallen, kgronau Thank You alot!!

But... Just another question.

Now I'm trying to select some data from a test table and faced to a problem:

SELECT * FROM "Sales"."Store"@ETWEBTEST.WORLD

[1]: (Error): ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Oracle][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'Store', database 'AdventureWorks', schema 'Sales'. {42000,NativeErr = 229} ORA-02063: preceding 2 lines from ETWEBTEST

But! If I grant sysadmin server role to the user account RECOVER - everything works correctly. If this account belongs only to the public server role - I get the error despite I mapped the RECOVER user account to my test database (AdventureWorks) as db_owner.

What I do wrong now? How to restrict the user account to the public server role?

Kgronau-Oracle

The error message is clear - you do not have (when the public server role is granted) the permissions to access that object. You should check that with your SQL Server admin if users with that role can access that object.

By the way, the RECOVER account shouldn't be used for normal selects. In the create database link statement you should specify a normal SQL Server username for example "sa". The recover account is only used for distributed transactions where the gateway has to write a temporary record into the HS_TRANSACTION_LOG table. The recover user only needs to own that object and needs to have full permissions on this table. Using that user to select/manipulate other resources is risky as you can find the user's password in the gateway init file (as long as you do not encrypt it).

- Klaus

FrAGarcher

Yes the meaning of the error message is quite clear, but since I myself is an administrator of the MS SQL Server I have granted a set of rights for the RECOVERY account . Therefore it was a surprise for me that I can perform select to a table on the MS SQL only in case when the RECOVERY user belongs to the sysadmin role...

So I assume that the RECOVERY account should have additional rights to accsess to some system object. But which objects?..

-> In the create database link statement you should specify a normal SQL Server username for example "sa". Coould You please explain this in more detail: how can I do this. Pardon me for such a question, I work with the Gateway first time...

And one other question: may be there is another way to build communication between Oracle and MS SQL server? I mean I remember that DG4MSQL requires one more license so may be there is a freeware to solve the task?

I've learned some resources upont the subject and found out that some DBA use an hsodbc...

Kgronau-Oracle

Regarding the SQL Server privileges, please have a look at the MS documentation - for example start here: http://msdn.microsoft.com/en-gb/library/ms188659.aspx

It is also a good idea to open the SQL Server Management Studio - click on the System Database and to check out the Database roles and the permissions granted to public. For example here you see which objects you can access by default. Either grant more privs to that role or use another user who is able to select the objects.

Regarding the database link. You currently created it like: create database link <name of your link> connect to "RECOVER" identified by ...

Let's assume you want to use a different user - for example let's use the SQL Server admin account (it is up to you to decide if that makes sense or not and matches your security requirements).

So first drop the already created link

drop database link ETWEBTEST.WORLD;

recreate it now with a different UID in the link: create database link ETWEBTEST.WORLD connect to "sa" identified by "<sa's password>" using 'SQLLAB.world';


As you said - DG4MSQL requires a license. The free product is called Database Gateway for ODBC. It uses the MS SQL Server ODBC driver to connect to the SQL Server. It is the follow up product of the desupported HSODBC.

- Klaus

Mkirtley-Oracle

There is a difference in the functionality between DG4MSQL and DG4ODBC so if you have access to My Oracle Support have a  look at this note -

Functional Differences Between DG4ODBC and Specific Database Gateways (Doc ID 252364.1)

So it depends on what you want to do with the gateway as to which you use. For example, DG4ODBC does not support distributed transactions.

Regards,

Mike

FrAGarcher

@"kgronau"

kgronau написал(а):




Regarding the SQL Server privileges, please have a look at the MS documentation - for example start here: http://msdn.microsoft.com/en-gb/library/ms188659.aspx


It is also a good idea to open the SQL Server Management Studio - click on the System Database and to check out the Database roles and the permissions granted to public. For example here you see which objects you can access by default. Either grant more privs to that role or use another user who is able to select the objects.

ok, I'll see, thank You.

Regarding the database link. You currently created it like: create database link <name of your link> connect to "RECOVER" identified by ...


Let's assume you want to use a different user - for example let's use the SQL Server admin account (it is up to you to decide if that makes sense or not and matches your security requirements).


So first drop the already created link


drop database link ETWEBTEST.WORLD;


recreate it now with a different UID in the link: create database link ETWEBTEST.WORLD connect to "sa" identified by "<sa's password>" using 'SQLLAB.world';

So If understood rihgt: there may be two accounts - in initdg4msql.ora and in create dblink statement. Am I correct?

As you said - DG4MSQL requires a license. The free product is called Database Gateway for ODBC. It uses the MS SQL Server ODBC driver to connect to the SQL Server. It is the follow up product of the desupported HSODBC.




- Klaus

would You please give me points to an instruclion how to setup and to configure  the  DG4ODBC?

Here, in the conference I found a topic with a link: How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1). But it's about configuring DG4ODBC on 64bit Unix OS. And what about similar instructions on windows systems?

I found this: How to setup dg4odbc

Is this instruction correct?

Download the Gateway:

In case the 11gR2 64 install does not install the latest g4odbc gateway, do as follow:

Click on the Patches and Updates tab in My Oracle Support. Put in patch number 10098816 and

Microsoft Windows x64 (64-bit) for the platform.

This is the 11.2.0.2 database patchset. The gateways distribution is the 5th file. You can

verify that by looking at the patch readme.

This file is the complete gateway cd. You only need to download this one file. When you run the setup, it will create the 11.2.0.2 oracle home

and install the gateway that you select while running the setup. Select the odbc gateway (dg4odbc).

But I still didn't understand: where can I get an installation pack of DG4ODBC?

FrAGarcher

Well

Actually the task I have to consider is migration to a new server.

Now I have a souce system:

1. Oracle Database Server 10g Release 1

+

2. MS SQL Server  2005 SP4 and  Oracle TG4MSQL  - both installed on the same windows server 2003 R2SP2.

I have to consider how to migrate an applicational database placed on the MS SQL Server. Additional problem is that there is no documentation upon this source system and I don't know who performed deployment of the system. So I have to "explore" it from the groud zero...

Another problem is that the applicational database is planned to migrate to our corporate "big-n-fat" MS SQL Server 2008 R2 where we are going to consolidate as more applicatioanl database from separated ms sql servers  as we can. So I cannot do deployment on this "big-n-fat" mssql-server anything including the TG4MSQL/DG4ODBC because "big-n-fat" is in windows failover cluster.

Sorry I realyze that two many words, but I try to explain a background..

What if to install Oracle TG4MSQL to a dedicated Windows Server 2008 R2 SP1? May be it will be the simpliest way - just to copy configuration from the source system on a new one? But then where can I get TG4MSQL software installation package for Windows? Is it still supported?

Kgronau-Oracle

@So If understood rihgt: there may be two accounts - in initdg4msql.ora and in create dblink statement. Am I correct?

Yes, in general these are 2 different accounts. One that restricts the access to the objects which is specified in the create database link statement and the recovery account which is only needed for the gateway to temporarily store a transaction id in the transaction log.

@Here, in the conference I found a topic with a link: How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1). But it's about configuring DG4ODBC on 64bit Unix OS. And what about similar instructions on windows systems?

=> there's a dedicated note for DG4ODBC on Windows:

How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install(Doc ID 466225.1)

and

How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to Connect to Non-Oracle Databases Post Install(Doc ID 1266572.1)


@But I still didn't understand: where can I get an installation pack of DG4ODBC?

It is on the same CD as you already used to install DG4MSQL. Instead of installing Database Gateway for MS SQL Server choose Database Gateway for ODBC.

As an alternative you could use the latest 11.2.0.4 CD pack. Log into My Oracle Support and look for the 11.2.0.4 patch set: Patch 13390677: 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER

Make sure to choose the correct platform (Windows 32bit/64bit) and then check out the readme which CD contains the Gateway software.


- Klaus

1 person found this helpful
FrAGarcher

Thank You!

FrAGarcher

@ As an alternative you could use the latest 11.2.0.4 CD pack. Log into My Oracle Support and look for the 11.2.0.4 patch set:  Patch 13390677: 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER

Pardon me: the link doesn't work...

P.S. I managed to find the patch set via a serch form on https://support.oracle.com/

but the patch set consists of 7 zip-files... Wich file should I download?

P.P.S. I found!

It's 5th zip-file !! :-)

Well

dg4odbc works.

But there is another question: in initdg4msql.ora I put

  1. HS_FDS_RECOVERY_ACCOUNT=RECOVER 
  2. HS_FDS_RECOVERY_PWD=RECOVER

However in instructions upon configuring dg4odbc I didn't see such parametres pattern. Should I put them manually in initdg4odbc.ora?

Mkirtley-Oracle

Hi,

   DG4ODBC does not support distributed transactions so there is no need to add a recovery account to recover failed transactions.

Regards,

Mike

FrAGarcher

Ah... Ok, I understood, thank You!

FrAGarcher

One other question:

Did The Oracle Transparent gateway for MS SQL in the 10g version require additional license as Oracle Database Gateway for MS SQL does 11g?

Kgronau-Oracle

All dedicated gateways connecting to a certain foreign database require a license.  Only the ODBC based gateways like the old and desupported HSODBC and its follow up product Database Gateway for ODBC are for free.

- Klaus

FrAGarcher

Clear enough, thank You!

1 - 22
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 4 2009
Added on Apr 6 2009
5 comments
3,033 views