stored procedure, cusor help

[sam]

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 ReturnCursorTyp e IS REF CURSOR;
END J1R_TYPES;


CREATE OR REPLACE PROCEDURE custom_report(
STARTDATE_STR_I N IN VARCHAR2 DEFAULT '01/01/2000',
STOPDATE_STR_IN IN VARCHAR2 DEFAULT '01/01/2000',
RC1 OUT J1R_TYPES.Retur nCursorType)

AS

startdate DATE;
stopdate DATE;
startdate_str VARCHAR2(10);
stopdate_str VARCHAR2(10);
SQLtext VARCHAR2(2000);


BEGIN

startdate_str := STARTDATE_STR_I N;
stopdate_str := STOPDATE_STR_IN ;

startdate := TO_DATE(startda te_str, 'mm/dd/yyyy');
stopdate := TO_DATE(stopdat e_str, 'mm/dd/yyyy');

SQLtext := 'select max(query_origi nal) as query, count(q.query_p k) 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.

Sam

[sybrandb@yahoo.com]

Re: stored procedure, cusor help

svs1219@hotmail .com (sam) wrote in message news:<cefb2cd0. 0310021251.27a0 9abf@posting.go ogle.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 ReturnCursorTyp e IS REF CURSOR;
END J1R_TYPES;
>
>
CREATE OR REPLACE PROCEDURE custom_report(
STARTDATE_STR_I N IN VARCHAR2 DEFAULT '01/01/2000',
STOPDATE_STR_IN IN VARCHAR2 DEFAULT '01/01/2000',
RC1 OUT J1R_TYPES.Retur nCursorType)
>
AS
>
startdate DATE;
stopdate DATE;
startdate_str VARCHAR2(10);
stopdate_str VARCHAR2(10);
SQLtext VARCHAR2(2000);
>
>
BEGIN
>
startdate_str := STARTDATE_STR_I N;
stopdate_str := STOPDATE_STR_IN ;
>
startdate := TO_DATE(startda te_str, 'mm/dd/yyyy');
stopdate := TO_DATE(stopdat e_str, 'mm/dd/yyyy');
>
SQLtext := 'select max(query_origi nal) as query, count(q.query_p k) 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