INTO clause inside a FOR loop's SELECT query

Boobal Ganesan

Guys,

When i accidentally happen to place an INTO clause in the select query placed in the FOR loop's cursor section, it worked fine! but the variable N always had NULL value in it. I have posted the script and its output below.

Script:
DECLARE

  n NUMBER;

BEGIN

  FOR i IN

  (SELECT dummy INTO n FROM dual

  )

  LOOP

    dbms_output.put_line('n value is '||NVL(TO_CHAR(n),'Null'));

  END LOOP i;

END;

Output:

anonymous block completed

n value is Null

Is this a defect in PLSQL or is this functionality meant for something really appropriate?

Thanks,

B

Unknown

The PL/SQL should NOT have compiled, so, yes it is a hole in the PL/SQL compiler.

Why you even want do this, where this code always gets one record (the cursor FOR loop is completely redundant)

I don't know.

If you want to do this however, you need to display i.dummy and remove the INTO clause in the SELECT

------------

Sybrand Bakker

Senior Oracle DBA

Pavan Kumar

Hi,

have you test "SELECT dummy INTO n FROM dual"  what does it results to on plain sqlplus command prompt ?

SQL> SELECT dummy  from dual;
D
-
X

1. Is that number ?

2. Not a good code

DECLARE
  n varchar2(2);
BEGIN
  FOR i IN (SELECT dummy INTO n FROM dual
  )
 LOOP
    dbms_output.put_line('n value is '||NVL(TO_CHAR(i.dummy),'Null'));   
  END LOOP;  
END;

SQL> DECLARE
  n varchar2(2);
BEGIN
  FOR i IN  (SELECT dummy FROM dual )
 LOOP
    dbms_output.put_line('n value is '||i.dummy);   
  END LOOP;
END;  2    3    4    5    6    7    8  
  9  /
n value is X

SQL> DECLARE
  n varchar2(2);
BEGIN
  FOR i IN (SELECT dummy INTO n FROM dual
  )
 LOOP
    dbms_output.put_line('n value is '||NVL(TO_CHAR(i.dummy),'Null'));   
  END LOOP;  
END;  2    3    4    5    6    7    8    9  
 10  /
n value is X

- Pavan Kumar N

Boobal Ganesan

Guys,

I have/know the better ways of getting my output, but i just want to know why this even works. And the variable i've used is a number to show that dummy column has a character and it doesnt throw any error when this code runs.

Even though if the select contains more rows this code doesnt fail. PFB.

DECLARE

  n NUMBER;

BEGIN

  FOR i IN

  (SELECT dummy

  INTO n

  FROM

    (SELECT dummy FROM dual

    UNION all

    SELECT dummy FROM dual

    )

  )

  LOOP

    dbms_output.put_line('n value is '||NVL(TO_CHAR(n),'Null'));

  END LOOP i;

END;

Thanks,

B

Pavan Kumar

Hi,

SQL> 
declare
n number;
begin
SELECT dummy into n FROM dual;
end;
/SQL>   2    3    4    5    6  
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

I have/know the better ways of getting my output,

1. Request to test it, does the conversion happened , No

2. During your PL SQL, it just picking up cursor, but into doesn't work

- Pavan Kumar N

Boobal Ganesan

Dear Pavan,

I understand/know that this conversion can never ever happen!

But my question was why does oracle compiler allows an INTO clause inside a FOR loop's SELECT query which actually doesnt work (even with compatible or non-compatible data types)?

Let us get back to the core topic from your code.

Thanks,

B

Pavan Kumar

Hi,

But my question was why does oracle compiler allows an INTO clause which actually doesnt work (even with compatible data types)?

SQL> declare
n number;
begin
SELECT '1' num1 into n FROM dual;
dbms_output.put_line('n value is '|| n);
dbms_output.put_line('n value is '|| to_char(n));
end;
/  2    3    4    5    6    7    8  
n value is 1
n value is 1
PL/SQL procedure successfully completed.
SQL> /
n value is 1
n value is 1

1. IF you use compatible types it should work with implicit conversion

2.  Check example

@ SQL Engine

SQL> select cursor(SELECT '1' num1  FROM dual) test from dual;

TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

N
-
1

select cursor(SELECT '1' num1  into n FROM dual) test from dual;
select cursor(SELECT '1' num1  into n FROM dual) test from dual
                               *
ERROR at line 1:
ORA-01744: inappropriate INTO

3. hope if you can understand,  at into is at plsql - very much valid when done/execute out of cursor (that is SQL engine), if you check normal into clause its returning values and holding structure variable at pl sql level. When you used for loop (its pickup up cursor) but not into since, it's out of scope of sql engine it just return result to cursor -- that is handle. Now into as per plsql valid syntax but not scope with cursor where it is closed and only opened with during implicit fetch (by loop). So it allowed but doesn't given syntax error

- Pavan Kumar N

Boobal Ganesan

Thank you Pavan.

I just got that this INTO clause doesnt push any value into the variable as the CURSOR is still in a CLOSED state and only opens up inside the LOOP.

But shouldnt PLSQL engine throw any error as this scenario is not a valid one?

INTO clause inside a FOR LOOP's cursor statement is a fail is it not? So, PLSQL should decompile this code to avoid users getting confusion isnt it?

