Can you please tell me what is wrong in the procedure below? A solution would be appreciated.
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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;
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.