Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Crystal Reports, passing varchar to stored procedure

Re: Crystal Reports, passing varchar to stored procedure

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Thu, 08 May 2003 14:32:40 -0700
Message-ID: <3EBACCF8.F099ACAE@exxesolutions.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US