PL/SQL compiling problem...

[Taoism]

I'm pasting a proc from a package I am writing below, for some reason PL/SQL won't compile the version that is in the comment block... the other version does compile.

Compile error is:

LINE/COL ERROR
-------- -----------------------------------------------------------------
234/41   PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         , from

The offending line selected from USER_SOURCE is:

TEXT
--------------------------------------------------
                      ROW_NUMBER() OVER (ORDER BY
stab_yy ASC) AS rn

Here is the proc:

    PROCEDURE sgovmrgn3(p_stab_yy            IN scc_gov_mrgn_nsr.stab_yy%TYPE,
                        p_prov_code          IN scc_gov_mrgn_nsr.prov_code%TYPE, 
                        p_prov_mncpty_code   IN scc_gov_mrgn_nsr.prov_mncpty_code%TYPE, 
                        p_gov_mrgn_type_code IN scc_gov_mrgn_nsr.gov_mrgn_type_code%TYPE, 
                        p_gov_mrgn_code      IN scc_gov_mrgn_nsr.gov_mrgn_code%TYPE,
                        p_non_cal_op         IN NUMBER,
                        p_gov_mrgn_amt       IN OUT scc_cur) IS 
    v_mncpty scc_gov_mrgn_nsr.prov_mncpty_code%TYPE;
    v_non_cal   PLS_INTEGER;
    BEGIN
      IF NOT use_mncpty_in_select(p_stab_yy,
                                  p_prov_code,
                                  p_prov_mncpty_code,
                                  p_gov_mrgn_type_code,
                                  p_gov_mrgn_code) THEN
        v_mncpty:=0;
      ELSE
        v_mncpty:=p_prov_mncpty_code;
      END IF;
      
      IF p_non_cal_op <> 0 THEN
        v_non_cal:=0;
      ELSE
        v_non_cal:=1;
      END IF;

         OPEN p_gov_mrgn_amt FOR
      -- This works
      --/*
       SELECT middle.gov_inv_mrgn_amt
         FROM (SELECT inner.*,
                      ROWNUM AS rn
                 FROM (SELECT gov_inv_mrgn_amt
                         FROM scc_gov_mrgn_nsr
                        WHERE stab_yy            = p_stab_yy
                          AND prov_code          = p_prov_code
                          AND prov_mncpty_code   = v_mncpty
                          AND gov_mrgn_type_code = p_gov_mrgn_type_code
                          AND gov_mrgn_code      = p_gov_mrgn_code
                        ORDER BY stab_yy ASC
                      ) inner
               ) middle
        WHERE middle.rn 
      BETWEEN 1 + v_non_cal 
          AND 5 + v_non_cal;
      --*/
      
      -- this block won't compile... WHY?
      -- this block will work in SQL*Plus worksheet
      -- the compile error centres around the 
      -- (ORDER BY stab_yy ASC) subclause
      -- add double dashes below to enable block
      /*
       SELECT inner.gov_inv_mrgn_amt 
         FROM (SELECT gov_inv_mrgn_amt,
                      ROW_NUMBER() OVER (ORDER BY stab_yy ASC) AS rn
                 FROM scc_gov_mrgn_nsr
                WHERE stab_yy            = p_stab_yy
                  AND prov_code          = p_prov_code
                  AND prov_mncpty_code   = p_prov_mncpty_code
                  AND gov_mrgn_type_code = p_gov_mrgn_type_code
                  AND gov_mrgn_code      = p_gov_mrgn_code) inner
        WHERE inner.rn 
      BETWEEN 1+v_non_cal 
          AND 5+v_non_cal;
      --*/
    END sgovmrgn3;

Can anyone shed some light for me as to why the query works fine as a direct query in SQL*Plus Worksheet as a straight SQL query, but if I put it in a PL/SQL block I get an error?

Thanks in advance for any replies.

Cheers,
Keith.

[slimdave]

My quick guess is that you are using 8i, where PL/SQL uses a SQL engine that does not recognise the analytical function format. You can use DBMS_SQL or execute immediate to make it use the regular SQL engine. What you lose in performance by that technique you'd probably recover in the speed of the SQL statement itself

[Taoism]

Originally posted by slimdave
My quick guess is that you are using 8i, where PL/SQL uses a SQL engine that does not recognise the analytical function format. You can use DBMS_SQL or execute immediate to make it use the regular SQL engine. What you lose in performance by that technique you'd probably recover in the speed of the SQL statement itself

Dave,

Thanks for the reply.

Yep,we use 8i... I do my initial development with 8iPE on my local computer.

...using 8i, where PL/SQL uses a SQL engine that does not recognise the analytical function format.

Well, I had a feeling it was something like that, but I couldn't locate anything to confirm it. Our upgrade path indicates that we won't be moving to 9i until next year some time.... oh well...

I doubt it would be worth it to execute it as dynamic SQL since it is only getting 6 rows at most on the inner select. Thank you for your insight though

Cheers,
Keith.