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 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.

Sam

[sybrandb]

sv*****@hotmail.com (sam) wrote in message news:<ce**************************@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