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