Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: Cannot connect to Oracle store procedure from Crystal Report

    Hello all,

    I got an Oracle store procedure as bellowed:
    ================================================== ========
    CREATE OR REPLACE PROCEDURE SP_RETRIEVE_SCHOOL_INFO
    (P_YEAR IN VARCHAR2,
    P_CLASS IN VARCHAR2,
    RS IN OUT SYS_REFCURSOR)
    IS

    P_RET_STATUS VARCHAR2(5);

    BEGIN
    OPEN RS FOR
    SELECT NAME, (SELECT ADDRESS WHERE SCHOOL_RECORD.ADDRESS = ADDRESS FROM BOOK_ADDRESS) AS VERIFIED_ADDRESS, AGE
    FROM SCHOOL_RECORD
    WHERE YEAR_NO = P_YEAR AND CLASS_CD = P_CLASS;

    P_RET_STATUS:=SQLCODE;

    EXCEPTION

    WHEN NO_DATA_FOUND THEN
    P_RET_STATUS:=SQLCODE;
    WHEN OTHERS THEN
    P_RET_STATUS:=SQLCODE;

    END SP_RETRIEVE_SCHOOL_INFO;
    ================================================== ======

    This store procedure compiled without errors in TOAD. I am developing a crystal report that using Database Expert to connect to this store procedure, but I could not due to the following error:

    ORA-06550: line x, column y:
    PLS-00306: wrong number of types of arguments in call to 'SP_RETRIEVE_SCHOOL_INFO'
    PL/SQL: statement ignored

    I am not familiar with SYS_REFCURSOR in Oracle SP, please help me to solve my issue. Anything need to change on my current SP??? Thanks in advance.
    Last edited by avt2k6; 12-18-11 at 01:01. Reason: clarifying

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try the following

    Code:
    CREATE OR REPLACE PROCEDURE SP_RETRIEVE_SCHOOL_INFO
                                     (P_YEAR IN VARCHAR2,
                                      P_CLASS IN VARCHAR2,
                                      RS OUT SYS_REFCURSOR)
    IS
    
    P_RET_STATUS VARCHAR2(5);
    
    BEGIN
       OPEN RS FOR
              SELECT  NAME, b.ADDRESS VERIFIED_ADDRESS, AGE
             FROM BOOK_ADDRESS b,SCHOOL_RECORD  a
             WHERE YEAR_NO = P_YEAR AND CLASS_CD = P_CLASS
    and a.address = b.address(+);
             
    END SP_RETRIEVE_SCHOOL_INFO;
    Last edited by beilstwh; 12-16-11 at 10:35.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    remove/delete/eliminate whole, complete & entire EXCEPTION handler code.
    It is a bad bug waiting to bite you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Agreed, and the sys_refcursor should only be out
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What do you need a stored procedure for? Why don't you simply use a SELECT statement in your report?

    If I read it correctly, it could have been rewritten as
    Code:
    select 
      r.name, 
      a.address,
      r.age
    from school_record r,
         book_address a
    where r.address = a.address
      and r.year_no = :p_year
      and r.class_cd = :p_class;
    Furthermore, as I don't see any reason to join these two tables, the final result might have been
    Code:
    select
      r.name,
      r.address,
      r.age
    from school_record r
    where r.year_no = :p_year
      and r.class_cd = :p_class;
    :P_YEAR and :P_CLASS are report parameters (I don't know how you qualify them in Crystal Reports - is it a colon sign, an ampersand & or something different, but I guess you know it).

  6. #6
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Hi all,

    Thanks for your helps, but I got the same error to call store procedure in the following code when I debugged in VS 2008.

    Any other idea, why it generate the error. Please see the link when I searched on google for more suggestion on this kind of error.

    10 Steps to access Oracle stored procedures from Crystal Reports at CodeKicks.com - Focus on Microsoft Technologies

    Oracle Stored Procedures and Crystal Reports


    Thanks for any further help.
    Last edited by avt2k6; 12-21-11 at 20:07.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Hello all,

    After your suggestions, I tried but it produced same error. I like to add more information that I already did, so you know what I need help for your Oracle expertise. I figured out my error came from SP when filling the dataset using parameter store procedure. Therefore, I eliminated the crystal report issue. In a brief, I could not fill dataset that call from store procedure. I really appreciate your inputs to solve my issue rather than what you think but not experience. For example, why I need SP rather than SQL select? I don't know whether or not you have been working on Crystal Report before, but I think SP is the right choice. Anyways, I re-checked my code to call stored procedure from VB.NET and found out that I misspelled the parameter name. Nothing wrong with SP at all after I found why. Oracle above error that never point out where the error was. That why it is hard to find. My experience after this error, check to make sure the parameter match between SP and code behind. Hope to help anyone else to run into this error.
    Last edited by avt2k6; 12-21-11 at 20:13.

Posting Permissions

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