Thanks,

B

Pavan Kumar

I accept it should raise error , logically that's not correct, let me check the syntax in documentation .

Boobal Ganesan

Pavan,

I just had to look into the syntax and it stated that the PLSQL INTO clause should not be used. So, this is a defect to be raised to the ORACLE team to fix or someone might have already got this into their attention. and the below is my banner information.

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

"CORE    11.2.0.3.0    Production"

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

A snippet from the Oracle documentation.

Thanks,

B

John Spencer

The earliest version I have to test with is 9.2.0.7, I also have 10.2.0.3, 11.2.0.3 and 11.2.0.4 and all versions exhibit exactly the same behaviour.  If you initialize the variable to an actual value, that value will be displayed by the dbms_output call.  My guess is that the compiler either ignores or parses away the into clause at some point in the process.  This also seems to be true for explicit cursor, that is, this works:

declare
  l_num number;
  l_dummy varchar2(2);
  cursor c is
  select dummy into l_num from dual;
begin
  l_num := 1;
  open c;
  fetch c into l_dummy;
  dbms_output.put_line('Num is: '||NVL(to_char(l_num), 'NULL'));
  dbms_output.put_line('Rec is: '||l_dummy);
  close c;
end;
/

Interestingly, it does seem to parse the into clause, at least initially, both versions, implicit and explicit cursor, fail to compile if you do:

SQL> declare
  2    l_num number;
  3    l_dummy varchar2(2);
  4    cursor c is
  5    select dummy into s from dual;
  6  begin
  7    l_num := 1;
  8    open c;
  9    fetch c into l_dummy;
10    dbms_output.put_line('Num is: '||NVL(to_char(l_num), 'NULL'));
11    dbms_output.put_line('Rec is: '||l_dummy);
12    close c;
13* end;
SQL> /
  select dummy into s from dual;
                    *
ERROR at line 5:
ORA-06550: line 5, column 22:
PLS-00201: identifier 'S' must be declared
ORA-06550: line 5, column 24:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, column 4:
PL/SQL: SQL Statement ignored

John

Boobal Ganesan

Thanks for your analysis John, but i dont think the parsing happens here.

I tried to change it into a procedure and it failed in the compilation itself. Thus the code has failed its basic semantic check and got decompiled as the parsing happens only during the run time.

The variable S was actually missing in the below code and the compiler found it and failed the compilation.

So according to your initial statement, the parsing itself is not happening here.

CREATE OR REPLACE PROCEDURE PROC

IS

  l_num   NUMBER;

  l_dummy VARCHAR2(2);

  CURSOR c

  IS

    SELECT dummy INTO s FROM dual;

BEGIN

  l_num := 1;

  OPEN c;

  FETCH c INTO l_dummy;

  dbms_output.put_line('Num is: '||NVL(TO_CHAR(l_num), 'NULL'));

  dbms_output.put_line('Rec is: '||l_dummy);

  dbms_output.put_line('Rec is: '||S);

  CLOSE c;

END;

/

PROCEDURE PROC compiled

Errors: check compiler log

Thanks,

B

ascheffer

declare
  l_v number;
  cursor c_x is select 1, 2 into l_v from dual;
begin
  null;
end;
Error at line 1
ORA-06550: line 3, column 38:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 3, column 17:
PL/SQL: SQL Statement ignored

Boobal Ganesan

Thanks.

Even here the basic syntax check is failed and thus the code gets decompiled.

But the ultimate question is, why does oracle compiler allows an INTO statement in the explicit/implicit cursor's SELECT query with no purpose?

Thank you,

B

John Spencer

2958780 wrote:

Thanks for your analysis John, but i dont think the parsing happens here.

I tried to change it into a procedure and it failed in the compilation itself. Thus the code has failed its basic semantic check and got decompiled as the parsing happens only during the run time.

The variable S was actually missing in the below code and the compiler found it and failed the compilation.

So according to your initial statement, the parsing itself is not happening here.

CREATE OR REPLACE PROCEDURE PROC
IS
  l_num   NUMBER;
  l_dummy VARCHAR2(2);
  CURSOR c
  IS
    SELECT dummy INTO s FROM dual;
BEGIN
  l_num := 1;
  OPEN c;
  FETCH c INTO l_dummy;
  dbms_output.put_line('Num is: '||NVL(TO_CHAR(l_num), 'NULL'));
  dbms_output.put_line('Rec is: '||l_dummy);
  dbms_output.put_line('Rec is: '||S);
  CLOSE c;
END;
/

PROCEDURE PROC compiled
Errors: check compiler log



Thanks,
B

Yes, trying to create the procedure with the "missing" variable s in the into clause will fail, that is what my anonymous block showed.  If you check the errors you will see two errors PLS-00201: identifier 'S' must be declared, one for each usage of s.  However, if you remove the references to s and use SELECT dummy INTO l_num FROM dual; it will compile and run successfully.

As I said, I believe that statement is parsed with the into clause intact, but in the actual compiled version that runs the into clause is either ignored or stripped out.  As to why it works that way, you would need to ask the people who programmed the PL/SQL compiler.  My guess would be that it gets stripped out of the compiled version because it is syntactically meaningless, not semantically incorrect.

John