execute immediate – ORA-00904: STRING: invalid identifier

November 10, 2021

I get the error ORA-00904: ggCategory: invalid identifier.

If I run the select normally, it works without any problems and returns the correct values. Does anyone know where the syntax error is?

execute immediate 'create table TEST_TABLE as (
    select 
        category.name l_category,
        u.*
    from 
        User u
    inner join listtext_view category on u.categoryID=category.ID and category.ident='||'ggCategory'||'
    )';

>Solution :

If ggCategory is meant to be a string literal then:

execute immediate 'create table TEST_TABLE as (
    select 
        category.name l_category,
        u.*
    from 
        User u
    inner join listtext_view category on u.categoryID=category.ID and category.ident=''ggCategory''
    )';

If it is meant to be a variable then:

execute immediate 'create table TEST_TABLE as (
    select 
        category.name l_category,
        u.*
    from 
        User u
    inner join listtext_view category on u.categoryID=category.ID and category.ident='||ggCategory||'
    )';

Assuming, in this later case, that it is a number or something else that does not need quoting; or, if it does need quoting:

execute immediate 'create table TEST_TABLE as (
    select 
        category.name l_category,
        u.*
    from 
        User u
    inner join listtext_view category on u.categoryID=category.ID and category.ident='''||ggCategory||'''
    )';