PLS Errors

[timmbo]

Hi All,

Have been trying to get my first procedure to compile - no luck.
Trying to get my cursor (pdr_summary_cur) to grab various columns from three different tables.
With each row from my cursor (loop), I want to populate pdr_summary_detail_info table.

Can someone lend a hand here???

My first error is telling me - PLS-00201: identifier 'PRSCCDB.PDR_ACCT_SUMMARY' must be declared. I'm trying to grab columns from this table. This error is raised at FROM pdr_acct_summary s,.

My next error - PLS-00201: identifier 'V_PDR_SUMMARY_REC' must be declared. This error hits at the FETCH pdr_summary_cur INTO v_pdr_summary_rec;. I thought I declared it at v_pdr_summary_rec pdr_summary_cur%ROWTYPE; line.

Any help would be most appreciated.

TIA

CREATE OR REPLACE PROCEDURE insert_summary IS

/*
|| InitialiZe Variables.
*/

acct_bal_summ CONSTANT VARCHAR2(13) := 'acct_bal_summ';
cr_db_summ CONSTANT VARCHAR2(10) := 'cr_db_summ';
detail_summ CONSTANT VARCHAR2(11) := 'detail_summ';

CURSOR pdr_summary_cur
IS
SELECT s.asofDate,
s.abaNum,
s.acctNum,
s.amount,
s.currencyCode,
s.itemCount,
b.bankName,
b.bankLocation,
c.baiType
FROM pdr_acct_summary s,
bank b,
bai_type_code c
WHERE s.abaNum = b.abaNum
AND s.baiType = c.baiType
AND c.baiType IN (15,40,45,72,74,100,400);

v_pdr_summary_rec pdr_summary_cur%ROWTYPE;

BEGIN
/* Checking if cursor is open */
IF NOT pdr_summary_cur%ISOPEN THEN
OPEN pdr_summary_cur;
END IF;
/* Retrieving all records from cursor. */
LOOP
FETCH pdr_summary_cur INTO v_pdr_summary_rec;
INSERT INTO pdr_summary_detail_info
VALUES (v_pdr_summary_rec.baiBatchNum,
TO_DATE(v_pdr_summary_rec.asofDate, 'mm/dd/yyyy'),
v_pdr_summary_rec.abaNum,
v_pdr_summary_rec.acctNum,
acct_bal_summ,
v_pdr_summary_rec.baiType,
v_pdr_summary_rec.amount,
v_pdr_summary_rec.currencyCode,
v_pdr_summary_rec.itemCount,
v_pdr_summary_rec.bankName,
v_pdr_summary_rec.bankLocation);
COMMIT;
/* Exit if EOF */
EXIT WHEN pdr_summary_cur%NOTFOUND;
END LOOP;

--EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- Null;
-- WHEN OTHERS THEN
-- Null;
END insert_summary;

 

[lewisp]

First, check that the user you are creating the procedure in has select access to pdr_acct_summary. It looks like this table is owned by PRSCCDB, so its likely you have a synonym set up but not a grant.

Your second error is a result of the first. It cannot create the cursor so therfore it cannot create the rowtype variable based on that cursor.

 

[timmbo]

Thanks for the response Lewisp. That's exactly what it was. Have a good one.