Problem with Crystal Reports and Oracle stored proc

From: <scarmody_at_allstate.com>
Date: Thu, 11 Jun 1998 16:27:38 GMT
Message-ID: <6lp0hq$rc9$1@nnrp1.dejanews.com>

Hi everyone,

I am having trouble with Crystal Reports going against an Oracle stored procedure. When I build a "new" report with Crystal Reports and select the stored procedure as the source it prompts me to enter the parameters (a cusor variable and two VARCHAR variables) to the procedure which is fine. When I do that it puts up the hourglass cursor for several minutes as expected but then comes back with the error message "ORA-01403 no data found". There definately is data there since I can run this same stored procedure in SQL Plus and get results back. All the procedure does is open a cusor which retrieves information from a joined select query, stores the results in working storage variables, gets more information from other sources and stores them in variables. All of these variables are then used in an "Insert" query to populate a table. This loops until all the data is read from the cursor and the table is completely populated. This cursor is closed and the one passed in as a parameter is OPENed FOR SELECT from the table that was just populated. Since this cursor is defined as IN OUT it is used to populate Crytal. Why is this happening??? If anyone out there can suggest something I would very much appreciate it. I am including the source for this problem in case someone would like to try it.

/* View which simply selects * from the table */ CREATE OR REPLACE VIEW V_PERFSEC_MTDRETN AS
SELECT * FROM DSTI.DSTFI_PERFSEC_MTD_HELD /* Package which defines the cursor variable passed to the procedure */ CREATE OR REPLACE PACKAGE mtd_perfsec_cursor_types AS TYPE mtd_cur IS REF CURSOR RETURN V_PERFSEC_MTDRETN%ROWTYPE; END mtd_perfsec_cursor_types ;

/* The infamous procedure - it looks complicated but essentially does what I described above. Keep in mind that this does work in SQL Plus. */ CREATE OR REPLACE PROCEDURE dst_perfsec_mtd_held ( mtd_cv IN OUT mtd_perfsec_cursor_types.mtd_cur, ws_curr_process_date_in IN VARCHAR2,

ws_prior_process_date_in IN VARCHAR2 ) AS ws_curr_process_date	DATE;
ws_prior_process_date DATE; ws_secid  NUMBER(10); ws_acid  NUMBER(10);
ws_end_phperiod  DATE; ws_mdlid  NUMBER(10); ws_ndid  NUMBER(10); ws_feehndl 

VARCHAR2(1); ws_calctype VARCHAR2(1); ws_phcurrency NUMBER(10); ws_end_phmrkt NUMBER; ws_phflowstot NUMBER; ws_phincearn NUMBER; ws_end_phincacc NUMBER; ws_prinretn NUMBER; ws_totretn NUMBER; ws_sxvalue VARCHAR2(30); ws_acnomajor VARCHAR2(12); ws_acnominor VARCHAR2(12);

ws_end_price  NUMBER; ws_end_unitval  NUMBER; ws_coupon  NUMBER;
ws_maturity_date  DATE; ws_short_desc  VARCHAR2(255); ws_beg_phperiod  DATE;
ws_beg_phmrkt  NUMBER; ws_beg_phincacc	NUMBER; ws_beg_price  NUMBER;
ws_beg_unitval	NUMBER; ws_count  NUMBER  := 0; ws_msg	VARCHAR2(80) := NULL;
ws_cursor_empty  EXCEPTION; currdate  DATE; priordate  DATE; CURSOR RETURN IS

 SELECT A.SECID, A.ACID, PHPERIOD, MDLID, NDID, FEEHNDL, CALCTYPE, PHCURRENCY, PHMRKT, PHFLOWSTOT, PHINCEARN, PHINCACC, PRINRETN, TOTRETN,  SXVALUE, ACNOMAJOR, ACNOMINOR, NVL(PRIPRICE,0), TOTUNIT, CURRENT_COUPON, MATURITY_DATE, SHORT_DESCRIPTION FROM FIGPS.OPP_RETURNS A,  FIGPS.PR_SECXREF B, FIGPS.PR_ACCOUNT C, FIGPS.PR_PRICE D, FIDM.ISSUE_at_DSTDWP.WORLD WHERE PHPERIOD = ws_curr_process_date AND PHRECTYPE
