none
How to call Oracle Stored Procedure which has an output parameter from SSIS?

    Question

  • I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

     

        PROCEDURE Interface_Begin

        (p_from_dttm       OUT varchar2,

         p_error_code            OUT number,

         p_error_text       OUT            varchar2,

         p_proc_name        OUT varchar2);  

     

    Friday, June 15, 2007 7:05 PM

Answers

  • Try doing this from a Execute SQL task, not a Source component. Source components expect a recordset, not an output parameter.
    Thursday, June 21, 2007 12:38 AM
    Moderator

All replies

  • Could you please be more specific? I tried the following:

     

    1) The stored proc spec is as follows:

     

        Procedure testing(myDate OUT varchar2);

     

    2) Created a Data Flow Task.

     

    3) Created 2 variables called myStoredProc & myDate at the package level.

     

    4) In the value for myDate variable i set it to myDate.

     

    5) In the properties for myStoredProc variable i changed the EvaluateAsExpression property to True.

     

    6) In the expression for myStoredProc variable i have the following:

     

        "declare myDate varchar2(50); begin sa.testing(" + @[User::myDate]  + "); end;"

    7) Now inside my Data Flow Task i have a Ole Db DataSource connection set to Native OLE DB\Microsoft Ole Db 

        Provider  for Oracle.

     

    8) Data Access Mode set to SQL Command from Variable.

     

    9) The value of the Variable Name is User::myStoredProc.

     

    10) Now when i hit preview i get the following error Message.

          

           No disconnected record set is available for the specified SQL statement.

                          I am not sure what's wrong here. Could someone help me??????????

     

     

     

    Monday, June 18, 2007 5:27 PM
  • Anyone?
    Wednesday, June 20, 2007 7:25 PM
  • Try doing this from a Execute SQL task, not a Source component. Source components expect a recordset, not an output parameter.
    Thursday, June 21, 2007 12:38 AM
    Moderator
  • Hi there,

     

    As Jwelch said use Execute SQL Task and select Single Row in Recordset option.

    I think this will iron out your issue.

     

    Please specify if this does not work.

     

    Thanks

    Thursday, June 21, 2007 6:35 AM
  • Thanks guys. Now i am able to execute it successfully. But i am getting some junk characters in the Output parameters. Here is the stored proc definition:

     

    create or replace procedure testing(myDate OUT varchar2)
    IS
    BEGIN
    myDate :=
    'hey';
    return;
    END;

     

    This should return 'hey' but i am getting this:

    )

     

    Any ideas?

    Friday, June 22, 2007 2:07 PM
  • I am experiencing the same problem as Sun Moon.


    My Oracle procedure appears to run successfully in SSIS as an "Execute SQL Task" but when I check the values of the variables set to the procedure's outputs, I see junk characters.

    e.g. I expected to see User::ProductType set to:

    S

    but instead it has been set to :

    {Sh—}

    Does anyone have a solution?

    Thanks in advance.

    Wednesday, June 24, 2009 6:46 AM
  • Did anyone resolve this, or has it just been left to die? I have the exact same issue as Sun Moon and mick4 and I can't find a response anywhere!
    Tuesday, August 25, 2009 3:26 PM
  • Hi Sun Moon,

    How do you get the value OUT parameter from Oracle?

    In Execute SQL Task, I tried to set:

    Resultset = Single row, Result Name = 1, Variable Name = User::output (String datatype)

    But it returns the following error:

        Error: No result rowset is associated with execution of this query.
        [Execute SQL Task] Error: An error occurred while assigning a value to variable "output"

    Thanks!
    Wednesday, November 03, 2010 3:05 AM