开发者

SSIS Execute SQL Task throwing ORA-01008: not all variables bound error

I have an Execute SQL task (SQL 2008) where I'm using two SSIS variables to interact with an Oracle database:

DECLARE ParamTest number; TempOutputRun varchar(255);
ParamTest := ?;
TempOutputRun := ?;

BEGIN
IF ParamTest = 0 THEN

   SELECT CAST(OUTPUT_RUN_ID AS VARCHAR(15)) AS OUTPUT_RUN_ID FROM GL_EXTRACT_STATUS WHERE STATUS='NEW' ORDER BY OUTPUT_RUN_ID ASC;

ELSE

   SELECT TempOutputRun AS OUTPUT_RUN_ID FROM DUAL; 

END IF
;
END;

I'm getting the error ORA-01008 on execution and I'm not sure why. Both variables have values, and are set to the correct datatypes with Parameter Names in mapping of 0 and 1 respectively. The ELSE part of this statement should be the one tripped by the current conditions I'm testing under.

Anyone have any idea开发者_如何学运维s? I'm stumped (and quite frustrated, to be perfectly honest).

Thanks!

Valkyrie


Without knowing much about SSIS, it seems to me you need to select...into in your code:

SELECT CAST(OUTPUT_RUN_ID AS VARCHAR(15)) AS OUTPUT_RUN_ID
  INTO some_var
  FROM ...
 WHERE ...

and

SELECT TempOutputRun AS output_run_id
  INTO another_var
  FROM dual;

I don't quite get why the ora-01008. Running the select from dual in a PL/SQL block as written gives me an ora-06550.


You have to use the following syntax

BEGIN
DECLARE
.....
BEGIN

.....

END;
END;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