Forum Stats

  • 3,770,340 Users
  • 2,253,097 Discussions
  • 7,875,408 Comments

Discussions

Problems with StoredProcedures that use INSERT/DELETE statements

596592
596592 Member Posts: 1
edited Nov 2, 2007 4:57PM in Hyperion Query and Reporting
Hello



I am using Hyperion Intelligence explorer 8.5, and the database source is a MS SQL Server 2000 source connected via ODBC.



I have this problem: when I use, in a Query section, a stored procedure that (in its code) uses only SELECT statements I get the result of the query in the Results section, but when I use a storedprocedure that does some work (and executes INSERT or DELETE or other SQL statements) and ends executing a SELECT statement in order to return data to the caller Hyperion hangs.

I mean: first I select the stored procedure (Query\StoredProcedure... menù, than I start it using the Process command in Hyperion.

If the storedproc contains only SELECT statements I get the results, but if it contains INSERT or DELETE (and the last statement is a SELECT) Hyperion does not return any data and if I try to repeat the Process command I get an error that tells "the connection is busy with results from another hstmt".

Before you ask me if the stored procedure works correctly, I can confirm this because the storedproc was tested and returns the correct data if used with the db manager application.

Any suggestions? Did you ever use (successfully )storedprocedures that process data (by INSERT or DELETE statements) and then return the result with a SELECT statement?

Thank you for your help

Comments

  • 597495
    597495 Member Posts: 1
    Hi,

    the INSERT / DELETE statements return information on the number of rows affected as they are executed. This confuses Hyperion no end. To fix, place the following statement at the top of your Stored Proc: SET NOCOUNT ON.

    This statement will suppress the rows affected information.

    Note that, because you execute this statement within the procedure, it applies only within that procedure and does not affect the global setting.

    Also note that Hyperion will not be supporting the use of Stored Procedures in future releases (there is a bug that prevents reports based on Stored Procedures from being schedueld for automatic execution, to fix the bug they are removing this capability).

    Have fun,
    Chris.
  • 429248
    429248 Member Posts: 2
    In regards to your last statement....I am currently in the process of estimating some Hyperion work that was to include stored procedures to handle some tricky conditional logic that would be difficult to handle within Hyperion. Can you provide more details on Hyperion's plans to drop support for stored procs in future releases? More specifically, what does "drop support" mean? Does is mean any existing Hyperion reports that use them will no longer work?

    Thanks,
    Dave
  • 603806
    603806 Member Posts: 2
    The issue with the INSERT/DELETE statements is resolved in a service pack to 8.5.

    There is NO truth to the statement that stored procedure support is being dropped. I have no idea where that information originated, but it is wrong.
  • 552406
    552406 Member Posts: 194
    Hi Chris,

    Could you please tell us in which version of IR Hyperion is not going to support Stored Procedures....


    Regards,

    Manmohan
  • 603806
    603806 Member Posts: 2
    Hi Chris,

    Could you please tell us in which version of IR
    Hyperion is not going to support Stored
    Procedures....


    Regards,

    Manmohan
    Manmohan,
    Did you even read what I just wrote? This is NOT happening. Stroed Procedures are an important part of the Intelligence Product, Oracle is continuing to enhance and support this functionality. I work for Oracle, I worked for Hyperion, and for Brio before that, so believe me when I tell you this.

    Chris (whoever he is) is gving out incorrect information in this regard. His suggestion of the workaround for the original issue is completely accurate, but as I added, it was in issue that has been corrected as of 8.5 Service Pack 2, so even the workaround is not required as long as you've upgraded. (Note that some versions of 9.x also will require the workaround due to the timing of the releases overlapping.)

    Thanks.
    Larry Johnson
  • 552406
    552406 Member Posts: 194
    Larry Thanks a lot for the information. ..



    Regards,

    Manmohan
  • 606658
    606658 Member Posts: 7
    hello everyone out there. I need some help I create a select stored procedure and would like to run it through Hyperion intelligence with some input parameters. The procedure is not working. it is throwing an error when I execute it through Hyperion. Also how do I put multiple values like an array sort of things in a where clause like this where name in('test', 'test1). I place the stored procedure below any help will be greatly appreciated.

    CREATE OR REPLACE PROCEDURE emp_by_dept (
    p_deptno ert_dept_t.dept%TYPE
    )
    IS
    emp_refcur SYS_REFCURSOR;
    v_dept ert_dept_t.dept%TYPE;
    v_deptname ert_dept_t.deptname%TYPE;
    BEGIN
    OPEN emp_refcur FOR SELECT dept, deptname FROM ert.ert_dept_t WHERE dept = p_deptno;
    DBMS_OUTPUT.PUT_LINE('dept deptname');
    DBMS_OUTPUT.PUT_LINE('----- -------');
    LOOP
    FETCH emp_refcur INTO v_dept, v_deptname;
    EXIT WHEN emp_refcur%NOTFOUND;

    END LOOP;
    CLOSE emp_refcur;
    END;
This discussion has been closed.