While INSERT got an error PLS-00357: Table,View Or Sequence reference not allowed in this context

Question

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
 for i in 1..rc.count 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;
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.

Code you posted won't even compile. I suggest you to post real code, if you want to get assistance. <var>˙? What are you exiting from (the 3rd line after begin? What is that = 0 doing there? It is not fom but from. select col1 from tbl1 requires an INTO and you should make sure it returns a single value (otherwise you'd get TOO_MANY_ROWS or NO_DATA_FOUND, and none of them is handled). — Littlefoot
@Littlefoot Sorry, i mistype the code. Here is the updated code. But i want to know PLS 00428: an INTO clause is expected in this SELECT statement and also PLS 00357: Table,View Or Sequence reference not allowed in this context. I think the issue maybe in the IF condition while match the data from txt along with the cursor variable. While cursor also fetching the record of same table txt. How can i resolve the issue. Could you please help me to resolve this issue. Thanks — Shahin P
@ShahinP That code still will not compile. Please fix all the obvious errors (e.g. the <var>). — Frank Schmitt
The error message PLS 00428: an INTO clause is expected in this SELECT statement tells you exactly what is wrong: when you use SELECT in PL/SQL, you need an INTO clause (the variable where you store the result of your SELECT). Therefore, you're missing INTO in select col1 from tbl1 — Frank Schmitt

Frank Schmitt Frank Schmitt · Accepted Answer · 2018-05-17T11:05:43

Your code contains multiple errors:

<var> name;

is not valid PL/SQL syntax. This should probably be

name varchar2(30 char);

Similar,

fom

should be

from

This is illegal:

exit when record%notfound = 0;

You can use exit only inside a loop. Also, the syntax is wrong - this should be

exit when record%notfound;

Since rc is not a collection, this:

for i in 1 .. rc.count

is also invalid. I gave up after that - please fix these simple errors and try to boil down your problem to a MCVE

While INSERT got an error PLS-00357: Table,View Or Sequence reference not allowed in this context

1 Answers