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 last published in September 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close