<> 'L' AND A.SECID = B.SMSECID AND A.ACID = C.ACID AND C.ACNOMINOR <>
' ' AND A.SECID = D.SMSECID(+) AND A.PHPERIOD = D.PRIDATE(+) AND SUBSTR(B.SXVALUE,1,9) = CUSIP; BEGIN currdate :=

TO_DATE(ws_curr_process_date_in,'mm/dd/yy'); priordate :=
TO_DATE(ws_prior_process_date_in,'mm/dd/yy'); ws_msg := 'DELETE PROCESSING
FROM DSTFI_PERFSEC_MTD_HELD'; DELETE FROM DSTI.DSTFI_PERFSEC_MTD_HELD; ws_msg
:= 'SELECT PROCESSING FROM DSTFI_SYS_DATE TABLE'; SELECT CURR_PROCESS_DATE, 
PRIOR_PROCESS_DATE  INTO ws_curr_process_date,	ws_prior_process_date FROM

DSTI.DSTFI_SYS_DATE_at_DSTDWP.WORLD; IF TRUNC(currdate) ^= TRUNC(SYSDATE) THEN ws_curr_process_date := currdate; ws_prior_process_date := priordate; END IF; ws_msg := 'OPEN PROCESSING FOR RETURN CURSOR'; OPEN RETURN; ws_msg := 'FIRST FETCH FOR RETURN CURSOR'; FETCH RETURN INTO ws_secid, ws_acid,

ws_end_phperiod,  ws_mdlid,  ws_ndid,  ws_feehndl,  ws_calctype, 
ws_phcurrency,	ws_end_phmrkt,	ws_phflowstot,	ws_phincearn, 
ws_end_phincacc,  ws_prinretn,	ws_totretn,  ws_sxvalue,  ws_acnomajor, 
ws_acnominor,  ws_end_price,  ws_end_unitval,  ws_coupon,  ws_maturity_date, 
ws_short_desc; IF RETURN%NOTFOUND THEN	RAISE ws_cursor_empty; END IF; LOOP 
BEGIN  ws_msg := 'SELECT1 PROCESSING FROM FIGPS.OPP_RETURNS';  SELECT
PHPERIOD,  PHMRKT,  PHINCACC,  TOTUNIT	INTO ws_beg_phperiod,  ws_beg_phmrkt,
 ws_beg_phincacc,  ws_beg_unitval  FROM FIGPS.OPP_RETURNS  WHERE SECID	=
ws_secid  AND  ACID  = ws_acid	AND  MDLID  = ws_mdlid	AND  NDID  = ws_ndid 

AND FEEHNDL = ws_feehndl AND CALCTYPE = ws_calctype AND PHCURRENCY = ws_phcurrency AND PHRECTYPE ^= 'L' AND PHPERIOD = ws_prior_process_date; EXCEPTION WHEN NO_DATA_FOUND THEN ws_beg_phperiod := '01-JAN-01'; END; IF ws_beg_phperiod = '01-JAN-01' THEN BEGIN ws_msg := 'SELECT2 PROCESSING FROM FIGPS.OPP_RETURNS'; SELECT A.PHPERIOD, A.PHMRKT, A.PHINCACC, A.TOTUNIT INTO ws_beg_phperiod, ws_beg_phmrkt, ws_beg_phincacc, ws_beg_unitval FROM FIGPS.OPP_RETURNS A WHERE A.SECID = ws_secid AND A.ACID = ws_acid AND A.MDLID = ws_mdlid AND A.NDID = ws_ndid AND A.FEEHNDL = ws_feehndl

