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?

as a side note, you are aware you are running a release that is no longer supported..... — steve, Aug 18 '13 at 03:51
Try pasting the whole query from the cursor outside the stored procedure and see if it works. Wild guess - permission issue in production like missing grants on tables? — wrschneider, Oct 21 '13 at 00:18
To check whether it's a configuration issue, try running `select * from v$parameter` on both databases and compare the output of both queries. — Frank Schmitt, Feb 20 '14 at 11:18
I suspect your test environment has been upgraded to a different version of Oracle. — abhi, Apr 22 '14 at 20:37

score 0 · Answer 1 · answered Apr 18 '17 at 05:28

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

score 0 · Answer 2 · answered Apr 18 '11 at 14:41

0

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.

answered Apr 18 '11 at 14:41

Bob Jarvis - Слава Україні

48,992
9
77
110

1

In Oracle SQL, the query starting with "SELECT ( SELECT..." is okay. However in Oracle 9, SQL and PL/SQL had nasty differences between their implementations of DML statements. That's the only reason I can see why "SELECT ( SELECT..." should work. Starting with Oracle 10, it should be okay in both SQL and PL/SQL. – Codo Apr 18 '11 at 15:03
@Codo: I stand corrected. I'd still be curious to know if the version of the query in the response above corrected the issue. – Bob Jarvis - Слава Україні Apr 18 '11 at 15:43
1

Tried in Oracle 10, Oracle 11, both in PL/SQL and SQL and it is always errors when using "SELECT (SELECT..." construction. And it is undocumented that Oracle can handle this. Possibly it's true, that your test server is unpatched somehow to allow this work? BTW, why you need this strange "select (select.. " construction? – Alexo Po. Jan 07 '16 at 22:21
This is just a subquery as part of the select clause, nothing unusual. https://stackoverflow.com/questions/20557899/how-does-subquery-in-select-statement-work-in-oracle – Erich Kitzmueller Aug 21 '20 at 14:04
https://docs.oracle.com/cd/E11882_01/server.112/e41084/expressions013.htm#SQLRF52093 states "You can use a scalar subquery expression in most syntax that calls for an expression (expr)." which includes the select_list of a select statement. – Erich Kitzmueller Aug 21 '20 at 14:12

PLS-00103 error for procedure

2 Answers2