CeDeT: OIF (Oracle is funny (that way)) #4

implementation restriction in PL/SQL

Using Native Dynamic SQL (NDS) in PL/SQL enables you (amongst other things) to open a cursor based on SQL in a string (using the OPEN-FOR Statement). This capability has not (yet?) made its way into Cursor FOR Loops - you can substitute a subquery but not (yet?) as a string:

The test

SQL> SELECT * FROM v$version WHERE ROWNUM<=2;

BANNER                                                                          
----------------------------------------------------------------                
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production                      
PL/SQL Release 8.1.7.2.0 - Production                                           

SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> BEGIN
  2    FOR v IN (SELECT * FROM v$version WHERE ROWNUM<=2) LOOP
  3      DBMS_OUTPUT.PUT_LINE(v.banner);
  4    END LOOP;
  5  END;
  6  /
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production                      
PL/SQL Release 8.1.7.2.0 - Production                                           

PL/SQL-procedure er udført.

SQL> BEGIN
  2    FOR v IN 'SELECT * FROM v$version WHERE ROWNUM<=2' LOOP
  3      DBMS_OUTPUT.PUT_LINE(v.banner);
  4    END LOOP;
  5  END;
  6  /
  FOR v IN 'SELECT * FROM v$version WHERE ROWNUM<=2' LOOP
           *
FEJL i linie 2:
ORA-06550: line 2, column 12: 
PLS-00999: implementation restriction (may be temporary)  
ORA-06550: line 2, column 3: 
PL/SQL: Statement ignored 


SQL> DECLARE
  2    str VARCHAR2(2000):='SELECT * FROM v$version WHERE ROWNUM<=2';
  3  BEGIN
  4    FOR v IN str LOOP
  5      DBMS_OUTPUT.PUT_LINE(v.banner);
  6    END LOOP;
  7  END;
  8  /
  FOR v IN str LOOP
           *
FEJL i linie 4:
ORA-06550: line 4, column 12: 
PLS-00306: wrong number or types of arguments in call to 'STR' 
ORA-06550: line 4, column 3: 
PL/SQL: Statement ignored 


SQL> DECLARE
  2    str VARCHAR2(2000):='SELECT * FROM v$version WHERE ROWNUM<=2';
  3    TYPE vCurType IS REF CURSOR;
  4    vCur vCurType;
  5    v v$version%ROWTYPE;
  6  BEGIN
  7    OPEN vCur FOR str;
  8    LOOP
  9      FETCH vCur INTO v;
 10      EXIT WHEN vCur%NOTFOUND;
 11      DBMS_OUTPUT.PUT_LINE(v.banner);
 12    END LOOP;
 13    CLOSE vCur;
 14  END;
 15  /
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production                      
PL/SQL Release 8.1.7.2.0 - Production                                           

PL/SQL-procedure er udført.