locked
using sp to pass parameters to oracle server via openquery RRS feed

  • Question

  • Howdy,

    My SS sp compiles fine but when I attempt to execute, it yells at me:

    Msg 102, Level 15, State 1, Line 7  Incorrect syntax near 'myOracleServer'.

    My sp is setup like this to build the openquery select to Oracle:

    begin
    declare @linkedserver char(14) = 'myOracleServer';
    -- actual server link name is 3 chars long, i.e. 'abc'
    declare @openquery varchar(500);
    declare @plsql varchar(8000);
    set @openquery = 'SELECT * FROM OPENQUERY(' + @linkedserver + ','''
    set @plsql = 
    '
    select tbl1.fld1
    from myOracleTable
    where fld2 = ''''' + @param1 + '''''
    ''
    )
    '
    exec (@openquery + @plsql)
    end
    Anyone see any possible reason why it's tripping on syntax for the server name?  (I actually have @linkedserver set to char(3) to reflect the actual linked server name length)

    Friday, August 10, 2012 7:42 PM

Answers

  • ok, got it:

    • plsql literals need 4 single quotes
    • escapement for parameters need 5 single quotes
    • Marked as answer by DimDude Friday, August 10, 2012 9:36 PM
    Friday, August 10, 2012 9:36 PM

All replies

  •  try :

    declare @param1 varchar(5) = 'test'

    declare @linkedserver char(14) = 'myOracleServer';
    -- actual server link name is 3 chars long, i.e. 'abc'
    declare @openquery varchar(max);
    declare @plsql varchar(max);
    set @openquery = 'SELECT * FROM OPENQUERY(' + @linkedserver + ','''
    set @plsql = 
    '
    select tbl1.fld1
    from myOracleTable tbl1
    where fld2 = '' + @param1 + ''
    ''
    )
    '
    print (@openquery + @plsql)

    Friday, August 10, 2012 8:46 PM
  • thx for the print tip!  it's not tripping on the server reference (myOracleServer is really ~ 'abc') - it's tripping on that same string value because it occurs within the actual plsql statement:

    select ''abc'' col_alias from tbl where col2 = ''''' + @param1 + '''''

    ... so I need to figure out how to properly encapsulate that literal value... (there's a few more like that in the plsql body as well)

    Friday, August 10, 2012 9:26 PM
  • ok, got it:

    • plsql literals need 4 single quotes
    • escapement for parameters need 5 single quotes
    • Marked as answer by DimDude Friday, August 10, 2012 9:36 PM
    Friday, August 10, 2012 9:36 PM