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" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
>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
>*********************************************************************
*******
>******
>*************************
>
>Regards,
>Jp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
