Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    23

    Unanswered: Crystal reports and Oracle result sets

    I want to call oracle stored procedures using crystal reports and would appreciate any feedback on which of the various methods of returning result sets are best practice.

    Having found a few methods the most straightforward seemed to be

    CREATE OR REPLACE PROCEDURE TA_DEV.Test_Procedure (
    Test_Cursor IN OUT Test_Package.Test_Type)
    )
    AS
    BEGIN
    OPEN Test_Cursor FOR
    SELECT *
    FROM Test_Table;
    END Test_Procedure;
    /

    In Sybase I can use select * to set the result set. Is there anything similar in Oracle?

    If I go with the above method when does the cursor close if it is not explicitly closed at the end of the procedure

    many thanks

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    22

    Result set from Oracle

    Create or replace Function TESTSP RETURN SYS_REFCURSOR
    IS
    p_cur_user SYS_REFCURSOR;
    begin
    open p_cur_user for
    SELECT * FROM USERACCOUNTS;
    return p_cur_user;

    end;

  3. #3
    Join Date
    Mar 2004
    Posts
    23
    thanks. Is there any downside to the fact that the cursor is not closed? Does it close when the calling crystal report is closed or when the procedure has finished returning the result set?

    thanks again

  4. #4
    Join Date
    Feb 2004
    Location
    India
    Posts
    22
    No there is no downside of the cursor. The oracle internally handles this.
    Regards,
    Jigar Bhavsar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •