Re: stored procedure, cusor help

svs1219_at_hotmail.com (sam) wrote in message news:<cefb2cd0.0310021251.27a09abf_at_posting.google.com>...
> I'm trying to develop a stored procedure for a Crystal reports.
> Here's the stored procedure. The SP uses the following package for
> the REF CURSOR.
>
> CREATE OR REPLACE PACKAGE J1R_TYPES AS
> TYPE ReturnCursorType IS REF CURSOR;
> END J1R_TYPES;
>
>
> CREATE OR REPLACE PROCEDURE custom_report(
> STARTDATE_STR_IN IN VARCHAR2 DEFAULT '01/01/2000',
> STOPDATE_STR_IN IN VARCHAR2 DEFAULT '01/01/2000',
> RC1 OUT J1R_TYPES.ReturnCursorType)
>
> AS
>
> startdate DATE;
> stopdate DATE;
> startdate_str VARCHAR2(10);
> stopdate_str VARCHAR2(10);
> SQLtext VARCHAR2(2000);
>
>
> BEGIN
>
> startdate_str := STARTDATE_STR_IN;
> stopdate_str := STOPDATE_STR_IN;
>
> startdate := TO_DATE(startdate_str, 'mm/dd/yyyy');
> stopdate := TO_DATE(stopdate_str, 'mm/dd/yyyy');
>
> SQLtext := 'select max(query_original) as query, count(q.query_pk) as
> query_count, ';
> SQLtext := SQLtext || 'max(url) as url, count(url) as url_count from
> query q, match m, pick p, document d ';
> SQLtext := SQLtext || 'where q.query_pk = m.query_pk and m.match_pk =
> p.match_pk ';
> SQLtext := SQLtext || 'and p.document_pk = d.document_pk and
> q.query_pk <> 0 and q.create_date >= startdate ';
> SQLtext := SQLtext || 'and q.create_date <= stopdate group by
> query_original, url ';
>
> OPEN RC1 FOR SQLtext;
>
> END custom_report;
>
>
> Whenever I try to run the report in Crystal, I keep getting an invalid
> column commance at the 'OPEN RC1 FOR SQLTEXT' line. Can anyone tell
> me how I can run the stored procedure in SqlPlus? I executed the SQL
> command and I'm sure the column names are correct. Any help is
> greatly appreciated. Thanks.
>

variable cur refcursor;
begin
your procedure(p1,p2, :cur);
end;
/
print :cur

Sybrand Bakker
Senior Oracle DBA

> Sam
Received on Fri Oct 03 2003 - 12:51:41 CEST