Problème avec procédure stockée appelée via dblink [Résolu]

[SelectEtoile]

Bonjour,

J'ai un problème assez étonnant avec une procédure stockée SQL-Server appelée depuis oracle via un dblink.

Les données du problème :
Je crée une table TEST :

Code :

1
2
CREATE TABLE TEST (TEXTE nvarchar(50))
INSERT INTO TEST (TEXTE) VALUES ('ABCD')

Je crée une procédure qui me renvoie la table TEST

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE PROCEDURE [dbo].[TESTPROC]
	@aParam Int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    IF @aParam = 1 
      -- si param=1 renvoie la table TEST
  	  SELECT TEXTE FROM dbo.TEST
    else
      -- Sinon renvoie un code d'erreur -1
  	  SELECT '-1' AS TEXTE
END

Tout ça fonctionne bien sûr très bien sous SQL-Server

Là où c'est moins drôle c'est quand j'appelle la procédure depuis Oracle via un dblink :
Si j'appelle la procédure en lui passant aParam=0 elle me renvoie bien "-1" comme prévu
Si je l'appelle en lui passant 1 j'ai le message d'erreur :

[Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver]
Erreur sur la ligne[Microsoft][ODBC SQL Server Driver]
Troncation à droite de la chaîne de données

Je modifie la procédure stockée en inversant simplement le IF:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ALTER PROCEDURE [dbo].[TESTPROC]
	@aParam Int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    IF @aParam != 1 
      -- Sinon renvoie un code d'erreur -1
  	  SELECT '-1' AS TEXTE
    else
      -- si param=1 renvoie la table TEST
  	  SELECT TEXTE FROM dbo.TEST
END

Là plus de problème, ça fonctionne dans tous les cas.

Sinon j'ai essayé ça qui fonctionne également dans tous les cas :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ALTER PROCEDURE [dbo].[TESTPROC]
	@aParam Int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    IF @aParam = 1 
      -- si param=1 renvoie la table TEST
  	  SELECT TEXTE FROM dbo.TEST
    else
      -- Sinon renvoie un code d'erreur -1
  	  SELECT '-1' AS TEXTE
  -- Code jamais éxécuté mais qui évite le message d'erreur
  IF 1=2 SELECT 'xxxxxxxxxxxxxxxxxxxx'  
END

Si si je vous assure je ne fume pas et d'ailleurs je n'ai pas de moquette dans le bureau.
Il faut que le nombre de caractères dans le "if 1=2 select 'xxxxxxxxxxxxxx'" soit plus grand que la plus grande chaine de la table TEST.

Quelqu'un peut m'expliquer le fonctionnement de SQL-Server ? J'ai l'impression que le code est interpreté au runtime et qu'il se base sur le dernier select pour déterminer la taille du buffer (d'où le message d'erreur "troncation à droite") ?

Il est difficile de livrer un code "sérieux" en expliquant qu'il ne faut surtout pas inverser le sens des "if else" ou en ajoutant des "if 1=2" !

Merci.

Pour info j'ai mis le code de la procédure oracle :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE OR REPLACE TYPE TTEST  IS OBJECT (STR1 VARCHAR2(50));
/
CREATE OR REPLACE TYPE TEST_SET AS TABLE OF TTEST;
/
CREATE OR REPLACE FUNCTION TEST (aParam IN Integer) RETURN TEST_SET pipelined AS
  lRec  TTEST;           -- Record resultat
  c Integer;             -- Handle de curseur  
  lReqSql VarChar2(200); -- Requète SqlServer
  i Integer;
begin
  lRec := TTEST('');
  -- Crée un curseur pour requète dans SqlServer via HS 
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@DBLINK;
  -- Construit la requète
  lReqSQl:='EXEC TESTPROC '||to_char(aParam);
  -- execution
  DBMS_HS_PASSTHROUGH.PARSE@DBLINK(c, lreqSql);
  --recuperation des données
  loop
    -- Lecture d'un enregistrement
    i:=DBMS_HS_PASSTHROUGH.FETCH_ROW@DBLINK(c, False);
    exit when I=0; -- EOF si i=0
    DBMS_HS_PASSTHROUGH.GET_VALUE@DBLINK(c,1,lRec.Str1);
    pipe row (lRec);
  end loop;
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@DBLINK(c);
end;
/
-- appel de la fonction
SELECT * FROM TABLE(Test(1));

[hmira]

Je te suggère, pour éviter (ou limiter) au maximum les problèmes et erreurs, de procéder ainsi :

1 – Respecter les correspondances (ou mappage) des types Oracle /Sql Server

Code :

1
2
3
Oracle             SQL Server 
VARCHAR2([1-4000])    VARCHAR([1-4000] 
NVARCHAR2([1-2000]) 	NVARCHAR([1-2000])

Or comme sous Oracle, la propriété STR1 est de type VARCHAR2(50), le type correspondant sous SQL Server est VARCHAR(50) (et non pas NVARCHAR(50))?

2 – De faire en sorte que, dans la procédure SQL Server dbo.TESTPROC le type de la colonne TEXTE soit toujours le même et ce quel que soit le test effectué sur le paramètre @aParam. Pour cela, il faut « caster » la constante -1 en varchar(50)
Conclusion je te propose de tester la solution ci-dessous :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE TEST (TEXTE VARCHAR(50))    -- et non pas nvarchar(50) 
 
INSERT INTO TEST (TEXTE) VALUES ('ABCD')
 
CREATE PROCEDURE [dbo].[TESTPROC]
	@aParam Int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    IF @aParam = 1 
      -- si param=1 renvoie la table TEST
  	  SELECT CAST(TEXTE AS VARCHAR(50)) FROM dbo.TEST    -- (1) 
    ELSE
      -- Sinon renvoie un code d'erreur -1
  	  SELECT CAST('-1' AS VARCHAR(50)) AS TEXTE
END

-- (1) si TEXTE est de type VARCHAR(50), un SELECT TEXTE FROM dbo.TEST aurait suffit sans le CAST, mais si pour une raison ou une autre le type du champ TEXTE doit être différent, il vaudrait mieux garder le CAST.

A+

[SelectEtoile]

Bonjour hmira,

Merci pour ta réponse.
J'ai essayé avec le

Code :

SELECT CAST('-1' AS VARCHAR(50)) AS TEXTE

ça fonctionne bien, plus de problème.

Pour info j'ai essayé en mixant les VarChar et NVarChar ça marche dans tous les cas.

Encore merci.