Sarseth Sarseth - 5 months ago 35
SQL Question

PLSQL oracle select after declare ORA-06550 PLS-00103

Oracle throws me ORA-065550 PLS-00103 Encountered the symbol "SELECT".
Well, can not find what's wrong here.

DECLARE someId NUMBER;
BEGIN
select id into someId from someTable where someColumn = 'someUniqueValue';
select * from someTable; --here
END;
select * from someTable; --or here


Neither second select, neither third works. When I dmbs_output someId into console it works well, this assigning to declared variable. But why I can not just select after 'into'? I want to declare one time value, and then make

select * from someTable where id = someId

or to be precised want to make like thousand inserts and I want to cache that first select.

DECLARE someId NUMBER;
BEGIN
select id into someId from someTable where someColumn = 'someValue';
END;
/
select * from otherTable where otherTableId = someId;


Here I lost someId scope I guess.

Answer

Basically you doing a lot of mistakes while doing this code.

1.You cant just 'SELECT *' in the anonymous block. You need to have cursor to do it.

2.Your variable scope is till the anonymous block. Now you are trying to access the variable outside the block --> NAAH not possible.

So I have tried to resolve your issue by below snippet hope it helps.

DECLARE
  someId NUMBER;
  p_lst sys_refcursor;
BEGIN
  SELECT id INTO someId FROM someTable WHERE someColumn = 'someUniqueValue';
  OPEN p_lst FOR
  'SELECT * FROM someTable 
   where otherTableId = '||someId; 
END;