Crystal Reports, passing varchar to stored procedure

If there is a better group for this question, please feel free to tell me so.

I am trying to run a CR report off of an Oracle SP using CR 8.5 and Oracle 9i. In fact, I've been relatively successful at it thus far. But now I am trying to pass in a where clause (or order by clause, or what have you) to concatenate onto some dynamic SQL.

Something like this:

procedure(cursor,where_clause varchar2)
...

open io_cursor for
 'select rows from table ' || where_clause;
...

end;

This isn't working for me. I am getting an error that the SQL command is not properly ended. I feel like I've tried everything but some notable attempts have produced:

if i declare a varchar2 in the procedure and concatenate THAT to the sql it works peachy. however, if i set the declared varchar2 to the where_clause parameter (using TRIM and SUBSTR at times) the problems reoccur.

also if i pass an empty string, it's fine (thought of course i haven't accomplished anything).

also, it appears to run fine in SQL*Plus and from VB. It's only happening when I run from Crystal Reports. It says SQL Command is not properly ended and also throws up an error about the 'database DLL.'

Thanks for any and all help
Kurt Received on Thu May 08 2003 - 14:49:06 CDT