Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Unhappy Unanswered: Does anybody see anything wrong with this code

    Hello experts. I am pulling my hair out. I am using Crystal Report XI and oracle stored procedures and before I blame Crystal reports XI I need to make sure my oracle syntax is correct. I have a function from Tom Kylte that will deal with IN values as follows:

    Code:
    CREATE OR REPLACE TYPE myTableType2 AS TABLE OF VARCHAR2(100);
    /
    CREATE OR REPLACE FUNCTION str2tbl2( p_str in varchar2 )
    RETURN myTableType2
    AS
      l_str   LONG DEFAULT p_str || ',';
      l_n     VARCHAR2(100);
      l_data  myTableType2 := myTabletype2();
    BEGIN
      LOOP
        l_n := INSTR( l_str, ',' );
        EXIT WHEN (NVL(l_n,0) = 0);
        l_data.EXTEND;
        l_data( l_data.count ) := LTRIM(RTRIM(SUBSTR(l_str,1,l_n-1)));
        l_str := SUBSTR( l_str, l_n+1 );
      END LOOP;
      RETURN l_data;
    END;
    /
    Now I have a stored procedure as follows

    Code:
    CREATE OR REPLACE PROCEDURE
    AS
      PROCEDURE TestReport SNumber IN VARCHAR DEFAULT 'ALL',
                                      CSta IN VARCHAR DEFAULT 'ALL',
                                      BClass IN VARCHAR DEFAULT 'ALL',
                                      results_cursor IN OUT CURSOR_TYPE) 
      IS
      BEGIN
        OPEN results_cursor FOR   
    SELECT 
             a.c_id_,
             b.t_id_
          
    FROM
      TableX a
    left outer join
      TableY b
    ON
      a.C_id_ = b.c_id_
    WHERE
       (nvl(SNumber, 'ALL') = 'ALL' or to_char(a.S_Numv_) in (Select * from table(str2tbl2(SNumber))))
    AND
      (nvl(CSta, 'ALL') = 'ALL' or to_char(a.C_Sta_ ) in (Select * from table(str2tbl2(CSta))))
    AND 
      (nvl(BClass, 'ALL') = 'ALL' or to_char(a.B_Class_ ) in (Select * from table(str2tbl2(BClass ))))
     END;
    END;
    The procedure compiles fine but when I run this in crystal reports XI and Choose 1, ALL, ALL for the parameters I get jiberish(looks like non english) data. 潗歲湩⁧湯愠映硩映牯愠牥潲⁲桷捩⁨慣捯畣⁲桷湥吠畓灰牯⁴牯敤獲愠敲攠瑮牥摥琠牨畯桧琠敨圠扥匠牥楶 散椠瑮牥慦散漠⁦卅佄⹅. (No there are no other characters beside english characters in the database).

    If I choose 1, 2, 3 I get good data. I am trying to establiah a pattern but cannot. However, I did notice that if I remove the parameter CSta and corresponding code and just leave the other 2 parameters and select ALL and ALL for the parameters it works fine. Any ideas. Could you tell me if my code is fine or an alternate way to try this. Thanks experts.

    Does it have to do with the Oracle driver. I am using the native oracle server driver that crystal provides.

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    What happens if you test it outside Crystal Reports?

  3. #3
    Join Date
    Apr 2006
    Posts
    140

    Red face

    William,

    Thanks for the reply. Well if I use the first parameter for instance and comment out the others(for display purposes I am just showing you the part of the code that I changed)
    Code:
    (nvl(a.S_Numv, 'ALL') = 'ALL' or to_char(a.S_Numv_) in (Select * from table(str2tbl2('ALL'))))
    and run the procedue outside crystal reports I get ORA-01722: invalid number. So I'm not really sure how to pass the ALL into the parameter list. I think the ALL is messing the code up. However, if I temparily remove the ALL part so that it looks like this

    Code:
    to_char(a.S_Numv_) in (Select * from table(str2tbl2('1')))
    it works fine. Maybe you can tell me what is wrong with the ALL argument. Thanks William.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The reason it fails is because you doing nvl() on a numeric field but if its null your returning a string, but nvl cant return both datatypes it has to be one or the other. In this case Oracle has decided its returning a number because of the column but it cant convert 'ALL' to a number.

    The best and most efficient way of doing this is to use dynamic sql where if the particular parameter is 'ALL' then dont put the criteria in (i.e. to_char(a.S_Numv_) in (Select * from table(str2tbl2(SNumber))).
    Check out the OPEN...FOR statement in the PLSQL manual for more info. This should be much quicker than using the OR clause.

    Alan

  5. #5
    Join Date
    Apr 2006
    Posts
    140

    Thanks Alan

    Thanks Alan for the feedback. For the time being I got my syntax to work as follows.

    Code:
     NVL(TO_CHAR(a.S_Numv), 'ALL') = 'ALL' or a.S_Numv in (Select * from table(str2tbl('ALL')))
    But I will definitely look into the Open For syntax.

    As for William..... to now answer your question, the query works fine outside of Crystal Reports.

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Now how is it in Crystal Reports?

  7. #7
    Join Date
    Apr 2006
    Posts
    140
    Still doesn't work.

Posting Permissions

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