ORACLE: Cannot bind parameter to create table AS subquery-CodePudding

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_collection_ids
        ON COMMIT PRESERVE DEFINITION AS (
        SELECT 
            DISTINCT cms3_dc_language.collection_id AS "collection_id" 
        FROM 
            cms3_dc_language
        WHERE 
            cms3_dc_language.language = :language)

$sth->bindParam(":language", $language);

Gives an error when trying to bind :language...

OCIBindByName: ORA-01036: illegal variable name/number

The bind works if the bound parameter is not part of the create table AS subquery e.g.

SELECT 
            DISTINCT cms3_dc_language.collection_id AS "collection_id" 
        FROM 
            cms3_dc_language
        WHERE 
            cms3_dc_language.language = :language

$sth->bindParam(":language", $language);

CodePudding user response：

If you do the same thing using execute immediate you can see the real issue:

ORA-01027: bind variables not allowed for data definition operations

Although a bind variable is allowed as part of a query (obviously), here the query is a secondary factor, and it's the DDL that is the problem. You'd get the same error with a normal, non-temporary, table or a view, for instance.