Ben,
1# i tried with "IS" in line number 3 too...doesn't work.
"AS" in line number 6 is the problem.
2# may be as u said , that embeded SELECT is a 9i feature.
let me confirm it.
Thanks Ben.
Jp.
18-07-2003 15:13:18, "Wittmeier, Ben" <Ben.Wittmeier_at_gov.ab.ca> wrote:
>Probably the syntax of the embedded select statement is not supported
in
>Oracle 8.1.6 - it's probably a 9i feature.
>Ben
>
>-----Original Message-----
>From: Prem Khanna J [mailto:jprem_at_kssnet.co.jp]
>Sent: Friday, July 18, 2003 12:13 AM
>To: LazyDBA.com Discussion
>Subject: Procedure -- Help !!!
>
>
>Guys,
>
>the procedure below works fine on 9iR2/win2k.
>but when i try to create it on 8.1.6/NT , i get the error below.
>
>can someone help me ???
>
>*********************************************************************
>******
>************************
> 1 CREATE OR REPLACE PROCEDURE DEL_MEMBER
> 2 (IN_MEMNO IN NUMBER,IN_CAUSE IN VARCHAR2,IN_IPNAM_ID IN NUMBER)
> 3 AS
> 4 CURSOR C1 IS
> 5 SELECT PREF,LEV,SEX,POINT,ENTPC
> 6 ,(SELECT RANK FROM ALB_BASE WHERE MEMNO = IN_MEMNO) AS RANK
> 7 FROM MEMBER
> 8 WHERE MEMNO = IN_MEMNO;
> 9 C1_REC C1%ROWTYPE;
> 10 ALB_RANK NUMBER(1);
> 11 BEGIN
> 12 OPEN C1;
> 13 LOOP
> 14 FETCH C1 INTO C1_REC;
> 15 EXIT WHEN C1%NOTFOUND;
> 16 --/*------------INSERT INTO MEMLOG-------------*/
> 17 IF C1_REC.RANK IS NULL THEN
> 18 ALB_RANK := 0;
> 19 ELSE
> 20 ALB_RANK := C1_REC.RANK;
> 21 END IF;
> 22 INSERT INTO MEMLOG
> 23 (MEMLOG_ID,MEMNO,
> 24 PREF,LEV,SEX,RANK,
> 25 LOG_CODE,POINT,USEPOINT,IPNAM_ID,ENTPC)
> 26 VALUES
> 27 (SEQ_MEMLOG.NEXTVAL,IN_MEMNO,
> 28 C1_REC.PREF,C1_REC.LEV,C1_REC.SEX,ALB_RANK,
> 29 '106',C1_REC.POINT,C1_REC.POINT,IN_IPNAM_ID,C1_REC.ENTPC
> 30 );
> 31 END LOOP;
> 32 CLOSE C1;
> 33 --/*------------MAKE DELFLG = 1 ------------*/
> 34 UPDATE INTRODUCTION
> 35 SET DELFLAG = 1
> 36 WHERE MEMNO = IN_MEMNO;
> 37 UPDATE MEMBER
> 38 SET LEV = 0,
> 39 LEVCHDATE = SYSDATE
> 40 WHERE MEMNO = IN_MEMNO;
> 41 UPDATE PROFILE
> 42 SET DEL_FLAG = 1
> 43 WHERE MEMNO = IN_MEMNO;
> 44 INSERT INTO MEMBER_COM
> 45 (CMMT_ID,MEMNO,ENTDATE,CMMT)
> 46 VALUES
> 47 (SEQ_MEMBER_COM.NEXTVAL,IN_MEMNO,SYSDATE,IN_CAUSE);
> 48* END;
>14:57:25 49 /
>
>Warning: Procedure created with compilation errors.
>
>14:57:27 SQL>SHOW ERROR
>Errors for PROCEDURE DEL_MEMBER:
>
>LINE/COL ERROR
>--------
>6/4 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>
>
>6/54 PLS-00103: Encountered the symbol "AS" when expecting one of
the
> following:
> ; return returning and or
>*********************************************************************
Received on Fri Jul 18 2003 - 01:39:00 CDT
