I am trying to write a SQL task that will execute a stored procedure on Oracle that returns a number. I am then trying to put that number in the success email notification. I set up the task to run the stored procedure and created a parameter. I named it the same name as the parameter in the stored proc. I gave the value field a value of 0 (What is supposed to go in this field?) and said it was Numeric and that it was an output parameter. When I run the task I get the error.

error in task sql: string[0]: the size property has an invalid size of 0


Any help would be appreciated.

Can I output a resultset to the email? That is my goal, but I am just trying this to see if it works.

Were you able to run Task this succesful the first time (before the output parameter)?

---

I was able to run a task that was just a sql query (not stored procedure) that just selected a count from a table and that executed successfully, but I couldn't not figure out how to get the count from that query to the email notification using the VC variables.

Henrik,
Something I just tried. I just switch back to inline sql and put in a select count. Then I went to the message body of my email notification and clicked on variables. I went into VC Variables/Jobs/<My Task Name>/Tasks/<My Task Name> and when I click on the Output variable it shows the count that I am looking for. So, I drag this variable to the message body of my email, but when the job finishes, the email body is blank.

Robert

Hmm..not sure what is happening but please add an Email Task instead and see if you get the same problem.

---

That did the trick. I just added an Email Task and then put the output of the query to the email message and it worked. I was even able to output a resultset from the query.

Thanks for the help.

Robert

Henrik,
I am back to trying to execute a stored procedure with an output parameter and then using that output parameter as a variable in another task. I saw another post that talked about not using the output parameter that VC would just put the variable in the output of the task, but when I don't put the output parameter the task fails and tells me that the stored proc expects the parameter. But when I use the parameter, the value of the output of the task is blank. I can't use inline sql, I need to execute a stored procedure.

Right now, only query output can be captured. Not a specific output parameter.

Henrik,
I am back to trying to execute a stored procedure with an output parameter and then using that output parameter as a variable in another task. I saw another post that talked about not using the output parameter that VC would just put the variable in the output of the task, but when I don't put the output parameter the task fails and tells me that the stored proc expects the parameter. But when I use the parameter, the value of the output of the task is blank. I can't use inline sql, I need to execute a stored procedure.

---

I am interested in this functionality, too. It seems that a user-defined SQL function (and I assume a stored procedure, too?) that returns a value doesn't return it to standard output, because it's not getting captured by VC (no errors, though). I tried setting the Return variable to a VisualCron variable, (the standard output one, in fact), but that didn't work, either.

I just tried it with a user-defined function because that's a DB object I happened to have handy that wouldn't screw anything up by executing; I'm actually interested in it for a stored procedure that would have some effects in the database and return a value as well. I'm primarily interested in teh Returned value, not an Output variable. I would have thought the Returned value would go to standard output. Oh, well. In the meanwhile, I'm just writing my SQL query that I need the output from as a text query.

Rebeccah