|
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;
|
