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@mws-stat-syd.cdn.telstra.com.au>

Hi,

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 Wed Feb 19 2003 - 19:43:23 CST