execute immediate - ORA-00904: STRING: invalid identifier

Question

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

Why do you use dynamic SQL? Your statement does not require it. — Wernfried Domscheit, Nov 10 '21 at 08:26
I'm a little inexperienced with databases and sql. What do you mean with my statement does not equire it? I changed the select command for data privacy reasons and even so that the error still exists. The select is in a procedure. — , Nov 10 '21 at 09:00

score 1 · Answer 1 · answered Nov 10 '21 at 08:23

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.

MT0 · Accepted Answer · 2021-11-10T08:33:11.723

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

thx :) is meant to be a string literal and works. – Nov 10 '21 at 08:38 — , Nov 10 '21 at 08:38

execute immediate - ORA-00904: STRING: invalid identifier

2 Answers2