Declare cursor in sqlplus command mode

Question

I have an SQL file which uses declares a cursor and I am running it using @abc, but it did not execute all statements and waiting without returning to command prompt. It did not proceed after declare cursor statement. When I tried to run the declare cursor statement in command mode, the same problem is happening again. I am able to return to SQL priompt only after pressing Ctrl + C. I am very new to SQL world. Though this could be a basic mistake, I am not able to find out the solution in any site. Any help is greatly appreciated.

SQL> DECLARE CURSOR id_cursor IS SELECT id FROM user_names WHERE dept_no = 1002 
AND BITAND(flags, 4) = 4 AND time_created BETWEEN 1137974400 AND 1326067199;
  2
  3
  4  ;
  5
  6

I have BEGIN END for the usage of cursor. Problem is that I missed / and ended up trying only declare statement which I posted here. — Mohan P
– Mohan P, Commented Jan 20, 2012 at 14:45

Vincent Malgrat · Accepted Answer · 2012-01-20 14:27:47Z

6

All DECLARE and BEGIN blocks in SQL*Plus need to be ended with a / on a new empty line:

SQL> DECLARE
  2    CURSOR c IS SELECT 1 FROM DUAL;
  3  BEGIN
  4     NULL;
  5  END;
  6  /

PL/SQL procedure successfully completed.

Without this / SQL*Plus has no way to know that your statement has ended (so in your example it waits for user input).

answered Jan 20, 2012 at 14:27

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Doyle · Accepted Answer · 2012-01-20 14:31:10Z

3

After the ; type a / and enter. This will run your PL/SQL statement. But the sample you've given does nothing but declare a cursor. You must then use it like so:

declare
  cursor ID_CURSOR is
    select ID
      from USER_NAMES
     where DEPT_NO = 1002
           and bitand(FLAGS, 4) = 4
           and TIME_CREATED between 1137974400 and 1326067199;
begin
  for REC in ID_CURSOL loop
    <do something with your data>;
  end loop;
end;
/

answered Jan 20, 2012 at 14:31

John Doyle

7,8235 gold badges35 silver badges40 bronze badges

1 Comment

Mohan P Over a year ago

I had written similar script only with a cursor declared and used. But I did not know that the statement should end with /. So I took the declare statement alone and tried to run it directly in sqlplus propmt.

Collectives™ on Stack Overflow

Declare cursor in sqlplus command mode

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related