Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Crystal Reports, passing varchar to stored procedure
kurt behn wrote:
> 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
Change your procedure parameter to IN OUT even if nothing is coming back out. That often solves the problem.
BTW: If your procedure is named 'cursor' rename it to something that is not a reserved word.
-- Daniel Morgan http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp damorgan_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Thu May 08 2003 - 16:32:40 CDT