October 4, 2007 at 4:06 am
Hi,
I am trying to get the value of user defined variable in a variable declared in sql editor and then pass it to the Stored procedure as a parameter(the databse is Oracle 10g).I am using ADO.NEt as connection manager and Microsoft Oledb for Oracle as Connection.
The query that I am trying is
declare identity varchar2(200);
begin
identity := (DT_WSTR,4)@[User::identity];
gmcir.SP_newTest(identity);
end;
If i provide a static value like identity :=1 then it works else its not working.
Please,help me out.
Mail me at natasha_nikky11@yahoo.co.in
October 4, 2007 at 6:24 am
Hi,
What you could do is:
In the Parameter Mapping tab click on Add and create a parameter to map with the variable you had created.
In the Execute SQL Task General Properties set the IsQueryStoredProcedure property to true. Then in the SQL Statement property type EXEC along with the parameter you created.
This should work things out. Let me know if it works
Gogula
October 4, 2007 at 7:21 pm
Looks like you're using expressions to construct the sql statement to be executed by the SQL task. That is the preferred method. It is better then trying to map parameters to your task, because you don't have to worry about what connection manager to use.
I did notice your code was not encapsulated in quotes, and that could be why it's failing. The code in the SQLStatementSource expression should be as follows. Remember, you're passing a string to the SQL task. Disclaimer: I work with SQL not Oracle, so my use of a single quote to qualify a string might be wrong, but you should get the general idea 😉
"declare identity varchar2(200);
begin
identity := '" + (DT_WSTR,4)@[User::identity] + "';
gmcir.SP_newTest(identity);
end;"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply