Problem with Crystal Reports and Oracle stored proc

[scar...@allstate.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....@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.DSTF...@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