14 Replies Latest reply on Mar 27, 2020 9:10 PM by ANNEC

    Insert table with function in the with clause

    ANNEC

      I am using SQL DML scripts to do some data inserting into a table; the select has a with clause and with clause has a function and additional with .

      If I don't add the insert into  table part, it works fine. but if I add insert, it ends with error:

      like below:

       

      Insert into tb1 (col1, col2....)

        WITH   --- to find the graduation date in studentcorefields table

          FUNCTION test_date (d VARCHAR2)

              RETURN VARCHAR2

          AS

              v_date   DATE;

          BEGIN

              SELECT TO_DATE (d, 'MM/YYYY') INTO v_date FROM DUAL;

              RETURN 'Valid';

          EXCEPTION

              WHEN OTHERS

              THEN

                  RETURN 'Invalid';

          END;

      graduationDay

          AS  (select id, graduation_year, test_date(graduation_year)

      from  tb2

      where....)

       

      select ...

      From tb3

      join graduationday

      on.... ;

       

      It gives syntax error, ORA-00933: SQL command not properly ended.

       

      But if I run only the part from with to the end without the insert, it runs fine.return results. It seems the program does not like with function when used with insert statement.

      I read from article Ask Tom, it says to add  /*+ WITH_PLSQL */  in the insert statement, but it still does not work for me.

       

      Any fix so that I am able still use SQL.

       

      Thanks,

        • 1. Re: Insert table with function in the with clause
          mathguy

          Let’s start with the obvious - did you end the whole thing with a forward slash by itself on the last line? That is mandatory when you put a function in the WITH clause. I didn’t see it in your code .

          • 2. Re: Insert table with function in the with clause
            alvinder

            The following works for me.

             

            Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

            PL/SQL Release 12.2.0.1.0 - Production

            "CORE 12.2.0.1.0 Production"

            TNS for Linux: Version 12.2.0.1.0 - Production

            NLSRTL Version 12.2.0.1.0 - Production

             

             

            create table tab1(col varchar2(200));

             

             

            Insert  /*+ WITH_PLSQL */  into tab1(col) 
              WITH   --- to find the graduation date in studentcorefields table
                FUNCTION test_date (d VARCHAR2)
                    RETURN VARCHAR2
                is
                    v_date   DATE;
                BEGIN
                    SELECT TO_DATE (d, 'MM/YYYY') INTO v_date FROM DUAL;
                    RETURN 'Valid';
                EXCEPTION
                    WHEN OTHERS
                    THEN
                        RETURN 'Invalid';
                END;
            graduationDay
                AS  (select  test_date(sysdate) col1
            from  dual
            )
            select * From graduationDay
            /
            

             

             

            Alvinder

            • 3. Re: Insert table with function in the with clause
              ANNEC

              Thank you. I  saw some one online mentioned that to add a  /

              I tried that too, then it gives another error:

              ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following:

               

               

                 := . ( @ % ; not null range with default character

              • 4. Re: Insert table with function in the with clause
                alvinder

                "/" is necessary but until u post the complete code we can't  tell what's wrong.

                 

                Also if you can post sample data as well for the tb2, tb3 then it will be great.

                 

                Alvinder

                • 5. Re: Insert table with function in the with clause
                  GregV

                  Hi,

                   

                  Are you using this in PL/SQL? FYI, inline PL/SQL functions don't work in PL/SQL. See this thread:

                   

                  Inline PL/SQL function in a query used within PL/SQL : compilation error

                   

                  I filed a bug for this issue:

                  Bug 14613825 : COMPILE ERROR FOR EMBEDDED SQL USING QRY THAT HAS PL/SQL-CONTAINING WITH CLAUSE

                  • 6. Re: Insert table with function in the with clause
                    mNem

                    Your query works for me.

                     

                    Here is a link to a similar question.

                    https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9539062000346471801

                     

                    SQL> Insert /*+ WITH_PLSQL */ into tab1(col) 
                      2  WITH     --- to find the graduation date in studentcorefields table  
                      3      FUNCTION test_date (d VARCHAR2)  
                      4          RETURN VARCHAR2  
                      5      is  
                      6          v_date   DATE;   
                      7      BEGIN  
                      8          SELECT TO_DATE (d, 'MM/YYYY') INTO v_date FROM DUAL;  
                      9          RETURN 'Valid';  
                     10      EXCEPTION  
                     11          WHEN OTHERS  
                     12          THEN  
                     13              RETURN 'Invalid';
                     14      END; 
                     15  graduationDay AS  
                     16  (
                     17  select  test_date(sysdate) col1 from  dual
                     18  )  
                     19  select * From graduationDay
                     20  ;
                    
                    Error report -
                    SQL Error: ORA-00933: SQL command not properly ended
                    00933. 00000 -  "SQL command not properly ended"
                    *Cause:    
                    *Action:
                    
                    SQL> Insert /*+ WITH_PLSQL */ into tab1(col) 
                      2  WITH     --- to find the graduation date in studentcorefields table  
                      3      FUNCTION test_date (d VARCHAR2)  
                      4          RETURN VARCHAR2  
                      5      is  
                      6          v_date   DATE;   
                      7      BEGIN  
                      8          SELECT TO_DATE (d, 'MM/YYYY') INTO v_date FROM DUAL;  
                      9          RETURN 'Valid';  
                     10      EXCEPTION  
                     11          WHEN OTHERS  
                     12          THEN  
                     13              RETURN 'Invalid';
                     14      END; 
                     15  graduationDay AS  
                     16  (
                     17  select  test_date(sysdate) col1 from  dual
                     18  )  
                     19  select * From graduationDay
                     20  ;
                     21  /
                    
                    1 row inserted.
                    
                    SQL> Insert /*+ WITH_PLSQL */ into tab1(col) 
                      2  WITH     --- to find the graduation date in studentcorefields table  
                      3      FUNCTION test_date (d VARCHAR2)  
                      4          RETURN VARCHAR2  
                      5      is  
                      6          v_date   DATE;   
                      7      BEGIN  
                      8          SELECT TO_DATE (d, 'MM/YYYY') INTO v_date FROM DUAL;  
                      9          RETURN 'Valid';  
                     10      EXCEPTION  
                     11          WHEN OTHERS  
                     12          THEN  
                     13              RETURN 'Invalid';
                     14      END; 
                     15  graduationDay AS  
                     16  (
                     17  select  test_date(sysdate) col1 from  dual
                     18  )  
                     19  select * From graduationDay  
                     20  /
                    
                    1 row inserted.
                    
                    SQL> Insert /*+ WITH_PLSQL */ into tab1(col) 
                      2  WITH     --- to find the graduation date in studentcorefields table  
                      3      FUNCTION test_date (d VARCHAR2)  
                      4          RETURN VARCHAR2  
                      5      is  
                      6          v_date   DATE;   
                      7      BEGIN  
                      8          SELECT TO_DATE (d, 'MM/YYYY') INTO v_date FROM DUAL;  
                      9          RETURN 'Valid';  
                     10      EXCEPTION  
                     11          WHEN OTHERS  
                     12          THEN  
                     13              RETURN 'Invalid';
                     14      END; 
                     15  graduationDay AS  
                     16  (
                     17  select  test_date(sysdate) col1 from  dual
                     18  )  
                     19  select * From graduationDay  
                    
                    1 row inserted.
                    
                    • 7. Re: Insert table with function in the with clause
                      mNem

                      alvinder,

                       

                      Sorry, reply #6 was supposed to be a reply to you.

                       

                      EDIT: Tested in SQL Developer 19.4.0.354.

                      • 8. Re: Insert table with function in the with clause
                        mathguy

                        ANNEC wrote:

                         

                        Thank you. I saw some one online mentioned that to add a /

                        I tried that too, then it gives another error:

                        ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following:

                         

                         

                        := . ( @ % ; not null range with default character

                         

                         

                        I don't understand. Do you mean that you had already tried with the / at the end, but it gave you this different error, ORA-06553, so you suspected the reason was the / at the end, you removed it, you got the error you originally reported, so you posted your question here?

                         

                        If so, you should have told us what you did - what you tried, what happened, and what you thought the reason was. Don't keep relevant information to yourself, doing so just wastes everyone's time.

                         

                        So, your issue (after adding the / back where it belongs) has nothing to do with your original question. Different issue, unrelated to ORA-00993. Right?

                         

                        For the new question - hard to tell, since you didn't actually post your code. Tell us exactly what code you tried, and also what client interface you used. SQL*Plus? Toad? SQL Developer? Something else?

                        • 9. Re: Insert table with function in the with clause
                          ANNEC

                          Thanks all.

                           

                          I found I ran the same query in Oracle sql developer with no error. I am currently using Oracle Toad, the same exact code when running in Toad, it first gives  syntax error, ORA-00933: SQL command not properly ended.

                          After I added /, it gives the  other error ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following:..

                          • 10. Re: Insert table with function in the with clause
                            Cookiemonster76

                            I wouldn't call Toad "Oracle Toad" since it isn't created by the oracle corporation.

                            SQL Developer on the other hand is created by oracle themselves.

                             

                            If it only doesn't work in Toad then that would be a problem with Toad that you need to raise with the creators - Quest Software.

                             

                            All we can do is suggest you don't use Toad.

                            • 11. Re: Insert table with function in the with clause
                              ANNEC

                              Thanks, I should say Toad for Oracle, for they have another edition Toad for SQL sever

                              • 12. Re: Insert table with function in the with clause
                                mNem

                                Copy paste the query from reply #2 (from @alvinder) except for the last line ...

                                 

                                create table tab1(col varchar2(200));

                                 

                                 

                                Insert /*+ WITH_PLSQL */ into tab1(col)
                                WITH
                                FUNCTION test_date (d VARCHAR2) RETURN VARCHAR2   
                                is   
                                   v_date   DATE;    
                                BEGIN   
                                   SELECT TO_DATE (d, 'MM/YYYY') INTO v_date FROM DUAL;   
                                   RETURN 'Valid';   
                                EXCEPTION   
                                   WHEN OTHERS   
                                   THEN   
                                       RETURN 'Invalid'; 
                                END;  
                                graduationDay AS   

                                select  test_date(sysdate) col1 from  dual 
                                )   
                                select * From graduationDay

                                 

                                -- note it has neither semi colon nor forward slash at the end.

                                • 13. Re: Insert table with function in the with clause
                                  mathguy

                                  Many (all??) client programs take liberties with the code we write. Obviously, whatever the final answer will be to your question, the issue is Toad, not your actual code (with the function defined in the WITH clause, within an INSERT statement).

                                   

                                  Let "us" (including me here) not bash Toad and tell you that SQL Developer is perfect. It is not. In SQL Developer I am able to run your INSERT statement as you wrote it initially, without the forward slash at the end. SQL Developer will run it just fine, but if I try the same thing in SQL*Plus, it will complain about the missing slash.

                                   

                                  As a rule, if you want to see if something is a "code" problem vs. a client program problem, try to run your code exactly as is in SQL*Plus. If it runs there, you will know that the problem is with the client environment.

                                   

                                  In this case, though, if it does turn out that in Toad the code runs but only if there is neither semicolon nor slash at the end, I would raise the issue with Toad - with their creator and vendor. That shouldn't be allowed to stand. Good luck!

                                  • 14. Re: Insert table with function in the with clause
                                    ANNEC

                                    Just a follow up. Toad found this could be a defect in their parser. they made a ticket for that.