AND  A.CALCTYPE  = ws_calctype	AND  A.PHCURRENCY = ws_phcurrency AND 
A.PHRECTYPE ^= 'L'  AND  A.PHPERIOD  =	(SELECT MAX(B.PHPERIOD)  FROM
FIGPS.OPP_RETURNS B  WHERE B.SECID  = ws_secid	AND  B.ACID  = ws_acid	AND 
B.MDLID  = ws_mdlid  AND  B.NDID  = ws_ndid  AND  B.FEEHNDL  = ws_feehndl 
AND  B.CALCTYPE  = ws_calctype	AND  B.PHCURRENCY = ws_phcurrency AND 

B.PHRECTYPE ^= 'L' AND B.PHPERIOD < ws_end_phperiod); EXCEPTION WHEN

NO_DATA_FOUND THEN  ws_beg_phperiod := '01-JAN-01';  ws_beg_phmrkt  := 0; 
ws_beg_phincacc := 0;  ws_beg_unitval  := 0;  END;  END IF;  IF
ws_beg_phperiod ^= '01-JAN-01' THEN  BEGIN  ws_msg := 'SELECT PROCESSING FROM

FIGPS.PR_PRICE'; SELECT PRIPRICE INTO ws_beg_price FROM FIGPS.PR_PRICE WHERE SMSECID = ws_secid AND PRIDATE = ws_beg_phperiod; EXCEPTION WHEN NO_DATA_FOUND THEN ws_beg_price := 0; END; ELSE ws_beg_price := 0; END IF; IF (ws_phflowstot ^= 0) OR (ws_phincearn ^= 0) OR (ws_end_phmrkt + ws_beg_phmrkt + ws_end_phincacc + ws_beg_phincacc ^= 0) THEN ws_msg := 'INSERT PROCESSING FOR DSTI.DSTFI_PERFSEC_MTD_HELD'; INSERT INTO DSTI.DSTFI_PERFSEC_MTD_HELD VALUES(ws_end_phperiod, ws_end_phmrkt,

ws_phflowstot,	ws_phincearn,  ws_end_phincacc,  ws_prinretn,  ws_totretn, 
ws_sxvalue,  ws_acnomajor,  ws_acnominor,  ws_end_price,  ws_beg_phmrkt, 
ws_beg_phincacc,  ws_beg_price,  (ws_end_unitval -
ws_beg_unitval)/ws_beg_unitval,  ws_coupon,  ws_maturity_date, 
ws_short_desc);  END IF;  ws_msg := 'FETCH PROCESSING FOR RETURN CURSOR'; 
FETCH RETURN INTO  ws_secid,  ws_acid,	ws_end_phperiod,  ws_mdlid,  ws_ndid,

 ws_feehndl, ws_calctype, ws_phcurrency, ws_end_phmrkt, ws_phflowstot,

ws_phincearn,  ws_end_phincacc,  ws_prinretn,  ws_totretn,  ws_sxvalue, 
ws_acnomajor,  ws_acnominor,  ws_end_price,  ws_end_unitval,  ws_coupon, 
ws_maturity_date,  ws_short_desc;  EXIT WHEN RETURN%NOTFOUND; END LOOP;

COMMIT; ws_msg := 'CLOSE PROCESSING FOR RETURN CURSOR'; CLOSE RETURN; COMMIT; OPEN mtd_cv FOR SELECT * FROM V_PERFSEC_MTDRETN; EXCEPTION WHEN ws_cursor_empty THEN ROLLBACK; dbms_output.put_line('THE RETURN CURSOR IS EMPTY'); ws_count := ws_count / 0; WHEN OTHERS THEN ROLLBACK;

dbms_output.put_line('SQL ERROR OCCURRED DURING ' || ws_msg); 
dbms_output.put_line('SQLCODE = '  ||  SQLCODE); 
dbms_output.put_line('SQLERRM = '  ||  SQLERRM);  ws_count := ws_count / 0;

END dst_perfsec_mtd_held;

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Jun 11 1998 - 11:27:38 CDT