Re: SQL runs but fails inside a PL/SQL (ORACLE 8i)

Jim,

Stat_Date is TEXT, someone decided to design that way.

However, PL/SQL just kept failing while SQL ran successfully evry time.

Mai

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:CKW4a.183087$be.163658_at_rwcrnsc53...
> substr(bm.stat_date,-7) = '01/2003';
> doesn't look right. If bm.stat_date is a date then a substr of a date is
> not a good idea. (dates are not strings, dates are dates. They can be
> formatted as strings.) So maybe you meant
> substr(to_char(bm.stat_date,'mm/dd/yyyy'),-7) = '01/2003';
> which could be done
> to_char(bm.stat_date,'dd/yyyy') = '01/2003';
>
> Jim
>
>
>
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family. Remove the negative part, keep the minus sign. You can
figure
> it out.
> "Mai Dang" <mdang_at_bigpond.com.au> wrote in message
> news:b31brh$3p7$1_at_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 - 20:38:13 CST