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
  • I found the fix,

    In SSIS I used Execute SQL TASK.

    1. Ensure that the RESULTSET is set to  NONE

    2. In the SQL STATEMENT call your Oracle stored proc like the below 

    DECLARE 
     Param_INPUT VARCHAR2(30);
      Param_output NUMBER;


    BEGIN 
       Param_INPUT:= ?;


     CALL_MY_SProc_Oracle ( Param_INPUT, Param_output);

     ? := Param_output  ;



      COMMIT; 
    END; 

    3) in the Parameter Mapping section, Map the SSIS input variable of your choice to the Parameter "0" with Direction "input" so that you can pass the value of your SSIS variable to the Param_input

    Then Map the SSIS output variable of your choice to the Parameter "1" with Direction "Output" so that you can pass the value of the Oracle output parameter (Param_output) into the SSIS variable

    Thanks

    Mezue

    Saturday, November 26, 2016 8:27 AM