Forum Stats

  • 3,741,459 Users
  • 2,248,431 Discussions
  • 7,861,818 Comments

Discussions

INTO clause inside a FOR loop's SELECT query

Boobal Ganesan
Boobal Ganesan Member Posts: 224
edited Aug 13, 2015 9:01AM in SQL & PL/SQL

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

Boobal Ganesan

Answers

  • Unknown
    edited Aug 11, 2015 9:19AM

    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
    Pavan Kumar Member Posts: 11,903 Gold Crown
    edited Aug 11, 2015 9:25AM

    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
    Boobal Ganesan Member Posts: 224
    edited Aug 11, 2015 9:36AM

    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
    Pavan Kumar Member Posts: 11,903 Gold Crown
    edited Aug 11, 2015 9:38AM

    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
    Boobal Ganesan Member Posts: 224
    edited Aug 11, 2015 9:49AM

    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
    Pavan Kumar Member Posts: 11,903 Gold Crown
    edited Aug 11, 2015 9:57AM

    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 GanesanBoobal Ganesan
  • Boobal Ganesan
    Boobal Ganesan Member Posts: 224
    edited Aug 12, 2015 1:28AM

    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
    Pavan Kumar Member Posts: 11,903 Gold Crown
    edited Aug 12, 2015 1:35AM

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

    Boobal GanesanBoobal Ganesan
  • Boobal Ganesan
    Boobal Ganesan Member Posts: 224
    edited Aug 12, 2015 2:28AM

    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.

    pastedImage_1.png

    Thanks,

    B

  • John Spencer
    John Spencer Member Posts: 8,567
    edited Aug 12, 2015 1:00PM

    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 GanesanBoobal Ganesan
  • Boobal Ganesan
    Boobal Ganesan Member Posts: 224
    edited Aug 13, 2015 3:41AM

    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
    ascheffer Member Posts: 1,898 Gold Trophy
    edited Aug 13, 2015 3:58AM
    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
    Boobal Ganesan Member Posts: 224
    edited Aug 13, 2015 5:01AM

    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
    John Spencer Member Posts: 8,567
    edited Aug 13, 2015 9:01AM
    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

This discussion has been closed.