Oracle Pass Through Query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Oracle Pass Through Query

  1. #1
    Mark Guest

    Oracle Pass Through Query


    Hello there. My ongoing saga of SQL Server to Oracle continues. I have the
    databases chatting away happily BUT I need to find a better way to query
    the Oracle DB with a wildcard. Here's what I'm doing right now from SQL
    Server to Oracle. I'm sure it's totally lame so go ahead and laugh your
    head off and then kindly provide me with a better solution:

    select * from openquery(IFSandGSPS,
    'select description from inventory_part_tab') where Description like '
    %module%'

    thanks

    Mark

  2. #2
    Chris Hylton Guest

    Re: Oracle Pass Through Query


    nothing lame that I see about...what do you think is lame about it or are
    you just not sure of your solution...I would always do exactly what you've
    done here with an 'openquery' statement running it as passthru to let Oracle
    handle the request and send back the results.

    I think what you have is fine and can't think of any other way to do it other
    than using a direct query instead of a pass-thru (which I don't recommend).
    If you have performance issues, it's due to description not being indexed,
    you could index that field, but I'm not sure that's the best solution...I'd
    look at another way to get to that data (if there is a way) through another
    key field...but, given your '%modules%', and the simplicity of the query,
    I think that's about all you can do.

    Chris

    "Mark" <mtaylor@globalsolar.com> wrote:
    >
    >Hello there. My ongoing saga of SQL Server to Oracle continues. I have

    the
    >databases chatting away happily BUT I need to find a better way to query
    >the Oracle DB with a wildcard. Here's what I'm doing right now from SQL
    >Server to Oracle. I'm sure it's totally lame so go ahead and laugh your
    >head off and then kindly provide me with a better solution:
    >
    >select * from openquery(IFSandGSPS,
    >'select description from inventory_part_tab') where Description like '
    >%module%'
    >
    >thanks
    >
    >Mark



  3. #3
    mark Guest

    Re: Oracle Pass Through Query


    Chris, thanks for the feedback. I found some other ways to zero in on the
    parts I want and those fields are easily indexed. Thanks for the vote of
    confidence. I'm only just getting up to snuff on distributed queries. I
    didn't find this solution is a book, i.e., pulling everything from oracle
    and then applying the selection criteria. I just sort of stumbled upon this
    by playing around with the Query Analyzer. Cool! I can think!

    Mark

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >nothing lame that I see about...what do you think is lame about it or are
    >you just not sure of your solution...I would always do exactly what you've
    >done here with an 'openquery' statement running it as passthru to let Oracle
    >handle the request and send back the results.
    >
    >I think what you have is fine and can't think of any other way to do it

    other
    >than using a direct query instead of a pass-thru (which I don't recommend).
    > If you have performance issues, it's due to description not being indexed,
    >you could index that field, but I'm not sure that's the best solution...I'd
    >look at another way to get to that data (if there is a way) through another
    >key field...but, given your '%modules%', and the simplicity of the query,
    >I think that's about all you can do.
    >
    >Chris
    >
    >"Mark" <mtaylor@globalsolar.com> wrote:
    >>
    >>Hello there. My ongoing saga of SQL Server to Oracle continues. I have

    >the
    >>databases chatting away happily BUT I need to find a better way to query
    >>the Oracle DB with a wildcard. Here's what I'm doing right now from SQL
    >>Server to Oracle. I'm sure it's totally lame so go ahead and laugh your
    >>head off and then kindly provide me with a better solution:
    >>
    >>select * from openquery(IFSandGSPS,
    >>'select description from inventory_part_tab') where Description like '
    >>%module%'
    >>
    >>thanks
    >>
    >>Mark

    >



  4. #4
    Chris Hylton Guest

    Re: Oracle Pass Through Query


    Great...glad you found a better query...

    If you play around with linked servers in SQL Server much, you'll find that
    SQL Server (actually it's the data providers fault) isn't too bright when
    it comes to using indexes on the linked platform (in your case Oracele).
    The OPENQUERY statement resolves this issue by doing pass-thru and I pretty
    much don't use direct queries unless linking to Access or SQL Server, I use
    OPENQUERY all the time (especially for Oracle).

    Chris

    "mark" <mtaylor@globalsolar.com> wrote:
    >
    >Chris, thanks for the feedback. I found some other ways to zero in on the
    >parts I want and those fields are easily indexed. Thanks for the vote of
    >confidence. I'm only just getting up to snuff on distributed queries. I
    >didn't find this solution is a book, i.e., pulling everything from oracle
    >and then applying the selection criteria. I just sort of stumbled upon

    this
    >by playing around with the Query Analyzer. Cool! I can think!
    >
    >Mark
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>nothing lame that I see about...what do you think is lame about it or are
    >>you just not sure of your solution...I would always do exactly what you've
    >>done here with an 'openquery' statement running it as passthru to let Oracle
    >>handle the request and send back the results.
    >>
    >>I think what you have is fine and can't think of any other way to do it

    >other
    >>than using a direct query instead of a pass-thru (which I don't recommend).
    >> If you have performance issues, it's due to description not being indexed,
    >>you could index that field, but I'm not sure that's the best solution...I'd
    >>look at another way to get to that data (if there is a way) through another
    >>key field...but, given your '%modules%', and the simplicity of the query,
    >>I think that's about all you can do.
    >>
    >>Chris
    >>
    >>"Mark" <mtaylor@globalsolar.com> wrote:
    >>>
    >>>Hello there. My ongoing saga of SQL Server to Oracle continues. I have

    >>the
    >>>databases chatting away happily BUT I need to find a better way to query
    >>>the Oracle DB with a wildcard. Here's what I'm doing right now from SQL
    >>>Server to Oracle. I'm sure it's totally lame so go ahead and laugh your
    >>>head off and then kindly provide me with a better solution:
    >>>
    >>>select * from openquery(IFSandGSPS,
    >>>'select description from inventory_part_tab') where Description like '
    >>>%module%'
    >>>
    >>>thanks
    >>>
    >>>Mark

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center