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

Thanks Varma,

It is working now.

Mai

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:v58gsffcdbvhab_at_corp.supernews.com...
> And plsql's sql lags behind in sql syntax.
>
> You cannot do select a, (select b from dual) from dual;
> ^^^^^^^^^^^^^^^
> in pl/sql (last time I checked in 8.1.6).
> example:
> *********************
> sql> select 1, (select 2 from dual) from dual;
>
> 1 (SELECT2FROMDUAL)
> ---------- -----------------
> 1 2
>
> sql> declare
> 2 a number;
> 3 b number;
> 4 begin
> 5 select 1, (select 2 from dual) into a,b from dual;
> 6 end;
> 7 /
> select 1, (select 2 from dual) into a,b from dual;
> *
> ERROR at line 5:
> ORA-06550: line 5, column 12:
> 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 5, column 32:
> PLS-00103: Encountered the symbol "INTO" when expecting one of the
following:
> ; return returning and or
>
>
> sql> declare
> 2 a number;
> 3 b number;
> 4 begin
> 5 select 1, 2 into a,b from dual;
> 6 end;
> 7 /
>
> PL/SQL procedure successfully completed.
> ********************************
>
> You need to re-write the sql to be pl/sql compliant.
>
> I guess in 9i you should be able to do it .. though I have not checked it
myself.
>
> Anurag
>
>
> "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 - 22:04:26 CST