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!
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
"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;
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.
'Something: ' || x.column1 || ' Something Else: ' || x.column2 || ...