I wonder where it shows PLS-00103: Encountered the symbol "BEGIN" when i compile the procedure. please help
create or replace
PROCEDURE GETCTRLEDPTDETAILSBYIPADDRR
(
v_ipaddress IN NVARCHAR2 DEFAULT NULL ,
v_organizationId IN NUMBER DEFAULT NULL ,
cv_1 OUT SYS_REFCURSOR,
cv_2 OUT SYS_REFCURSOR
)
AS
v_collectorId NUMBER(10,0);
v_sqlQuery VARCHAR2(7500);
BEGIN
OPEN cv_1 FOR
SELECT *
INTO v_collectorId FROM(select CollectorId from Collectors where IPAddress like '%'||v_ipaddress || '%');
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;
BEGIN
IF ( v_collectorId IS NOT NULL ) THEN
BEGIN
v_sqlQuery := 'SELECT
null as EndpointId,
C.CollectorId,
C.CollectorTypeId,
null as StatusCodeId,
C.StatusCodeID,
null as SerialNumber,
null as MeterNo,
RFC.FirmwareVersion,
C.Name
From Endpoints E
LEFT JOIN Collectors C on C.collectorId = E.spuId
LEFT JOIN Meters M on M.meterId = E.meterId
LEFT JOIN RFCollectors RFC on RFC.collectorId = C.collectorId
LEFT JOIN CollectorTypes CT on CT.collectorTypeId = C.collectorTypeId;
Where C.CollectorId = v_collectorId';
END;
ELSE
BEGIN
v_sqlQuery := 'SELECT
E.EndpointId,
null as CollectorId,
null as CollectorTypeId,
E.StatusCodeId ,
C.StatusCodeId ,
E.SerialNumber,
M.MeterNo,
null as FirmwareVersion,
null as Name
From Endpoints E
LEFT JOIN Collectors C on C.collectorId = E.spuId
LEFT JOIN Meters M on M.meterId = E.meterId
LEFT JOIN RFCollectors RFC on RFC.collectorId = C.collectorId
LEFT JOIN CollectorTypes CT on CT.collectorTypeId = C.collectorTypeId;
Where E.Ipaddrv6 like ''%'|| v_ipaddress ||'%''';
END;
END IF;
OPEN cv_2 FOR v_sqlQuery;
END;
You have got:
procedure xxx is
begin
some code
exception
...
end;
begin
...
That is not allowed. A procedure has ONE begin-end section. If you need others, than they need to be nested *within* that, eg
procedure xxx is
begin
begin
some code
exception
...
end;
begin
...
end;
end;
</code>