0

i need to do update of one table and next i want see result of my actions. How can i use PL/SQL?! I have this variant, but it’s not work.

declare 
 switcher VARCHAR(20);
--step one - update
begin
  switcher := &faid;
  update table_one.field t1
  set t1.str_value = switcher
  where t1.name = 'NAME_1';
commit; 
end;
--step two - select 
begin
  select * from table_one.field t1
  where t1.name = 'NAME_1'
end
Евгений Рябышев
  • 29
  • 1
  • 6
  • 1
    which Oracle version are you using? You can't do `select * from` inside a procedure without an INTO. In 12c and above you may use `DBMS_SQL.RETURN_RESULT` – Kaushik Nayak Sep 21 '18 at 06:34
  • I guess if you wanted to 'see' results in `pl/sql` then you would use `dbms_output.put_line` to print out variables that contain the results - but first you would need to fill these variables with the results of the select statement. Your step 2 is actually an `sql` not `pl/sql` that is why it 'doesn't work' .... – PKey Sep 21 '18 at 06:34
  • Your naming convention is somewhat strange. Is table name really FIELD which belongs to user TABLE_ONE? Sounds as if it is a table whose name is TABLE_ONE and has a column named FIELD, but ... who knows? Let's suppose that UPDATE will, actually, work. The simplest way to display those values (if table isn't large and doesn't have many columns) is to a cursor FOR loop and DBMS_OUTPUT.PUT_LINE column by column. Otherwise, you'd have to return refcursor (for example). But, just for seeing what you've done? Is it worth it? Why not simply running that SELECT in SQL layer? – Littlefoot Sep 21 '18 at 06:35

1 Answers1

1

You can't return a result using a SELECT from within a PL/SQL block. Remove the begin before the select and the end after it.

You also can't include a column name (not "field") in the table for the UPDATE statement. The columns are referenced in the SET clause. The same is true for the table reference in the FROM clause.

You also need a different character to end a PL/SQL block. In the Oracle world (sqlplus, SQL Developer) that is usually the / character.

declare 
  switcher VARCHAR(20);
--step one - update
begin
  switcher := &faid;

  update table_one -- only the table name here
    set str_value = switcher  -- a column can be referenced here
  where name = 'NAME_1'; -- and a column can be referenced here

  commit; 
end;
/

select * 
from table_one t1 -- no "field" after the table name here either
where t1.name = 'NAME_1';

But you don't really need PL/SQL for the whole thing. Assuming SQL*Plus (or SQL Developer) the following would work just as well:

update table_one
  set str_value = &faid
where name = 'NAME_1';
commit; 

select * 
from table_one t1
where t1.name = 'NAME_1';