| Plzz help me to find out this problem with procedure [message #373806] |
Thu, 10 May 2001 17:28  |
Sushmita Saha
Messages: 1
Registered: May 2001
|
Junior Member |
|
|
Please help me to find out what error is there
I am using Oracle 8
*********************************************************
create or replace procedure fetch_USERNEWS(vnewsId IN VARRAY,userNO IN NUMBER,userTYPE IN NUMBER,out_news OUT vARRAY) as
v_counter NUMBER ;
TYPE vnews IS VARRAY(366) OF NUMBER;
iCount NUMBER;
ID NUMBER;
CURSOR cnews_id(inewsId NUMBER) IS
select UNIQUE newscategory_id from news where news_id=inewsId;
CURSOR cnews_func(inewsId NUMBER) IS
select news_id from news_audience where news_id=inewsId and newscategory_id=1 and
news_subcategory in(select generic_id from user_prsnl where
user_number =userNO);
CURSOR cnews_usertype(inewsId NUMBER) is
select news_id from news_audience where news_id=inewsId and newscategory_id=2 and
news_subcategory in(select user_type from corp_users where user_type=userType
and is_active='Y');
BEGIN
iCount:=vnewsId.count;
for iCount in 1..vnewsId.count loop
OPEN cnews_id;
for cnews_id_rec in cnews_id(vnewsId(iCount)) LOOP
IF(cnews_id_rec.newscategory_id = 1) THEN
OPEN cnews_func;
FOR cnews_func_rec IN cnews_func(vnewsId(iCount)) LOOP
out_news (v_counter) := cnews_func_rec.news_id;
v_counter:= v_counter + 1;
END LOOP;
CLOSE cnews_func;
ELSE IF(cnews_id_rec.newscategory_id = 2) THEN
OPEN cnews_usertype;
FOR cnews_usertype_rec IN cnews_usertype(vnewsId(iCount)) LOOP
out_news (v_counter) := cnews_usertype_rec.news_id;
v_counter:=v_counter + 1;
END LOOP;
CLOSE cnews_usertype;
ELSE
OPEN cnews_usertype;
FOR cnews_usertype_rec IN cnews_usertype(vnewsId(iCount)) LOOP
out_news (v_counter) := cnews_usertype_rec.news_id;
v_counter:=v_counter + 1;
END LOOP;
CLOSE cnews_usertype;
END IF;
END LOOP;
CLOSE cnews_id;
END LOOP;
END fetch_USERNEWS
**************************************************************
MY ERROR:
Errors for PROCEDURE FETCH_USERNEWS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
46/7 PLS-00103: Encountered the symbol "LOOP" when expecting one of
the following:
if
50/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin function package pragma procedure form
|
|
|
|
| Re: Plzz help me to find out this problem with procedure [message #373811 is a reply to message #373806] |
Mon, 14 May 2001 07:22  |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Currently you have:
########################################################################
DECLARE
variable set here
C1 (my C1 is your CURSOR cnews_id)....
C2 (my C2 is your CURSOR cnews_func)....
C3 (my C3 is your CURSOR cnews_usertype)....
BEGIN
initialize variables
FOR condition is true loop
FOR C1 loop
OPEN C1;
IF 'csnews_id_rec.newscategory' = 1 then
OPEN C2;
FOR C2 loop
.
.
END LOOP; /* end of third for loop */
CLOSE C2; /* close cursor 2 */
ELSE IF 'csnews_id_rec.newscategory' =2 then
OPEN C3;
For C3 loop
.
.
END LOOP; /* end if third for loop */
CLOSE c3; /* close cursor 3 */
ELSE
OPEN C3;
For C3 loop
.
.
END LOOP; /* end if third for loop */
CLOSE c3; /* close cursor 3 */
END IF; /* end of if */
END LOOP; /* end of second for loop */
CLOSE C1; /* close cursor 1 */
END LOOP; /* end of first loop */
##########################################################################
You're missing a 'END' at the end of procedure. It should be like this:
==========================================================================
DECLARE
variable set here
C1 ....
C2 ....
C3 ....
BEGIN
initialize variables
FOR condition is true loop
FOR C1 loop /* open C1 here */
IF 'csnews_id_rec.newscategory' = 1 then
FOR C2 loop /* open C2 here */
.
.
END LOOP; /* end of third for loop */
CLOSE C2; /* close cursor 2 */
ELSE IF 'csnews_id_rec.newscategory' =2 then
For C3 loop /* open C3 here */
.
.
END LOOP; /* end if third for loop */
CLOSE c3; /* close cursor 3 */
ELSE
For C3 loop
.
.
END LOOP; /* end if third for loop */
CLOSE c3; /* close cursor 3 */
END IF; /* end of if */
END LOOP; /* end of second for loop */
CLOSE C1; /* close cursor 1 */
END LOOP; /* end of first loop */
END; /* YOU'RE MISSING this END AND / CHAR */
/
========================================================================
I am a little puzzle of what you're trying to do for the conditions of
'csnews_id_rec.newscategory' equal to 2 and greater than 2. It seems like
currently you have both conditions perform the same tasks wheather
'csnews_id_rec.newscategory' equal to 2 or greater than 2. If this true,
then do this:
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DECLARE
variable set here
C1 ....
C2 ....
C3 ....
BEGIN
initialize variables
FOR condition is true loop
FOR C1 loop /* open C1 here */
IF 'csnews_id_rec.newscategory' = 1 then
FOR C2 loop /* open C2 here */
.
.
END LOOP; /* end of third for loop */
CLOSE C2; /* close cursor 2 */
ELSE /* this mean 'csnews_id_rec.newscategory' > 1 conditions */
For C3 loop /* Open C3 here */
.
.
END LOOP; /* end if third for loop */
CLOSE c3; /* close cursor 3 */
END IF; /* end of if */
END LOOP; /* end of second for loop */
CLOSE C1; /* close cursor 1 */
END LOOP; /* end of first loop */
END; /* end of begin */
/
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
If this not what you meant to codes, then you should modify one of the two
FOR C3 Loop codes, so that if it equal to two, you want it do something
differently from all other conditions (greater than 2). Current codes are
not the case.
Also you don't need the OPEN C1, OPEN C2, and OPEN C3. because of the FOR C1
loop, FOR C2 loop, and FOR C3 loop.
HTH,
--Cindy
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
