SQL runs but fails inside a PL/SQL (ORACLE 8i)
From: Mai Dang <mdang_at_bigpond.com.au>
Date: Thu, 20 Feb 2003 12:43:23 +1100
Message-ID: <b31brh$3p7$1_at_mws-stat-syd.cdn.telstra.com.au>
Hi,
Connected.
( - + mod not null others <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string>
; return returning and or
The symbol "return" was substituted for "VERSION" to continue.
, into bulk
aggr_date DATE;
BEGIN
FOR bm_rec IN bm_cursor LOOP
) AS TOTAL FROM DUAL
));
END LOOP;
END; Received on Thu Feb 20 2003 - 02:43:23 CET
Date: Thu, 20 Feb 2003 12:43:23 +1100
Message-ID: <b31brh$3p7$1_at_mws-stat-syd.cdn.telstra.com.au>
Hi,
[Quoted] I wonder if anyone can help me where I did wrong in the following PL/SQL
If I only run the DELETE and INSERT Statements without CURSOR, then SQL
works, by replacing
to_char(aggr_date, 'DD/MM/YYYY') with '01/01/2003').
But if I compile the whole PL/SQL then it ended up with
Error Message
Connected.
(SELECT VERSION
*
ERROR at line 16:
ORA-06550: line 16, column 8: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string>
ORA-06550: line 19, column 33: PLS-00103: Encountered the symbol "VERSION" when expecting one of the following:
; return returning and or
The symbol "return" was substituted for "VERSION" to continue.
ORA-06550: line 23, column 7: PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
, into bulk
PL/SQL:
set serveroutput on;
DECLARE
CURSOR bm_cursor IS
SELECT to_date(bm.stat_date, 'DD/MM/YYYY') As stat_date FROM cq_vps_aggr_cc_perform_bm_v8 bm where bm.BM= 'BM1' and substr(bm.stat_date,-7) = '01/2003';
aggr_date DATE;
BEGIN
FOR bm_rec IN bm_cursor LOOP
aggr_date := bm_rec.stat_date;
IFaggr_date = '01-JAN-2003' THEN
DELETE FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8 WHERE BM = 'CBM4' AND stat_date = to_char(aggr_date, 'DD/MM/YYYY'); COMMIT; INSERT INTO CQ_VPS_AGGR_CC_PERFORM_CBM_V8
(SELECT
to_char(aggr_date, 'DD/MM/YYYY') STAT_DATE,
(SELECT VERSION
FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8 WHERE stat_date = to_char(aggr_date, 'DD/MM/YYYY') AND BM IN ('CBM1')) VERSION, 'CBM4' AS BM, TOTAL, SYSDATE AS RUN_DATE FROM
(
SELECT ( SELECT TOTAL FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8 WHERE stat_date = to_char(aggr_date, 'DD/MM/YYYY') AND BM IN ('CBM1')) + (SELECT TOTAL FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8 WHERE stat_date = to_char(aggr_date, 'DD/MM/YYYY') AND BM IN ('CBM2')) + (SELECT TOTAL FROM CQ_VPS_AGGR_CC_PERFORM_CBM_V8 WHERE stat_date = to_char(aggr_date, 'DD/MM/YYYY') AND BM IN ('CBM3')
) AS TOTAL FROM DUAL
));
COMMIT;
END IF; dbms_output.put_line(aggr_date);
END LOOP;
END; Received on Thu Feb 20 2003 - 02:43:23 CET