PLS 00357 Error- Table, View or Sequence "txt.col1" not allowed in the context

I have created one Stored Procedure. In that Stored Proc I want if the value of col1 & col2 match with employee then insert the unique record of the employee. If not found then match the value of col1, col2 & col3 with employee match then insert the value. If also not found while match all these column then insert the record by using another column. Also one more thing that i want find list of values like emp_id by passing the another column value and if a single record can not match then make emp_id as NULL.

create or replace procedure sp_ex
AS
empID_in varchar2(10);
fname_in varchar2(20);
lname_in varchar2(30);
---------

type record is ref cursor return txt%rowtype;  --Staging table
v_rc record;
rc rc%rowtype;
begin
 open v_rc for select * from txt;
 loop 
 fetch v_rc into rc;
 exit when v_rc%notfound;
 loop
      select col1 from tbl1
 Where EXISTS (select col1 from tbl1 where tbl1.col1 = rc.col1);

IF txt.col1 = rc.col1 AND txt.col2 = rc.col2 THEN
insert into main_table select distinct * from txt where txt.col2 = rc.col2;

ELSIF txt.col1 = rc.col1 AND txt.col2 = rc.col2 AND txt.col3 = rc.col3 THEN 
insert into main_table select distinct * from txt where txt.col2 = rc.col2;

ELSE 
insert into main_table select * from txt where txt.col4 = rc.col4;
end if;
end loop;
close v_rc;
end sp_ex;

I found an error while compile this Store Procedure PLS-00357: Table,View Or Sequence reference not allowed in this context. How to resolve this issue and how to insert value from staging to main table while using CASE or IF ELSIF statement. Could you please help me so that i can compile the Stored Proc.

Solution

Since I don't have your database to work with it's difficult to be 100% certain, but to my eye the line which reads

rc rc%rowtype;

should say

rc txt%rowtype;

You've defined the cursor v_rc as returning txt%rowtype, and your SQL statement used with this cursor is select * from txt, but that data type is at odds with the definition of rc. Thus, it appears you need to change rc as shown.

It also looks like the LOOP statement which comes immediately after exit when v_rc%notfound; should be removed, as there's nothing after that which would terminate that loop.

In addition, you have many references to columns in the txt table, e.g. IF txt.col1 = rc.col1. You can't refer to values in a table in this manner. I'm not quite sure what you're trying to do here so I can't really suggest anything.

Also, the statement

select col1 from tbl1
  Where EXISTS (select col1 from tbl1 where tbl1.col1 = rc.col1);

is selecting a column from the database, but isn't putting it anywhere. This should be either a singleton SELECT (SELECT..INTO) or a cursor.

One more thing: you can't use distinct *. You need to use a column list with distinct.

Perhaps the following would be close to what you're trying to do:

create or replace procedure sp_ex
AS
begin
  FOR rc IN (SELECT * FROM TXT)
  LOOP
    FOR t1 IN (SELECT *
                 FROM TBL1
                 WHERE TBL1.COL1 = rc.COL1)
    LOOP
      IF t1.COL1 = rc.COL1 AND
         t1.COL2 = rc.COL2
      THEN
        insert into main_table
          select *
            from txt
            where txt.col2 = rc.col2;
      ELSIF t1.col1 = rc.col1 AND
            t1.col2 = rc.col2 AND
            t1.col3 = rc.col3
      THEN 
        insert into main_table
          select *
            from txt
            where txt.col2 = rc.col2;
      ELSE
        insert into main_table
          select *
            from txt
            where txt.col4 = rc.col4;
      END IF;
    END LOOP;  -- t1
  END LOOP;  -- rc
end sp_ex;

Best of luck.