execute immediate - ORA-00904: STRING: invalid identifier-CodePudding

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'||'
    )';

CodePudding user response：

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||'''
    )';

CodePudding user response：

What is ggcategory? I presume it is a variable (or a parameter); if so, it shouldn't be enclosed into single quotes, i.e.

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 ||')';
                                                           ----------

Besides, table name certainly isn't User as it is reserved word for Oracle's function.