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,

[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

Original text of this message