Problem with PLSQL cursor

Please look at this anynymous block.

declare
existingEmployee varchar2(45);
errorCode number;
errorMessage varchar2(250);
pastEmployee exception;

cursor c1 is
  select user_uid, user_company_name, user_email from sometable;

p1 c1%ROWTYPE;

begin
  if not c1%isopen then
      open c1;
  end if;
 
  fetch c1 into p1;
 
  while c1%found loop
   if (  
     (instr(p1.email, '@ddd.com') > 0) or
     (instr(p1.email, '@sss.com') > 0) or
     (instr(p1.email, '@fff.com') > 0)
    )
  then
     raise pastEmployee;
 end if;

  fetch c1 into p1;
     
  end loop;
  close c1;
     
             
exception
  when pastEmployee then
      insert into ERRORTABLE (errorNo, errorDescription, errorText)
             values (3001, p1.person_id || ' ' || p1.email
         || ' ' || 'User is invalid', 'USER IS EMPLOYEE');
     when others then
            errorCode := sqlcode;
            errorMessage := sqlerrm;
            insert into ERRORTABLE(errorCode, errorMessage, 'SERVER GENERATED ERROR');
 end;
/

When I run this I get the following error:

SQL> @c:\conversion\code\PopulateUsers.sql;
  select user_uid, user_company_name, user_email from sometable;
         *
ERROR at line 8:
ORA-06550: line 44, column 56:
PL/SQL: ORA-01747: invalid user.table.column, table.column, or column
specification
ORA-06550: line 44, column 4:
PL/SQL: SQL Statement ignored

How would I be able to fix it? Any help!!
bbhattaraiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dotmeatCommented:
the error could be here...

 while c1%found loop
   if (  
     (instr(p1.user_email, '@ddd.com') > 0) or
     (instr(p1.user_email, '@sss.com') > 0) or
     (instr(p1.user_email, '@fff.com') > 0)
    )


You open the cursor , and select user_email, NOT email field.
***********************
cursor c1 is
  select user_uid, user_company_name, user_email from sometable;
***********************
bbhattaraiAuthor Commented:
dotmeat, you spotted the mistake but this error is still coming even after I fixed it as you said. The star points to select statement just like I posted earlier and the error codes also are the same ones.
dotmeatCommented:
You have 2 errors:

ERROR at line 8:
ORA-06550: line 44, column 56:
PL/SQL: ORA-01747: invalid user.table.column, table.column, or column
specification
ORA-06550: line 44, column 4:
PL/SQL: SQL Statement ignored

Try to test your " insert into ERRORTABLE ..." seperately.
It maybe something wrong with this statement... I dont have Oracle installed in my laptop. Back to your question on Monday when I back to work...




Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bbhattaraiAuthor Commented:
dotmeat, thanks for the help. That worked. Could you also please tell me how would I go back and continue processing the table using the cursor after the exception is thrown once. There can be more than one records which should be recorded in the error table?
dotmeatCommented:
You should not throw exception this case.      

begin
  if not c1%isopen then
      open c1;
  end if;
 
  fetch c1 into p1;
 
  while c1%found loop
   if (  
     (instr(p1.email, '@ddd.com') > 0) or
     (instr(p1.email, '@sss.com') > 0) or
     (instr(p1.email, '@fff.com') > 0)
    )
  then
     insert into ......;
 end if;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.