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:
CREATE OR REPLACE TYPE myTableType2 AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE FUNCTION str2tbl2( p_str in varchar2 )
l_str LONG DEFAULT p_str || ',';
l_data myTableType2 := myTabletype2();
l_n := INSTR( l_str, ',' );
EXIT WHEN (NVL(l_n,0) = 0);
l_data( l_data.count ) := LTRIM(RTRIM(SUBSTR(l_str,1,l_n-1)));
l_str := SUBSTR( l_str, l_n+1 );
Now I have a stored procedure as follows
CREATE OR REPLACE PROCEDURE
PROCEDURE TestReport SNumber IN VARCHAR DEFAULT 'ALL',
CSta IN VARCHAR DEFAULT 'ALL',
BClass IN VARCHAR DEFAULT 'ALL',
results_cursor IN OUT CURSOR_TYPE)
OPEN results_cursor FOR
left outer join
a.C_id_ = b.c_id_
(nvl(SNumber, 'ALL') = 'ALL' or to_char(a.S_Numv_) in (Select * from table(str2tbl2(SNumber))))
(nvl(CSta, 'ALL') = 'ALL' or to_char(a.C_Sta_ ) in (Select * from table(str2tbl2(CSta))))
(nvl(BClass, 'ALL') = 'ALL' or to_char(a.B_Class_ ) in (Select * from table(str2tbl2(BClass ))))
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.
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)
(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
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.
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.