CODEWITHSUNDEEP
oracle-database
user376237
user376237

Reputation: 27

Why can't I execute an anonymous block in PL / SQL?

I want to make a procedure in an anonymous block, which will display information about all database sessions that have unclosed transactions from the data dictionary views

BEGIN 
    select s.sid, s.serial#, s.username from v$transaction t inner join 
    v$session s on t.addr = s.taddr; 
END;

But when I try to call it, nothing happens.

enter image description here

Upvotes: 0

Views: 1047

Answers (2)

EdStevens
EdStevens

Reputation: 3872

You have to follow your anonymous block with a '/', to tell oracle to stop parsing and start executing.

That said, your code still will just throw an error because you need to SELECT .... INTO some variable to hold the results of your SELECT.

And that said, even if you provide the correct variables and INTO syntax, your code still will not do what you expect, because PL/SQL is a server-side process that has no ability to display output. The closest it can come is to use DBMS_OUTPUT.PUT_LINE, but that just writes to a buffer that is returned, upon completion of the entire procedure, to the client. It is then up to the client to decide what do do with it.

And all that said, why do you think you need an anonymous PL/SQL block just to execute a simple query?

And all that said, screen shots of terminal sessions are very hard to read. It's actually very easy to simply copy the text from the terminal, and paste that into your postings. Then select the pasted text and click the 'code sample' icon of the edit window menu to set it off as formatted code.

Upvotes: 3

Viktor Török
Viktor Török

Reputation: 1319

If you really need an anonymous block, you can use the following code:

begin
  for r_rows in (
    select s.sid, s.serial#, s.username
      from v$transaction t
      inner join v$session s on t.addr = s.taddr) loop
      
    dbms_output.put_line(r_rows.sid);
  end loop;
end;
/

You must write the other columns in the dbms_output.put_line statement.

Upvotes: 2

Related Questions