anonymous block completed in oracle SQL

Question

Whilst trying to create a query cursor as follows:

DECLARE CURSOR Query1
IS
SELECT * FROM RACE 
WHERE Race_Time='22-SEP-14 12.00.00.000000000'; 
BEGIN
OPEN Query1;
END;

I get the following error. anonymous block completed. Does anyone know how to fix this? I tried setting the 'SET SERVEROUTPUT ON;' before the declare but this did not seem to fix the error. Thanks in advance!

"anonymous block completed" means your PL/SQL code was successfully executed. — Egor Skriptunoff, Mar 13 '14 at 22:07

score 5 · Answer 1 · answered Apr 03 '15 at 20:05

It seems that dbms_output is turned off

you can see you out put if you put SET SERVEROUTPUT ON; in the beginning of your script.

or you can view dbms_output window (View then DBMS Output) then press the "+" at the top of the Dbms Output window and then select an open database

score 1 · Answer 2 · answered Mar 13 '14 at 22:09

1

"anonymous block completed" means your PL/SQL code was successfully executed. To Display: try using a output statement...

For example:

 BEGIN
    dbms_output.put_line ('Hello, world!');
END;

answered Mar 13 '14 at 22:09

PETTA

101
10

I'm sorry but the example you have given there is just confusing as it has nothing to do with outputting my cursor but simply a line of text. Going by what you're saying to display what is stored in my cursor would it simply be: BEGIN dbmz_output.query1 END; Is this what you're implying? My oracle knowledge is very poor. – user3357925 Mar 13 '14 at 22:14
Look here too: http://stackoverflow.com/questions/7887413/printing-the-value-of-a-variable-in-sql-developer – PETTA Mar 13 '14 at 22:21
One more: http://stackoverflow.com/questions/14803195/plsql-anonymous-block-complete – PETTA Mar 13 '14 at 22:21

score 1 · Answer 3 · answered Mar 13 '14 at 22:36

If you want to control the process in PL/SQL, you could do something like

DECLARE 
  l_race_rec race%rowtype;

  CURSOR Query1
  IS
    SELECT * 
      FROM RACE 
     WHERE Race_Time='22-SEP-14 12.00.00.000000000'; 
BEGIN
  OPEN Query1;
  LOOP
    FETCH query1 INTO l_race_rec;
    EXIT WHEN query1%notfound;

    dbms_output.put_line( l_race_rec.column1 || ' ' || l_race_rec.column2 || ... || l_race_rec.columnN );
  END LOOP;
  CLOSE Query1;    
END;

Unless your assignment requires the use of explicit cursors, though, implicit cursors are likely easier to use

BEGIN
  FOR x IN( SELECT * 
              FROM RACE 
             WHERE Race_Time='22-SEP-14 12.00.00.000000000')
  LOOP
    dbms_output.put_line( x.column1 || ' ' || x.column2 || ... || x.columnN );
  END LOOP;
END;

If you are using SQL*Plus, you can also do something like

VAR rc REFCURSOR;
BEGIN
  OPEN :rc
   FOR SELECT *
         FROM race
        WHERE race_time = '22-SEP-14 12.00.00.000000000'; 
END;
PRINT rc

If race_time is really a timestamp, you should really be comparing a timestamp with another timestamp rather than comparing a timestamp to a string. Use explicit conversion with an explicit format mask to avoid errors due to different sessions having different NLS settings

WHERE race_time = to_timestamp( '22-SEP-14 12.00.00.000000000', 
                                'DD-MON-RR HH24:MI:SS.FF9' )

Of course, I'm not sure why you would use a timestamp in the first place here-- it seems unlikely that you really know the nanosecond at which a race started.

Is there anyway of putting text before the column fetched, for example if i want to put 'Race id:' Before the first collumn fetched is there a way i can do that? Otherwise it's just a number showing up — user3357925, Mar 13 '14 at 22:51
@user3357925 - At that point, you're just concatenating strings so you can put whatever you'd like there. `'Something: ' || x.column1 || ' Something Else: ' || x.column2 || ...` — Justin Cave, Mar 13 '14 at 22:52

anonymous block completed in oracle SQL

3 Answers3