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. Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation