12 Replies Latest reply on Dec 10, 2019 4:20 AM by Quanwen Zhao

    ORA-19114: XPST0003 - error during parsing the XQuery expression:

    Quanwen Zhao

      Hi my guys and friends ,

       

      Why showing this error when I run the following PL/SQL code?

       

      DECLARE
         v_sql VARCHAR2(2000);
      BEGIN
         v_sql := 'CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE(''1 TO 10000'')';
         EXECUTE IMMEDIATE v_sql;
      END;
      /
      

       

      SQL> DECLARE
        2     v_sql VARCHAR2(2000);
        3  BEGIN
        4     v_sql := 'CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE(''1 TO 10000'')';
        5     EXECUTE IMMEDIATE v_sql;
        6  END;
        7  /
      DECLARE
      *
      ERROR at line 1:
      ORA-19114: XPST0003 - error during parsing the XQuery expression:
      LPX-00801: XQuery syntax error at 'TO'
      1   1 TO 10000
      -    ^
      ORA-06512: at line 5
      

       

      But separately running this SQL - "CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE('1 TO 10000');" on SQL*Plus has no problem.

       

      Could you give me some suggestion? Thanks in advance!

       

      Best Regards

      Quanwen Zhao

        • 1. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
          Gaz in Oz

          Use '1 to 1000', i.e. "to" in lowercase : o)

          SQL> SELECT ROWNUM AS id FROM XMLTABLE('1 TO 10000');

          SELECT ROWNUM AS id FROM XMLTABLE('1 TO 10000')

                  *

          ERROR at line 1:

          ORA-19114: XPST0003 - error during parsing the XQuery expression:

          LPX-00801: XQuery syntax error at 'TO'

          1   1 TO 10000

          -    ^

           

          SQL> SELECT ROWNUM AS id FROM XMLTABLE('1 to 10000');

           

                  ID

          ----------

                   1

                   2

          ...

                9997

                9998

                9999

               10000

          1 person found this helpful
          • 2. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
            mathguy

            Quanwen Zhao wrote:

             

             

            But separately running this SQL - "CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE('1 TO 10000');" on SQL*Plus has no problem.

             

             

            I don't believe your claim - that the separately run SQL has no problem. On my system, it has exactly the same problem as you report for the PL/SQL block. Obviously, the reason is the same (what Gaz has already pointed out).

            1 person found this helpful
            • 3. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
              Quanwen Zhao

              Hello mathguy ,

               

              I'm very sorry, yes, you're right!

               

              Thank you for guiding and reminding me.

               

              Best Regards

              Quanwen Zhao

              • 4. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
                Quanwen Zhao

                Hi Gaz in Oz ,

                 

                Thanks for pointing out my mistake.

                 

                SQL> DECLARE
                  2     v_sql VARCHAR2(2000);
                  3  BEGIN
                  4     v_sql := 'CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE(''1 to 10000'')';
                  5     EXECUTE IMMEDIATE v_sql;
                  6  END;
                  7  /
                
                
                PL/SQL procedure successfully completed.
                

                 

                But if I use a parameter name to replace the maximum value I want to generate this time it has shown another error making me confused.

                 

                CREATE OR REPLACE PROCEDURE crt_tab_t (l_num IN NUMBER)
                AS
                   v_sql VARCHAR2(2000);
                BEGIN
                   v_sql := 'CREATE TABLE t AS SELECT ROWNUM AS id FROM XMLTABLE(''1 to l_num'')';
                   EXECUTE IMMEDIATE v_sql;
                END;
                /
                

                 

                SQL> CREATE OR REPLACE PROCEDURE crt_tab_t (l_num IN NUMBER)
                  2  AS
                  3     v_sql VARCHAR2(2000);
                  4  BEGIN
                  5     v_sql := 'CREATE TABLE t AS SELECT ROWNUM AS id FROM XMLTABLE(''1 to l_num'')';
                  6     EXECUTE IMMEDIATE v_sql;
                  7  END;
                  8  /
                
                
                Procedure created.
                
                
                SQL> execute crt_tab_t(10000);
                BEGIN crt_tab_t(10000); END;
                
                
                *
                ERROR at line 1:
                ORA-19228: XPST0008 - undeclared identifier: prefix '.' local-name ''
                ORA-06512: at "SYS.CRT_TAB_T", line 6
                ORA-06512: at line 1
                

                 

                or,

                 

                CREATE OR REPLACE PROCEDURE crt_tab_t (l_num IN NUMBER)
                AS
                   v_sql VARCHAR2(2000);
                BEGIN
                   v_sql := q'[CREATE TABLE t AS SELECT ROWNUM AS id FROM XMLTABLE('1 to l_num')]';
                   EXECUTE IMMEDIATE v_sql;
                END;
                /
                

                 

                SQL> CREATE OR REPLACE PROCEDURE crt_tab_t (l_num IN NUMBER)
                  2  AS
                  3     v_sql VARCHAR2(2000);
                  4  BEGIN
                  5     v_sql := q'[CREATE TABLE t AS SELECT ROWNUM AS id FROM XMLTABLE('1 to l_num')]';
                  6     EXECUTE IMMEDIATE v_sql;
                  7  END;
                  8  /
                
                
                Procedure created.
                
                
                SQL> 
                SQL> execute crt_tab_t(10000);
                BEGIN crt_tab_t(10000); END;
                
                
                *
                ERROR at line 1:
                ORA-19228: XPST0008 - undeclared identifier: prefix '.' local-name ''
                ORA-06512: at "SYS.CRT_TAB_T", line 6
                ORA-06512: at line 1
                

                 

                Best Regards

                Quanwen Zhao

                • 5. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
                  cormaco

                  Here is one way to get what you want:

                  CREATE OR REPLACE PROCEDURE crt_tab_t (l_num IN NUMBER)  
                  AS  
                     v_sql VARCHAR2(2000);  
                  BEGIN  
                     v_sql := REPLACE(q'[CREATE TABLE t AS SELECT ROWNUM AS id FROM XMLTABLE('1 to l_num')]','l_num',TO_CHAR(l_num));  
                     EXECUTE IMMEDIATE v_sql;
                  END;  
                  
                  
                  1 person found this helpful
                  • 6. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
                    Gaz in Oz

                    You have forgotten one of the (many) golden rules wrt dynamic SQL.

                    . Use dbms_output.put_line to debug your code!

                    SQL> ed

                    Wrote file afiedt.buf

                     

                      1  declare

                      2    v_sql VARCHAR2(2000);

                      3    l_num NUMBER := 10;

                      4  begiN

                      5    V_sql := 'CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE(''1 TO l_num'')';

                      6    -- execute immediate v_sql;

                      7    dbms_output.put_line(v_sql);

                      8* end;

                    SQL> /

                    CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE('1 TO l_num')

                     

                    PL/SQL procedure successfully completed.

                    As you can see, that'll never work.

                    One way to do it would be to concatenate the input variable.

                    For example:

                    SQL> ed

                    Wrote file afiedt.buf

                     

                      1  declare

                      2    v_sql VARCHAR2(2000);

                      3    l_num NUMBER := 10000;

                      4  begiN

                      5    V_sql := 'CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE(''1 to '||l_num||''')';

                      6    -- execute immediate v_sql

                      7    dbms_output.put_line(v_sql);

                      8* end;

                    SQL> /

                    CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE('1 to 10000')

                     

                    PL/SQL procedure successfully completed.

                    1 person found this helpful
                    • 7. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
                      mathguy

                      Quanwen Zhao wrote:

                       

                      But if I use a parameter name to replace the maximum value I want to generate this time it has shown another error making me confused.

                       

                      1. CREATE OR REPLACE PROCEDURE crt_tab_t(l_num IN NUMBER)
                      2. AS
                      3. v_sql VARCHAR2(2000);
                      4. BEGIN
                      5. v_sql:='CREATE TABLE t AS SELECT ROWNUM AS id FROM XMLTABLE(''1 to l_num'')';
                      6. EXECUTE IMMEDIATE v_sql;
                      7. END;
                      8. /

                       

                       

                      [........]

                       

                      You can't reference your local variable  l_num  in the v_sql string. The string is interpreted as is - with the five-character string 'l_num' in it, it has no particular meaning as part of v_sql. Then, when the CREATE TABLE statement is executed, the SQL runtime complains about the identifier  l_num  - it doesn't know what that is.

                       

                      Instead, you must concatenate  l_num  OUTSIDE the quoted strings that make up v_sql.  Something like this:

                       

                      CREATE OR REPLACE PROCEDURE crt_tab_t (l_num IN NUMBER)

                      AS

                        v_sql VARCHAR2(2000);

                      BEGIN

                        v_sql := 'CREATE TABLE t AS SELECT ROWNUM AS id FROM XMLTABLE(''1 to ' || l_num || ''')';

                        EXECUTE IMMEDIATE v_sql;

                      END;

                      /

                       

                      exec crt_tab_t(3)

                       

                      select * from t;

                       

                         ID

                      -----

                          1

                          2

                          3

                      1 person found this helpful
                      • 8. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
                        Quanwen Zhao

                        Hello mathguy ,

                         

                        Thanks. You and Gaz in Oz's replying is both nice!

                         

                        v_sql := 'CREATE TABLE t AS SELECT ROWNUM AS id FROM XMLTABLE(' '1 to ' || l_num || ' ' ')';

                                                                                                                                                                                                   ^^^ : Why using 3 single quote here?

                         

                        Best Regards

                        Quanwen Zhao

                        • 9. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
                          Paulzip

                          But if I use a parameter name to replace the maximum value I want to generate this time it has shown another error making me confused.

                           

                           

                          1. CREATEORREPLACEPROCEDUREcrt_tab_t(l_numINNUMBER)
                          2. AS
                          3. v_sqlVARCHAR2(2000);
                          4. BEGIN
                          5. v_sql:='CREATETABLEtASSELECTROWNUMASidFROMXMLTABLE(''1tol_num'')';
                          6. EXECUTEIMMEDIATEv_sql;
                          7. END;
                          8. /

                           

                          That isn't how you pass parameters to XQuery.

                           

                          Here's how you'd do it :

                           

                          select rownum

                          from xmltable('1 to xs:integer($i)' passing :l_num as "i")

                           

                          The xs:integer cast is required as otherwise XQuery defaults to xs:decimal, which isn't compatible for the loop.

                           

                          The trouble is, you couldn't really convert this to dynamic SQL though, as parameters can't be used in DDL for dynamic SQL, so the approach you should use is Gary's.

                           

                          declare

                            v_sql VARCHAR2(2000);

                            l_num NUMBER := 10000;

                          begiN

                            V_sql := 'CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE(''1 to '||l_num||''')';

                            -- execute immediate v_sql

                            dbms_output.put_line(v_sql);

                          end;

                           

                          The three quotes are because you are opening another string plus two quotes to escape the embedded quote.

                           

                          Alternatively, using q strings....

                           

                          declare

                            v_sql VARCHAR2(2000);

                            l_num NUMBER := 10000;

                          begiN

                            V_sql := q'[CREATE TABLE bigdata AS SELECT ROWNUM AS id FROM XMLTABLE('1 to ]'||l_num||q'[')]';

                            -- execute immediate v_sql

                            dbms_output.put_line(v_sql);

                          end;

                          1 person found this helpful
                          • 10. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
                            Quanwen Zhao

                            Hello Gaz in Oz ,

                             

                            I'm a beginner of PL/SQL code, now I try my best to write some, a special thank you again!

                             

                            Now I also found a funny and strange phenomenon when creating a table with Dynamic SQL.

                             

                            CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
                            AS
                               v_sql VARCHAR2(2000);
                            BEGIN
                               v_sql := 'CREATE TABLE test1 '
                                        || 'NOLOGGING '
                                        || 'SEGMENT CREATION IMMEDIATE '
                                        || 'AS SELECT ROWNUM id '
                                        || '          , CASE WHEN ROWNUM BETWEEN 1                      AND 1/5*' || l_num || ' THEN ''low'' '
                                        || '                 WHEN ROWNUM BETWEEN 2/5*' || l_num || '    AND 3/5*' || l_num || ' THEN ''mid'' '
                                        || '                 WHEN ROWNUM BETWEEN 4/5*' || l_num || '    AND     ' || l_num || ' THEN ''high'' '
                                        || '                 ELSE ''unknown'' '
                                        || '            END flag '
                                        || '   FROM XMLTABLE(''1 to ' || l_num || ''')';
                               EXECUTE IMMEDIATE v_sql;
                            END crt_tab_test;
                            /
                            

                             

                            SQL> CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
                              2  AS
                              3     v_sql VARCHAR2(2000);
                              4  BEGIN
                              5     v_sql := 'CREATE TABLE test1 '
                              6              || 'NOLOGGING '
                              7              || 'SEGMENT CREATION IMMEDIATE '
                              8              || 'AS SELECT ROWNUM id '
                              9              || '          , CASE WHEN ROWNUM BETWEEN 1                      AND 1/5*' || l_num || ' THEN ''low'' '
                             10              || '                 WHEN ROWNUM BETWEEN 2/5*' || l_num || '    AND 3/5*' || l_num || ' THEN ''mid'' '
                             11              || '                 WHEN ROWNUM BETWEEN 4/5*' || l_num || '    AND     ' || l_num || ' THEN ''high'' '
                             12              || '                 ELSE ''unknown'' '
                             13              || '            END flag '
                             14              || '   FROM XMLTABLE(''1 to ' || l_num || ''')';
                             15     EXECUTE IMMEDIATE v_sql;
                             16  END crt_tab_test;
                             17  /
                            
                            
                            Procedure created.
                            
                            
                            SQL> execute crt_tab_test(10000);
                            BEGIN crt_tab_test(10000); END;
                            
                            
                            *
                            ERROR at line 1:
                            ORA-00922: missing or invalid option
                            ORA-06512: at "SYS.CRT_TAB_TEST", line 15
                            ORA-06512: at line 1
                            

                             

                            As you can see my emphasizing section from the following order of two statements:

                             

                                        || 'NOLOGGING '

                                        || 'SEGMENT CREATION IMMEDIATE '

                             

                            If I exchange the order of statements above running my PL/SQL code again is normal.

                             

                            CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
                            AS
                               v_sql VARCHAR2(2000);
                            BEGIN
                               v_sql := 'CREATE TABLE test1 '
                                        || 'SEGMENT CREATION IMMEDIATE '
                                        || 'NOLOGGING '
                                        || 'AS SELECT ROWNUM id '
                                        || '          , CASE WHEN ROWNUM BETWEEN 1                      AND 1/5*' || l_num || ' THEN ''low'' '
                                        || '                 WHEN ROWNUM BETWEEN 2/5*' || l_num || '    AND 3/5*' || l_num || ' THEN ''mid'' '
                                        || '                 WHEN ROWNUM BETWEEN 4/5*' || l_num || '    AND     ' || l_num || ' THEN ''high'' '
                                        || '                 ELSE ''unknown'' '
                                        || '            END flag '
                                        || '   FROM XMLTABLE(''1 to ' || l_num || ''')';
                               EXECUTE IMMEDIATE v_sql;
                            
                            
                            END crt_tab_test;
                            /
                            

                             

                            SQL> CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
                              2  AS
                              3     v_sql VARCHAR2(2000);
                              4  BEGIN
                              5     v_sql := 'CREATE TABLE test1 '
                              6              || 'SEGMENT CREATION IMMEDIATE '
                              7              || 'NOLOGGING '
                              8              || 'AS SELECT ROWNUM id '
                              9              || '          , CASE WHEN ROWNUM BETWEEN 1                      AND 1/5*' || l_num || ' THEN ''low'' '
                             10              || '                 WHEN ROWNUM BETWEEN 2/5*' || l_num || '    AND 3/5*' || l_num || ' THEN ''mid'' '
                             11              || '                 WHEN ROWNUM BETWEEN 4/5*' || l_num || '    AND     ' || l_num || ' THEN ''high'' '
                             12              || '                 ELSE ''unknown'' '
                             13              || '            END flag '
                             14              || '   FROM XMLTABLE(''1 to ' || l_num || ''')';
                             15     EXECUTE IMMEDIATE v_sql;
                             16  
                             17  END crt_tab_test;
                             18  /
                            
                            
                            Procedure created.
                            
                            
                            SQL> execute crt_tab_test(10000);
                            
                            
                            PL/SQL procedure successfully completed.
                            

                             

                            This's why?

                             

                            Best Regards

                            Quanwen Zhao

                            • 11. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
                              Paulzip

                              According to the syntax diagrams for Create table, the segment properties clause (which would contain NOLOGGING), is part of the deferred segment clause, so yes, should appear after it.

                              1 person found this helpful
                              • 12. Re: ORA-19114: XPST0003 - error during parsing the XQuery expression:
                                Quanwen Zhao

                                Hello Paulzip ,

                                 

                                Thanks for your good suggestion.

                                 

                                Actually using "q or Q delimiter" is a method understanding pretty easily. Hence I've re-modified my code.

                                 

                                CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
                                AS
                                   v_sql VARCHAR2(2000);
                                BEGIN         
                                   v_sql := q'[CREATE TABLE test1 
                                               SEGMENT CREATION IMMEDIATE 
                                               NOLOGGING 
                                               AS SELECT ROWNUM id 
                                                         , CASE WHEN ROWNUM BETWEEN  1                      AND 1/5*]' || l_num || q'[ THEN 'low' ]'
                                            || q'[              WHEN ROWNUM BETWEEN  2/5*]' || l_num || q'[ AND 3/5*]' || l_num || q'[ THEN 'mid' ]'
                                            || q'[              WHEN ROWNUM BETWEEN  4/5*]' || l_num || q'[ AND     ]' || l_num || q'[ THEN 'high' ]'
                                            || q'[              ELSE 'unknown' ]'
                                            || q'[         END flag ]'
                                            || q'[FROM XMLTABLE('1 to ]' || l_num || q'[')]';         
                                   EXECUTE IMMEDIATE v_sql;
                                END crt_tab_test;
                                /
                                

                                 

                                SQL> CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
                                  2  AS
                                  3     v_sql VARCHAR2(2000);
                                  4  BEGIN         
                                  5     v_sql := q'[CREATE TABLE test1 
                                  6                 SEGMENT CREATION IMMEDIATE 
                                  7                 NOLOGGING 
                                  8                 AS SELECT ROWNUM id 
                                  9                           , CASE WHEN ROWNUM BETWEEN  1                      AND 1/5*]' || l_num || q'[ THEN 'low' ]'
                                 10              || q'[              WHEN ROWNUM BETWEEN  2/5*]' || l_num || q'[ AND 3/5*]' || l_num || q'[ THEN 'mid' ]'
                                 11              || q'[              WHEN ROWNUM BETWEEN  4/5*]' || l_num || q'[ AND     ]' || l_num || q'[ THEN 'high' ]'
                                 12              || q'[              ELSE 'unknown' ]'
                                 13              || q'[         END flag ]'
                                 14              || q'[FROM XMLTABLE('1 to ]' || l_num || q'[')]';         
                                 15     EXECUTE IMMEDIATE v_sql;
                                 16  END crt_tab_test;
                                 17  /
                                
                                
                                Procedure created.
                                
                                
                                SQL> set serveroutput on
                                SQL> 
                                SQL> execute crt_tab_test(10000);
                                
                                
                                PL/SQL procedure successfully completed.
                                

                                 

                                Best Regards

                                Quanwen Zhao