Post reply

Calling Oracle Procedure from SQL Procedure

  • February 13, 2006 at 8:15 am

    #89418

    Can anyone tell me how to call an Oracle Procedure from a SQL Procedure.  The Oracle procedure I want to call is a query that performs an "Insert" statement and then returns a few parameters.  My DBA has set up a linked server for this all I need is the correct syntax.  We work on SQL Server 2000.  I'll REALLY appreciate any help.

     

  • February 14, 2006 at 7:06 am

    #620661

    Hi John,

    It's really very simple, here's some of my code :

    SELECT [ID], MATCHCODE_1, CAST(KZ_TVO AS CHAR(4)) AS KZ_TVO

    INTO #austriaink

    FROM OPENQUERY(TLSTUK,'SELECT ARTIKEL.*,FARBEN_LOESUNGSMITTEL.KZ_TVO

    FROM TLS.ARTIKEL, TLS.FARBEN_LOESUNGSMITTEL WHERE ID LIKE ''00006%'' AND ARTIKEL.ID = FARBEN_LOESUNGSMITTEL.AR_ID')

    OK, so here's the thing :

    1) "Select BLAH from openquery" is the SQL native code to tell SQL server you're using an openquery server.

    2) Everything else is then inside brackets.

    3) TLSTUK is the name of our linked server, then a comma, then you must put the whole query into quotes - you are essentially sending a text string through the linked server.

    If you need to use single quotes in the query itself life becomes a little more complex, you need to use TWO SINGLE QUOTES, for example in a WHERE clause it would be WHERE blah = ''5''.

    There are some situations where you need to explicitly specify the single quote as CHAR(39), for instance when you're building a string dynamically.

    Also you might find some problems with datatypes, do the conversion in the Oracle query, so TOCHAR etc. Also, I think this is bad programming on our Oracle peoples side, sometimes you'll get numerics coming across with nulls in, which SQL server doesn't understand, so you have to use NVL(value, 0) to fill them with zeros.

    And don't get me started on MDAC 2.7 lah lah lah I'm not listening.

    But if you follow those simple rules you should be fine.

    Word of warning - there is a bug in Enterprise manager - when you first create a stored procedure using openquery DO IT ON THE SERVER, NOT THROUGH ENTERPRISE MANAGER ON YOUR CLIENT. If you ignore this advice your linked server will fall over, there are a couple of other ways to get it to fall over as well, which I forget at present. When it does this the only way to reset it (that I've found - contradictions appreciated) is to stop and start the SQL service.....

    Have fun, I certainly did.

  • February 14, 2006 at 9:13 am

    #620735

    Is this using an Oracle stored procedure?

    I'm curious as well.

    *I have to try this one out

    http://objectsharp.com/blogs/matt/archive/2005/06/13/2221.aspx

  • February 15, 2006 at 6:54 am

    #621011

    Thanks for your help guys but I'm actually calling an Oracle procedure from my SQL procedure.  We've managed to retrieve values returned from Oracle by changing the Oracle procs to return PL SQL tables instead of normal parameters.  This seems kind of heavy duty and convoluted to us, is there no way of retrieving normal parameters from Oracle?

     

    Thanks again for your help. 

  • February 16, 2006 at 2:54 am

    #621286

    Sorry John, I see now I didn't explain myself right - actually our approach was just to cut and paste the Oracle proc into SQL and call it from there using the parameters as variables in the SQL query, there were so many oddball problems doing it the other way round we just didn't see the point in trying.

    Here's some sample code for building a string to pass across to openquery if you're interested: 

    SELECT @sql_str = ''

       SELECT @sql_str = @sql_str + 'INSERT #Bob_Details3 SELECT '

       SELECT @sql_str = @sql_str + 'ID '

       SELECT @sql_str = @sql_str + 'FROM OPENQUERY(TLSTUK,' + char(39)

       SELECT @sql_str = @sql_str + 'SELECT '

       SELECT @sql_str = @sql_str + 'eb.ID '

       SELECT @sql_str = @sql_str + 'FROM '

       SELECT @sql_str = @sql_str + 'tls.etiketten_bobinen eb '

       SELECT @sql_str = @sql_str + 'WHERE '

       SELECT @sql_str = @sql_str + 'eb.BOBINENNR = ' + char(39) + char(39)+ rtrim(@bob_key) + char(39) + char(39)

       SELECT @sql_str = @sql_str + char(39)

       SELECT @sql_str = @sql_str + ')'

    EXEC(@sql_str)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply