Re: 2 similar statements, one works, one doesn't. Why?

after the
end test;
/

put in

end test;
/
show errors
/

That will help in with debugging the intial PL/SQL code process.

If you are thinking that it SQL*Plus would print '1 row updated' for a update dual set x = x', then you would be correct. However it is SQL*Plus the program that is printing that text and not the execution of the 'update...' SQL statement.

Here is clue as to the differences. Which of the SQL DML statements produce output and which do not? The answer is 'update', 'delete' and 'insert' does not produce output. Issuing a 'select' returns data from the server (hence output) that needs to go somewhere. Assuming your mentioned 'select *...' returns only one row of data (aka singleton select), then you would need a 'select ... into ...' statement. If the select returns multiple rows you'll need to use a cursor select and 'fetch' the returning values into variables.

Of course the actual coding implementation of all this is much more complicated than this simple explanation.

Enoy your endeavors!

rsenn wrote in message <3446BCFD.D1B3DED4_at_capaccess.org>...
>Gentle readers, can someone tell me why one of these statements works
>and the other
>doesn't ? Thanks.
>
>SQL> create or replace procedure test
> 2 is
> 3 begin
> 4 update emp_hist
> 5 set id = 13
> 6 where id = 9999 ;
> 7 end test ;
> 8 /
>
>Procedure created.
>
>SQL> create or replace procedure test
> 2 is
> 3 begin
> 4 select * from emp_hist ;
> 5 -- update emp_hist
> 6 -- set id = 13
> 7 -- where id = 9999 ;
> 8 end test ;
> 9 /
>
>Warning: Procedure created with compilation errors.
>
>
Received on Sat Oct 18 1997 - 00:00:00 CDT