locked
How to call a stored procedure (Oracle) RRS feed

  • Question

  • Hello Everyone.

    I found a lot of answers on wrappers for stored procedures (because of passing parameters), return type (out_ref cursor), and so on but what I need to know is the very basics: how can I call a stored procedure in the Reporting Service using SQL?

    I tried something like SELECT packagename.function but this only gave me errors. I never used stored procedures before and I am still waiting for a book about RS but until it arrives I would like to start working already.

    So, could someone explain this to me?

    Thanks.
    Wednesday, June 18, 2008 4:24 PM

Answers

  • Here is an example of how to easily call a stored procedure using the actual stored procedure functionality:

     

    1. Create a new DataSet
    2. Select Command type to be stored procedure
    3. Query String: name of procedure

    Using SQL

    1. Create a new DataSet
    2. Select Command type to be text
    3. Query String: exec StoredProcedureName @param1, @param2 ...

    Hope this helps

    Wednesday, June 18, 2008 4:35 PM

All replies

  • Just specifying the package name and function name should work.

     

    For example:

    Code Snippet

    PackageName.Function

     

     

    Parameters will be auto-detected once you try to run it from within the query designer or when refreshing the dataset fields list.

     

    -- Robert

    Wednesday, June 18, 2008 4:31 PM
  • Here is an example of how to easily call a stored procedure using the actual stored procedure functionality:

     

    1. Create a new DataSet
    2. Select Command type to be stored procedure
    3. Query String: name of procedure

    Using SQL

    1. Create a new DataSet
    2. Select Command type to be text
    3. Query String: exec StoredProcedureName @param1, @param2 ...

    Hope this helps

    Wednesday, June 18, 2008 4:35 PM
  • Thank a lot, this was very helpful.

    But I am getting a error when I run the command: PLS-00221: 'functionname' is not a procedure or is undefined
    How can this be, since it recognizes the parameters automatically (so it can find it)?
    Thursday, June 19, 2008 9:07 AM