PLS-00103 error for procedure

Question

I have 2 servers (one for testing, one for production), both have the following Oracle packages (identical output on both of them for SELECT * FROM V$VERSION; :

Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

The strange thing is that it works just fine on one server and it gives these errors on the other one... Where should I look? It seems it's a server configuration problem.

I'm trying to compile this procedure:

CREATE OR REPLACE PROCEDURE P_A1 AS  
  NUMAR INTEGER := 0;  
  CURSOR A1_C3 IS  
    SELECT   
(SELECT SUM(D1.A_PAY) FROM A1_A D1 WHERE D1.YR_R = D.YR_R AND D1.MON_R = D.MON_R) x_A  
      FROM APP_COMPANY_ALL CO, A1_A D  
     WHERE D.YR_R = y.APPL_admin.F$APPL_YEAR  
       AND D.MON_R = y.APPL_admin.F$APPL_MONTH  
       AND d.cif=SUBSTR(RTRIM(CO.c_fisc),3);  
  V_A1 A1_C3%ROWTYPE;  

 BEGIN  
  NULL;  
END;  

I get the folowing errors on one of the servers:
Compilation errors for PROCEDURE P_A1

Error: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

          ( - + case mod new not null others <an identifier>
          <a double-quoted delimited-identifier> <a bind variable> avg
          count current exists max min prior sql stddev sum variance
          execute forall merge time timestamp interval date
          <a string literal with character set specification>
          <a number> <a single-quoted SQL string> pipe
Text: (SELECT SUM(D1.A_PAY) FROM A1_A D1 WHERE D1.YR_R = D.YR_R AND D1.MON_R = D.MON_R) x_A

Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:

          . ( * @ % & - + ; / at for mod rem <an exponent (**)> and or
          group having intersect minus order start union where connect
          ||
Text: (SELECT SUM(D1.A_PAY) FROM A1_A D1 WHERE D1.YR_R = D.YR_R AND D1.MON_R = D.MON_R) x_A

---

The thing is that, as I said, it works on the test server, but not on the production server. The Oracle versions are identical. I am pretty sure it's a configuration option that's causing this problem. But I don't know where to look for a solution.

The "Select ( Select ..." works just fine if it's not a scalar subquery. It fails when it's inside the cursor. Why doesn't it work on the production server?

Answer 1

This may just have been a cut-n-paste issue, but in the original post the cursor starts with

SELECT (SELECT...

which I don't think will work. I suggest that you try the following:

CREATE OR REPLACE PROCEDURE P_A1 AS
  NUMAR INTEGER := 0;
  CURSOR A1_C3 IS
    SELECT x_A.PAY_SUM
      FROM (SELECT SUM(D1.A_PAY) AS PAY_SUM
              FROM A1_A D1
              WHERE D1.YR_R = D.YR_R AND
                    D1.MON_R = D.MON_R) x_A
      INNER JOIN A1_A A D
        ON (D.YR_R = y.APPL_admin.F$APPL_YEAR AND
            D.MON_R = y.APPL_admin.F$APPL_MONTH)
      INNER JOIN APP_COMPANY_ALL CO
        ON (SUBSTR(RTRIM(CO.c_fisc),3) = D.CIF);
  V_A1 A1_C3%ROWTYPE;
BEGIN
  NULL;
END P_A1; 

Share and enjoy.

Answer 2

Might be worth checking a couple initialization parameters in terms of comparing the two servers:

plsql_optimize_level compatible

Differences in these could yield different behavior between servers. You can validate the suggestion that it might be the parser issue by changing it to dynamic SQL along the lines of:

open my_cursor for 
     'SELECT   
           (SELECT SUM(D1.A_PAY) FROM A1_A D1 WHERE D1.YR_R = D.YR_R AND D1.MON_R = D.MON_R) x_A  
      FROM APP_COMPANY_ALL CO, A1_A D  
     WHERE D.YR_R = :1 
       AND D.MON_R = :2
       AND d.cif=SUBSTR(RTRIM(CO.c_fisc),3)'  using y.APPL_admin.F$APPL_YEAR , y.APPL_admin.F$APPL_MONTH