Hi All,
I will be really obliged if some one will help me in finding the beneath errors in below written code.
CREATE OR REPLACE FUNCTION GETPAIR1(
ELE_ID IN NUMBER,
REQPAIR IN NUMBER,
USER_SPEC_PAIRS VARCHAR2
)
RETURN VARCHAR2
IS
ELE_TYPE VARCHAR2(50);
PARENTID NUMBER;
TYPEID NUMBER;
LOWEND NUMBER;
USER_SPE_PAIRS VARCHAR2(20);
LOW_INPORT_ID NUMBER;
LOW_INPORT_NAME VARCHAR2(20);
HIGH_INPORT_ID VARCHAR2(500);
HIGH_OUTPORT_ID NUMBER;
HIGH_OUTPORT_NAME VARCHAR2(200);
CAB_ID NUMBER;
CABLE_INPIN_ID NUMBER;
CABLE_OUTPIN_ID NUMBER;
STRIPNO NUMBER;
STRIPID NUMBER;
PORTID NUMBER;
STR VARCHAR2(2000);
NEWTRACE VARCHAR2(2000);
TAG NUMBER;
CURSOR C1 IS SELECT PARENT_ID, TYPE_ID FROM ELEMENT WHERE ID = ELE_ID;
CURSOR C2 IS SELECT STRIP_NO, ID FROM PAIR_DETAILS WHERE ELEMENT_ID = PARENTID AND PHYSICAL_STATUS = 'Spare' AND IN_OUT = 'Spare' AND ROWNUM <= REQPAIR ;
CURSOR C3 IS SELECT NAME, ID FROM PAIR_DETAILS WHERE ELEMENT_ID = ELE_ID AND PHYSICAL_STATUS = 'Spare' AND IN_OUT = 'E' AND ROWNUM <= REQPAIR ;
CURSOR C4 IS SELECT ID1, LOW_END FROM CONNECTIVITY WHERE ID2 = LOW_INPORT_ID AND TABLE_REF1 = 5001 AND TABLE_REF2 = 4003;
CURSOR C5 IS SELECT CABLE_ID FROM CABLE_PIN WHERE ID = CABLE_OUTPIN_ID AND SEQ_NUM = 2;
CURSOR C6 IS SELECT ID FROM CABLE_PIN WHERE CABLE_ID = CAB_ID AND SEQ_NUM = 1;
CURSOR C7 IS SELECT P.STRIP_NO, P.STRIP_ID, P.NAME, C.ID1 FROM PAIR_DETAILS P, CONNECTIVITY C WHERE C.ID1 = P.ID AND C.ID2 = CABLE_INPIN_ID AND C.LOW_END = LOWEND AND C.TABLE_REF1 = 4003 AND C.TABLE_REF2 = 5001;
CURSOR C8 IS SELECT NAME, ID FROM PAIR_DETAILS WHERE ELEMENT_ID = ELE_ID AND PHYSICAL_STATUS = 'Spare' AND IN_OUT = 'E' AND ROWNUM <= 2 ;
BEGIN
ELE_TYPE := '';
PARENTID := 0;
TYPEID := 0;
LOWEND := 0;
NEWTRACE := '';
LOW_INPORT_ID := 0;
HIGH_INPORT_ID := 0;
LOW_INPORT_NAME := '';
HIGH_OUTPORT_NAME := '';
HIGH_OUTPORT_ID := 0;
CAB_ID := 0;
CABLE_INPIN_ID := 0;
CABLE_OUTPIN_ID := 0;
PORTID := 0;
STRIPID := 0;
STR := '';
NEWTRACE := '<TABLE>';
-- IF GIVEN ELEMENT ID IS OF MDF
--IF PARENTID = ELE_ID AND TYPEID <> 128 THEN
IF TYPEID = 125 THEN
OPEN C1;
FETCH C1 INTO PARENTID, TYPEID;
CLOSE C1;
OPEN C2;
FETCH C2 INTO STRIPNO, PORTID;
WHILE C2%FOUND
LOOP
STR := STR || STRIPNO || '/' || PORTID || ':' ;
STRIPNO := 0;
PORTID := 0;
FETCH C2 INTO STRIPNO, PORTID;
END LOOP;
CLOSE C2;
--ELSIF PARENTID <> ELE_ID AND TYPEID <> 128 THEN
ELSIF TYPEID = 126 THEN
OPEN C3;
FETCH C3 INTO LOW_INPORT_NAME, LOW_INPORT_ID;
WHILE C3%FOUND
LOOP
NEWTRACE := NEWTRACE || '<ROW>';
OPEN C4;
FETCH C4 INTO CABLE_OUTPIN_ID, LOWEND;
CLOSE C4;
OPEN C5;
FETCH C5 INTO CAB_ID;
CLOSE C5;
OPEN C6;
FETCH C6 INTO CABLE_INPIN_ID;
CLOSE C6;
OPEN C7;
FETCH C7 INTO STRIPNO, STRIPID, HIGH_OUTPORT_NAME, HIGH_OUTPORT_ID;
CLOSE C7;
--ELSIF PARENTID <> ELE_ID AND TYPEID = 128 THEN
ELSIF TYPEID = 128 THEN
OPEN C8;
FETCH C8 INTO LOW_INPORT_NAME, LOW_INPORT_ID;
WHILE C8%FOUND
LOOP
NEWTRACE:='';
NEWTRACE := NEWTRACE || '<ROW>';
OPEN C4;
FETCH C4 INTO CABLE_OUTPIN_ID, LOWEND;
CLOSE C4;
OPEN C5;
FETCH C5 INTO CAB_ID;
CLOSE C5;
OPEN C6;
FETCH C6 INTO CABLE_INPIN_ID;
CLOSE C6;
OPEN C7;
FETCH C7 INTO STRIPNO, STRIPID, HIGH_OUTPORT_NAME, HIGH_OUTPORT_ID;
CLOSE C7;
/*
OPEN C4;
FETCH C4 INTO CABLE_OUTPIN_ID, LOWEND;
CLOSE C4;
OPEN C5;
FETCH C5 INTO CAB_ID;
CLOSE C5;
OPEN C6;
FETCH C6 INTO CABLE_INPIN_ID;
CLOSE C6;
OPEN C7;
FETCH C7 INTO STRIPNO, STRIPID, HIGH_OUTPORT_NAME, HIGH_OUTPORT_ID;
CLOSE C7;
*/
NEWTRACE :=
NEWTRACE
|| '<STRIPNO'
|| TAG
|| '>'
|| STRIPNO
|| '</STRIPNO'
|| TAG
|| '>';
NEWTRACE :=
NEWTRACE
|| '<STRIPID'
|| TAG
|| '>'
|| STRIPID
|| '</STRIPID'
|| TAG
|| '>';
NEWTRACE :=
NEWTRACE
|| '<HIGH_OUTPORT_NAME'
|| TAG
|| '>'
|| HIGH_OUTPORT_NAME
|| '</HIGH_OUTPORT_NAME'
|| TAG
|| '>';
NEWTRACE :=
NEWTRACE
|| '<HIGH_OUTPORT_ID'
|| TAG
|| '>'
|| HIGH_OUTPORT_ID
|| '</HIGH_OUTPORT_ID'
|| TAG
|| '>';
NEWTRACE :=
NEWTRACE
|| '<LOW_INPORT_NAME'
|| TAG
|| '>'
|| LOW_INPORT_NAME
|| '</LOW_INPORT_NAME'
|| TAG
|| '>';
NEWTRACE :=
NEWTRACE
|| '<LOW_INPORT_ID'
|| TAG
|| '>'
|| LOW_INPORT_ID
|| '</LOW_INPORT_ID'
|| TAG
|| '>';
IF TYPEID = 128 THEN
FETCH C8 INTO LOW_INPORT_NAME, LOW_INPORT_ID;
TAG := TAG + 1;
ELSE
FETCH C3 INTO LOW_INPORT_NAME, LOW_INPORT_ID;
TAG := TAG + 1;
END IF;
STRIPNO := 0;
PORTID := 0;
--LOW_INPORT_ID := 0;
HIGH_OUTPORT_ID := 0;
NEWTRACE := NEWTRACE || '</ROW>';
END LOOP;
CLOSE C3;
NEWTRACE := NEWTRACE || '</TABLE>';
END IF;
RETURN NEWTRACE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END getpair1;
/
Errors are in line which are in bold, italic and are underlined. From site it doesn't seem that there is any error. But compiler is throwing thses errors.
i) PLS-00103: Encountered the symbol "TYPEID" when expecting one of the following:
:= . ( @ % ;
ii) PLS-00103: Encountered the symbol "EXCEPTION" 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 identif
iii) PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
I will be really thankful to you.
Gursimran Singh