14 Replies Latest reply on Feb 27, 2019 9:49 AM by hemant_k

    connect sqlserver to oracle

    hemant_k

      hi

      i am trying to read sql database through oracle

      after doing all necessary modifications

      i am testing connection\

      however i am getting error as

      'branchcode' invalid  identifier

       

      query is select accountid from dbxx9xx41@hrk1 where branchcode=2

       

       

      please

        • 1. Re: connect sqlserver to oracle
          top.gun

          How are you testing connection?
          Which tool are you using?

          We need more information.

          • 2. Re: connect sqlserver to oracle
            EdStevens

            As top.gun said, we need more information.  Since your query references db link 'hrk1', the first thing I'd want to see is the output of

             

            select * from dba_db_links where upper(db_link)='HRK1';

             

            Don't tell us what is returned. Show us what is returned by copying and pasting both the SELECT and the full result from a sqlplus session.

            • 3. Re: connect sqlserver to oracle
              L. Fernigrini

              When you mention SQL database, you mean MS SQL Server?

               

              Because if dealing with other SQL engines (like Informix) you may have to use the same case as the tables are defined in the source database.

               

              In a project  ayear ago we had to write queries against Informis using:

               

              select "accountid" from dbxx9xx41@hrk1 where "branchcode"=2

              1 person found this helpful
              • 4. Re: connect sqlserver to oracle
                hemant_k

                sir

                will be able to do so tomorrow as i am away from system .

                 

                but i have checked the same and the result is HRK1

                still will post the result tomorrow

                 

                regards

                hemu

                • 5. Re: connect sqlserver to oracle
                  hemant_k

                  will try as you suggested sir

                   

                  thanks

                  • 6. Re: connect sqlserver to oracle
                    hemant_k

                    sir

                    we are having mssql version 12 and i have established odbc connection through express edition 11rg 2 on  bit system

                    • 7. Re: connect sqlserver to oracle
                      hemant_k

                      hi

                      the result of query you supplied sir

                       

                       

                      PUBLIC HRK1 support test 25-02-19

                       

                       

                      hemu

                      • 8. Re: connect sqlserver to oracle
                        hemant_k

                        SIR

                        it's working fine

                        only  problem is to refer filed names in double quote  and adhere to the case sensitivity

                         

                         

                        thanks a lot

                         

                        however is there any workaround for this?

                        • 9. Re: connect sqlserver to oracle
                          EdStevens

                          hemant_k wrote:

                           

                          hi

                          the result of query you supplied sir

                           

                           

                          PUBLIC HRK1 support test 25-02-19

                           

                           

                          hemu

                          As I said:  "Don't tell us what is returned. Show us what is returned by copying and pasting both the SELECT and the full result from a sqlplus session."

                           

                          And you need to learn to format such, as well.  After pasting the text, select it, change the font do Courier New, and select to 'quote' it.

                           

                          SQL> select db_link, host

                            2  from dba_db_links

                            3  where upper(db_link)='MYSQLSRV';

                           

                          DB_LINK                             HOST

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

                          MYSQLSRV                            MYSQLSRV

                           

                          Makes it much easier to read, and to know exactly how you got the results you did.

                          • 10. Re: connect sqlserver to oracle
                            EdStevens

                            hemant_k wrote:

                             

                            SIR

                            it's working fine

                            only problem is to refer filed names in double quote and adhere to the case sensitivity

                             

                             

                            thanks a lot

                             

                            however is there any workaround for this?

                            The double-quote IS the work-around.  You are dealing with two different rdbms products that each treat case-sensitivity in fundamentally different ways.

                            1 person found this helpful
                            • 11. Re: connect sqlserver to oracle
                              L. Fernigrini

                              Yes, as EdStevens also mentions, that's the way you need to write the SELECT sentences to make it work.

                               

                              It may be quite annoying if you need to code a lot, but I did not find any other solution when I had to deal with that problem.

                              • 12. Re: connect sqlserver to oracle
                                EdStevens

                                EdStevens wrote:

                                 

                                hemant_k wrote:

                                 

                                SIR

                                it's working fine

                                only problem is to refer filed names in double quote and adhere to the case sensitivity

                                 

                                 

                                thanks a lot

                                 

                                however is there any workaround for this?

                                The double-quote IS the work-around. You are dealing with two different rdbms products that each treat case-sensitivity in fundamentally different ways.

                                And just to take that one step further, one should never use double quotes when referring to oracle objects.  Again, because of the way oracle treats case-sensitivity vs. the way MSSQL does.

                                 

                                In oracle if you create an object without double quotes, like these examples:

                                 

                                create table mytable (fname varchar2(10));

                                then the name of the object (in this case, the table name and the column name) will be stored in the data dictionary in upper-case, but oracle knows this and future references to 'mytable' and 'fname' without double-quotes will be accepted.  But if you create the object names with double-quotes:

                                 

                                create table "mytable" ("fname" varchar2(10));

                                then they will be entered in the data dictionary in exactly the case specified, and any future references will also need to be enclosed in double quotes and specified in exactly the case in which they were created.

                                1 person found this helpful
                                • 13. Re: connect sqlserver to oracle
                                  hemant_k

                                  HI

                                   

                                   

                                  SQL> select db_link,host from dba_db_links where upper(db_link)='HRK1';

                                   

                                   

                                  DB_LINK

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

                                   

                                   

                                  HOST

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

                                   

                                   

                                  HRK1

                                  test

                                  • 14. Re: connect sqlserver to oracle
                                    hemant_k

                                    thanks a lot sir

                                    now i am trying to generate reports using apex

                                    the issu is a query runs fine in sql command but id placed in page i get below error

                                    ----

                                     

                                    Error during rendering of page item P5_PRODUCT.

                                    ORA-04052: error occurred when looking up remote object PUBLIC.DBxxx0054@HRK1 ORA-00604: error occurred at recursive SQL level 3 ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'ContingentLiabilityAndAssetCod'. {42S22,NativeErr = 207}[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'TrackNumberOfAuthorizationPend'. {42S22,NativeErr = 207}[Microsoft][SQL Server Native Client 10

                                    i am not using mentioned column names in my query...

                                    what could be an issue?

                                    please help