Insert with multiple with clause with functions in it

Toad for Oracle
1

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
/

2

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:

There's a section on the WITH_PLSQL hint on the page at


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]

3

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.

4

Hi Anne,

SqlDeveloper? What's that? :wink:

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]

5

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

1 Like
6

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.

7

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)