Ask the Expert

SQL vs. PL/SQL

Can you please tell me what is wrong in the procedure below? A solution would be appreciated.
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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: