Oracle Function compilation error - Cannot find the reason of a PLS-00103

Question

I am getting the errror:

Line/Col: 24/13
PLS-00103: Encountered the symbol ";" when expecting one of the following: (

When trying to compile the function:

create or replace function  xml_sum (innXML XMLType, outXML XMLType) RETURN number
  IS
 sum NUMBER := 0;
  BEGIN
        FOR j IN    
            (SELECT y.feature, rownum
                FROM    XMLTABLE
                    ('//FeatureVector/feature'
                      PASSING outXML
                      COLUMNS
                       feature  NUMBER  PATH '.') y)
                LOOP                    
                           FOR i IN
                      (SELECT x.feature, rownum rn
                        FROM    XMLTABLE
                            ('//FeatureVector/feature'
                              PASSING innXML
                              COLUMNS
                               feature  NUMBER  PATH '.') x WHERE rn = j.rownum) 
                    LOOP
                            sum := i.feature + j.feature;                    
                END LOOP;
        END LOOP;
  RETURN sum;
  END;
 /

By the error it seems that I am missing a ";" but I cannot find where it is being missed.

Could someone point it out? It would certainly help!

Thanks in advance!!

Change variable name sum to something other, let's say v_sum_feature for start. — Ponder Stibbons
– Ponder Stibbons, Commented Oct 26, 2017 at 12:15

XING · Accepted Answer · 2017-10-26 12:14:08Z

You are using a Oracle reserved Key word SUM. Change it something else and your issue is resolved.

Also at this place:

WHERE rn = j.ROWNUM

you are referrring a column aliasing in where clause directly which is not allowed. Either you need a outer query or you can use it directly. You can see how i used it below:

So your code becomes:

CREATE OR REPLACE FUNCTION xml_sum (innXML XMLTYPE, outXML XMLTYPE)
   RETURN NUMBER
IS
   ToT_SUM   NUMBER := 0;
BEGIN
   FOR j
      IN ( SELECT y.feature, ROWNUM
             FROM XMLTABLE ('//FeatureVector/feature'
                            PASSING outXML
                            COLUMNS feature NUMBER PATH '.') y)
   LOOP
      FOR i
         IN ( SELECT x.feature, ROWNUM rn
                FROM XMLTABLE ('//FeatureVector/feature'
                               PASSING innXML
                               COLUMNS feature NUMBER PATH '.') x
               WHERE ROWNUM= j.ROWNUM)
      LOOP
         ToT_SUM := i.feature + j.feature;
      END LOOP;
   END LOOP;

   RETURN ToT_SUM;
END;
/

MT0 · Accepted Answer · 2017-10-26 12:46:23Z

Your solution will not work as:

SELECT *
FROM   any_table
WHERE  ROWNUM = 2

will never return any rows.

Apart from that, @XING noted a couple of other issues and you are overwriting the value in the sum variable in each iteration so you are only getting the last value.

You should be able to rewrite the procedure to remove the need for cursor loops:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE FUNCTION xml_sum(
  innXML XMLType,
  outXML XMLType
) RETURN number
IS
  total NUMBER := 0;
BEGIN
  SELECT SUM( y.feature + x.feature )
  INTO   total
  FROM   XMLTABLE(
           '//FeatureVector/feature'
           PASSING outXML
           COLUMNS rn       FOR ORDINALITY,
                   feature  NUMBER  PATH '.'
         ) y
         INNER JOIN
         XMLTABLE(
           '//FeatureVector/feature'
           PASSING innXML
           COLUMNS rn       FOR ORDINALITY,
                   feature  NUMBER  PATH '.'
         ) x
         ON ( x.rn = y.rn );

  RETURN total;
END;
/

Query 1:

SELECT xml_sum(
         XMLTYPE( '<FeatureVector><feature>1</feature><feature>2</feature></FeatureVector>' ),
         XMLTYPE( '<FeatureVector><feature>1</feature><feature>2</feature></FeatureVector>' )
       ) AS total
FROM   DUAL

Results:

| TOTAL |
|-------|
|     6 |

Collectives™ on Stack Overflow

Oracle Function compilation error - Cannot find the reason of a PLS-00103

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related