DECLARE arr_var_seq NUMBER; arr_measure NUMBER; BEGIN SELECT /*+ ALL_ROWS */ var_seq, NVL ( ( SELECT NVL ( TO_CHAR (a.measure_data), 'NULL' ) FROM fsb_accum_data a WHERE a.var_seq = v.var_seq AND week_code = 1229 ), 'NULL' ) AS measure_data INTO arr_var_seq, arr_measure FROM fsb_variables v; dbms_output.put_line (arr_var_seq || ' = var_seq'); dbms_output.put_line (arr_measure || ' = measure'); END;Please don't tell me to change my SQL statement as is correct according to my output. If I run just the SQL statement it is working fine, and failing if it is in the PL/SQL block.
If you're trying this in Oracle version 8, you're probably getting the error: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: .... As I mentioned in a previous answer, I've seen no guarantee that scalar subquery expressions (such as the subquery from fsb_accum_data in your code) will work at all in Oracle 8. They do happen to work in SQL*Plus, so if you cut out the entire SELECT statement (minus the INTO clause, of course) it will run in SQL*Plus. Unfortunately, PL/SQL does not always behave exactly the same as SQL*Plus: PL/SQL sometimes lags behind. This looks like one of those cases.
If you're trying this in Oracle version 9, you're probably getting the error: ORA-01422: exact fetch returns more than requested number of rows Define a cursor based on your query and use a "FOR ... LOOP" block to process one row of results at a time. I did this below, replacing the scalar subquery expression with an inner join so that it will work in Oracle version 8 (and earlier).
DECLARE CURSOR fsb_cursor IS SELECT /*+ ALL_ROWS */ v.var_seq, NVL ( TO_CHAR (a.measure_data), 'NULL' ) AS measure_data FROM fsb_accum_data a, fsb_variables v WHERE a.var_seq (+) = v.var_seq AND ( week_code = 1229 OR a.var_seq IS NULL ); BEGIN FOR fsb_record IN fsb_cursor LOOP dbms_output.put_line (fsb_record.var_seq || ' = var_seq'); dbms_output.put_line (fsb_record.measure_data || ' = measure'); END LOOP; END;
This was first published in September 2003