Getting error while executing Oracle procedure with output parameter from Sql-Server

I had this question after viewing How to call an Oracle Stored Procedure within SQL Server ?.

This is the Oracle Procedure:

CREATE OR REPLACE PROCEDURE PPC.PRUEBA_CHAR (    OUT_SALIDA1 OUT VARCHAR2    )
IS  begin  
OUT_SALIDA1:= 'A';
EXCEPTION
        WHEN OTHERS THEN NULL;
END;

When I try to run it from Sql-Server with this:

      DECLARE @DATA2 VARCHAR(3)
      SET @DATA2='xx'
      EXECUTE ('BEGIN PPC.PRUEBA_CHAR (?); END;', @DATA2 OUTPUT) AT [GETESTING]  
      SELECT @DATA2

I am getting this error:

(1 row affected)
Msg 7215, Level 17, State 1, Line 3
Could not execute statement on remote server 'GETESTING'.
lazarioAsked:
Who is Participating?
 
lazarioConnect With a Mentor Author Commented:
I found the solution myself, comparing between servers that works and server that doesn't I realized that it worked ok in server or desktops that has Oracle 12 drivers installed instead of 11. After installing new driver I was able to run the code from Windows 10 with SSMS or Window Server and SSMS.
0
 
Pawan KumarDatabase ExpertCommented:
Is this GETESTING? your linked server? Try below also.

DECLARE @DATA2 VARCHAR(3)
SET @DATA2='xx'
EXEC ('BEGIN PPC.PRUEBA_CHAR (?); END;', @DATA2 OUTPUT) AT [GETESTING]  
SELECT @DATA2

Open in new window

0
 
lazarioAuthor Commented:
Yes, the linked server is GETESTING, I tried your suggestion but got the same error. I forgot to say that same T-SQL runs ok from another Sql-Servers we have here. From some it works from other doesn't.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Pawan KumarDatabase ExpertCommented:
Please see if this works. I m passing blank string for time being.
I think it will work with integers and numeric values not for STRINGS. Am i correct ?
Looks like the parameter we are passing are without quotes and that is creating the issue.

--

DECLARE @DATA2 varchar(50) 
SET @DATA2 = '' 
EXECUTE (' BEGIN PPC.PRUEBA_CHAR ''''; END;',@DATA2 OUTPUT) AT [b][GETESTING][/b]
SELECT @DATA2
--

Open in new window


and then try this with parameter.

--

DECLARE @DATA2 varchar(50) 
SET @DATA2 = '' 
EXECUTE (' BEGIN PPC.PRUEBA_CHAR ' + '''' + '(?)' + '''' + '; END;',@DATA2 OUTPUT) AT [b][GETESTING][/b]
SELECT @DATA2
--

Open in new window

0
 
lazarioAuthor Commented:
Pawan Kumar, thank you for your suggestions and concerbs but as mentioned the code's sintaxis is ok because same T-SQL is running ok in other servers.
0
 
Pawan KumarDatabase ExpertCommented:
great. glad you got the solution.
0
 
lazarioAuthor Commented:
Thank you for the feedback.
0
 
Pawan KumarDatabase ExpertCommented:
Solved by Author himself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.