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;
精彩评论