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
