stored procedure, cusor help
Date: 2 Oct 2003 13:51:57 -0700
Message-ID: <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
AS
BEGIN
startdate_str := STARTDATE_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, ';
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)
startdate DATE;
stopdate DATE;
startdate_str VARCHAR2(10);
stopdate_str VARCHAR2(10);
SQLtext VARCHAR2(2000);
stopdate_str := STOPDATE_STR_IN;
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.
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 Received on Thu Oct 02 2003 - 22:51:57 CEST