cursor_for_loop allows PL/SQL SELECT..INTO statement [message #631281] |
Mon, 12 January 2015 06:04 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
The Oracle documentation about Cursor FOR LOOP Statement says,
Quote:select_statement
SQL SELECT statement (not PL/SQL SELECT INTO statement). For select_statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor. However, because select_statement is not an independent statement, the implicit cursor is internal--you cannot reference it with the name SQL.
However, there is no PL/SQL error -
SQL> set serveroutput on
SQL> DECLARE
2 rec emp%rowtype;
3 BEGIN
4 FOR r IN
5 ( SELECT empno INTO rec.empno /*-- strange!*/
6 FROM emp
7 )
8 LOOP
9 dbms_output.put_line('empno = '||rec.empno);
10 END LOOP;
11 END;
12 /
empno =
empno =
empno =
empno =
empno =
empno =
empno =
empno =
empno =
empno =
empno =
empno =
empno =
empno =
PL/SQL procedure successfully completed.
SQL>
Regards,
Lalit
|
|
|
|
|
|
|
Re: cursor_for_loop allows PL/SQL SELECT..INTO statement [message #631293 is a reply to message #631291] |
Mon, 12 January 2015 08:15 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
@Michel, yes, the functionality is intact. When the engine sends SQL statements to the SQL engine, it should have thrown a syntax error, as SELECT INTO is a PL/SQL statement. Since it works, I assume there is something wrong in the switching. Searched MOS, found nothing related. Seems interesting, will try to file a bug. Unfortunately, I can only browse through the support site, perhaps I don't the privilege to report a bug Probably one of you guys could try.
|
|
|
Re: cursor_for_loop allows PL/SQL SELECT..INTO statement [message #631341 is a reply to message #631281] |
Mon, 12 January 2015 15:05 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Mon, 12 January 2015 07:04The Oracle documentation about Cursor FOR LOOP Statement says,
Quote:select_statement
SQL SELECT statement (not PL/SQL SELECT INTO statement). For select_statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor. However, because select_statement is not an independent statement, the implicit cursor is internal--you cannot reference it with the name SQL.
However, there is no PL/SQL error -
And what does INTO has to do with the quote you posted. That quote simply tells you can't use SQL%ROWCOUNT, SQL%NOTFOUND and other implicit cursor attributes. However, I'd agree "can't" is misleading. It simply means you will not get results:
DECLARE
rec dept%rowtype;
BEGIN
FOR r IN
( SELECT deptno INTO rec.deptno
FROM dept
)
LOOP
dbms_output.put_line('Rows fetched = ' || sql%rowcount);
END LOOP;
END;
/
Rows fetched =
Rows fetched =
Rows fetched =
Rows fetched =
PL/SQL procedure successfully completed.
SQL>
Compare it to:
DECLARE
rec dept%rowtype;
cursor v_cur
is
SELECT deptno INTO rec.deptno
FROM dept;
BEGIN
FOR r IN v_cur
LOOP
dbms_output.put_line('Rows fetched = ' || v_cur%rowcount);
END LOOP;
END;
/
Rows fetched = 1
Rows fetched = 2
Rows fetched = 3
Rows fetched = 4
PL/SQL procedure successfully completed.
SQL>
And notice cursor declaration also takes INTO clause. This because SELECT has two forms non-embedded and embedded. So parser will parse embedded SQL but embedded part (INTO clause) will be simply ignored if caller isn't using it:
SQL> variable dname varchar2(20)
SQL> select dname into :dname from dept where deptno = 10;
DNAME
--------------
ACCOUNTING
SQL> print dname
DNAME
--------------------------------
SQL>
SY.
|
|
|
|
Re: cursor_for_loop allows PL/SQL SELECT..INTO statement [message #631614 is a reply to message #631613] |
Fri, 16 January 2015 08:00 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Quote:
perhaps I don't the privilege to report a bug
Actually I was not aware that I can't raise a SR in MOS being a part of Oracle. I can only raise internal bug.
Based on above discussion, I don't think it would be considered as a bug, probably the documentation might be modified to make it verbose.
|
|
|
|