Question

Oracle coding error ORA-06550

Oracle development, Oracle error messages, ORA-06550

Hi,

I get the following error on compiling my code -
***********************************************************
ORA-06550: line 16, column 1:
PLS-00103: Encountered the symbol "ELSE" when expecting one of the
following:

begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
ORA-06550: line 19, column 1:
PLS-00103: Encountered the symbol "END"
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
********************************************************
MyCode is
********************************************************
drop table BDTable;
/

CREATE GLOBAL TEMPORARY TABLE BDTable
(
BDGroupArea VARCHAR(1)
) ON COMMIT DELETE ROWS
/
declare
str VARCHAR(255);
BDGroupArea VARCHAR(255);
pos integer;
Begin
BDGroupArea:='1,2,3';
IF (instr(BDGroupArea, '%,%')<>0) Then
WHILE (INSTR(BDGroupArea, '%,%')<>0) LOOP BEGIN
BDGroupArea := BDGroupArea + ',' ;
pos:= instr(BDGroupArea, ',');
str:= SUBSTR(BDGroupArea,1,pos-1);
INSERT INTO BDTable (BDGroupArea) VALUES (str);
BDGroupArea:= SUBSTR(BDGroupArea, pos+1, LENGTH(BDGroupArea)); END LOOP; ELSE INSERT INTO BDTable (BDGroupArea) VALUES ( BDGroupArea ); End IF; End; / SELECT * FROM BDTable; /


Thanks,
Sowmya

One suggestion too all of the new bees, take time and and take a good look and alakyze your code, other wise you will send your code these sites all the time.

Here is your right code. "BEGIN" inside the loop didn't have "END"

DECLARE
str VARCHAR(255);
BDGroupArea VARCHAR(255);
pos INTEGER;
BEGIN
BDGroupArea := '1,2,3';

IF (INSTR(BDGroupArea,'%,%') <> 0) THEN
WHILE(INSTR(BDGroupArea,'%,%') <> 0) LOOP
--BEGIN
BDGroupArea := BDGroupArea + ',';
pos := INSTR(BDGroupArea,',');
str := SUBSTR(BDGroupArea,1, pos - 1);

INSERT INTO BDTable
(BDGroupArea)
VALUES (str);

BDGroupArea := SUBSTR(BDGroupArea, pos + 1 ,LENGTH(BDGroupArea));
END LOOP;
ELSE
INSERT INTO BDTable
(BDGroupArea)
VALUES (BDGroupArea);
END IF;
END;