Q

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.

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close