Procedure compile error

[timmbo]

Hi all,

Attempting to write my first procedure and not sure where I'm going wrong. When I compile, the following error shows:

PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> c

Can someone tell me where I'm going wrong.
Code follows:

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';


BEGIN
DECLARE CURSOR pdr_summary_cur RETURN summary_rec%ROWTYPE;
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);
BEGIN
/* Checking if cursor is open */
IF NOT pdr_summary_detail_info%ISOPEN THEN
OPEN pdr_summary_cur;
END IF;
/* Retrieving all records from cursor. */
LOOP
FETCH pdr_summary_cur INTO summary_rec;
INSERT INTO pdr_summary_detail_info
VALUES (summary_rec.baiBatchNum,
TO_DATE(summary_rec.asofDate, 'mm/dd/yyyy'),
summary_rec.abaNum,
summary_rec.acctNum,
acct_bal_summ,
summary_rec.baiType,
summary_rec.amount,
summary_rec.currencyCode,
summary_rec.itemCount,
summary_rec.bankName,
summary_rec.bankLocation);
COMMIT;
/* Exit if EOF */
EXIT WHEN pdr_summary_cur%NOTFOUND;
END LOOP;

END pdr_summary_cur;

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


TIA,
Tim

Using TOAD ver6.5.

 

[Guest_imported]

Why don't you try your code this way:

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);

pdr_summary_cur summary_rec%ROWTYPE;

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

END pdr_summary_cur;

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

 

[timmbo]

Thanks for the help sokeh. I will try your suggestion.