oracle-databaseexecute-immediate

execute immediate - ORA-00904: STRING: invalid identifier


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