Forum Stats

  • 3,759,183 Users
  • 2,251,510 Discussions
  • 7,870,526 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
«1

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,904 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,904 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,904 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,904 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
This discussion has been closed.