Param to pass to Oracle sp_ and sp_ to ret 1 or 0.

• AlexSQLForums

  SSChampion

  Points: 14277

  More actions

  August 9, 2006 at 3:59 pm

  #113259

  I have an oracle linked server setup with ODBC data source which uses OLE DB Provider for Oracle.

  RPC AND RPC OUT are checked.

  In oracle there is a stored procedure that does some updates and returns an output of 1 or 0.

  I have to pass a variable to that oracle stored procedure from sql server.

  And that stored procedure has to pass 1 or 0 back to sql server, depending on the value received i update a table.

  Stored procedure is ready and when i try to pass a variable in this query:

  declare @status bit, @Username varchar(50)

  set @Username = 'ABC'

  exec DEV..SYSADM.update_procedure @Username,@status

  if @status = 1 Print 'Success' Else Print 'Failed'

   

  I get an error.

  Server: Msg 7212, Level 17, State 1, Line 5

  Could not execute procedure 'update_procedure' on remote server 'DEV'.

  [OLE/DB provider returned message: One or more errors occurred during processing of command.]

  [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]

  What's the proper syntax to pass parameters to stored procedures in Oracle?

  THANK YOU

  Alex S

• August 14, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Ken McKelvey

  SSCoach

  Points: 18859

  More actions

  August 14, 2006 at 8:18 am

  #654877

  I have never called an Oracle SP from T-SQL but you could try passing @status as an output parameter.

  eg exec DEV..SYSADM.update_procedure @Username,@status OUTPUT

   

• AlexSQLForums

  SSChampion

  Points: 14277

  More actions

  August 18, 2006 at 7:16 am

  #655626

  I tried OUTPUT it din't work.

  I used ODBC driver for ORACLE and was able to solve the problem by rewriting the procedure as update statements to oracle and then use @@rowcount to find out if there was an update.

   

  Thanks

   

  Alex S