| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL runs but fails inside a PL/SQL (ORACLE 8i)
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:
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 - 20:55:08 CST
 |
 |