# Insert with multiple with clause with functions in it **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [March 26, 2020, 5:01am UTC](https://forums.toadworld.com/t/insert-with-multiple-with-clause-with-functions-in-it/49412 "2020-03-26T05:01:58Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Annec](https://avatars.discourse-cdn.com/v4/letter/a/ee59a6/32.png) [@Annec](https://forums.toadworld.com/u/Annec)\ **Post date:** [March 26, 2020, 5:01am UTC](https://forums.toadworld.com/t/insert-with-multiple-with-clause-with-functions-in-it/49412/1 "2020-03-26T05:01:58Z")
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 clause . 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 and add a / at end of the file. but when I run it, it gives another error: sample code: 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 / ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [March 26, 2020, 8:24am UTC](https://forums.toadworld.com/t/insert-with-multiple-with-clause-with-functions-in-it/49412/2 "2020-03-26T08:24:04Z")
Hi Anne, I'm not 100% sure what you are doing here, sorry, but it looks like you need Oracle 12c and above to use PL/SQL in a WITH clause. Are you using a suitable version? If so, then Tim Hall over at Oracle Base, an excellent resource by the way, has some information that might help you out: > **[WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)](https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1)** > > Define PL/SQL functions and procedures in the WITH clause of SQL statements. There's a section on the WITH\_PLSQL hint on the page at > **[WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)](https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#with-plsql-hint)** > > Define PL/SQL functions and procedures in the WITH clause of SQL statements. as well. What I don;t see is any use of INSERT, I'm pretty sure that the WITH clause is for SELECT queries only, even in 12c, that's all that the SQL Language manual mentions, SELECTs. Good luck. Cheers, Norm. [TeamT] ---
**Author:** ![Annec](https://avatars.discourse-cdn.com/v4/letter/a/ee59a6/32.png) [@Annec](https://forums.toadworld.com/u/Annec)\ **Post date:** [March 26, 2020, 3:51pm UTC](https://forums.toadworld.com/t/insert-with-multiple-with-clause-with-functions-in-it/49412/3 "2020-03-26T15:51:23Z")
I am using Oracle 12c. And I ran the same code in Oracle SQL developers with no error and returned correct result. Edit I am thinking Toad is not recognize the syntax and gives error. Maybe a bug? But it is correct syntax. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [March 26, 2020, 4:15pm UTC](https://forums.toadworld.com/t/insert-with-multiple-with-clause-with-functions-in-it/49412/4 "2020-03-26T16:15:23Z")
Hi Anne, SqlDeveloper? What's that? 😉 That connects to the databases in a different manner to Toad. I'm wondering if your Oracle Client used by Toad is also 12c? If not, or an older version, that might be the reason why. Are you sure it's correct syntax? The manual only indicates that a `with` clause can be used with a `select` statement, not an `insert`. I'm puzzled. Cheers, Norm. [TeamT] ---
**Author:** ![avergison](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/avergison/32/66_2.png) [@avergison](https://forums.toadworld.com/u/avergison)\ **Post date:** [March 26, 2020, 7:36pm UTC](https://forums.toadworld.com/t/insert-with-multiple-with-clause-with-functions-in-it/49412/5 "2020-03-26T19:36:06Z")
With 13.2 Toad yields syntax errors at lines 6, 15, 16, 17 and 18. It looks as if your statement is valid (INSERT followed by a subquery, the latter starting with a WITH clause), but our parser doesn't seem to like it. I'll make a ticket for that. Thanks, Andre ---
**Author:** ![Annec](https://avatars.discourse-cdn.com/v4/letter/a/ee59a6/32.png) [@Annec](https://forums.toadworld.com/u/Annec)\ **Post date:** [March 26, 2020, 11:24pm UTC](https://forums.toadworld.com/t/insert-with-multiple-with-clause-with-functions-in-it/49412/6 "2020-03-26T23:24:37Z")
Thank you. I meant in Oracle SQL developer tool, the query runs fine. I am sure the syntax is correct. My oracle client is also 12. Thanks for looking into it. ---
**Author:** ![brian\_leach\_781](https://avatars.discourse-cdn.com/v4/letter/b/bcef8e/32.png) [@brian\_leach\_781](https://forums.toadworld.com/u/brian_leach_781)\ **Post date:** [April 1, 2020, 9:56pm UTC](https://forums.toadworld.com/t/insert-with-multiple-with-clause-with-functions-in-it/49412/7 "2020-04-01T21:56:24Z")
I wrapped the entire select clause in a "Select \* from ( )" and it worked in TOAD. It sounds as if you shouldn't have to, but this is a work-around ``` INSERT /*+ WITH_PLSQL */ INTO deleteme_table( col1 ) SELECT * FROM (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) ```