ora 00904 invalid identifier что за ошибка
Ora 00904 invalid identifier что за ошибка
Наиболее распространённые ошибки, возникающие во время переноса базы данных, EXP-00091, ORA-00904, ORA-28031, IMP-00032 и неверные кодировки.
EXP-00091: Exporting questionable statistics.
Может возникать при экспорте данных таблицы, если вместе с данными экспортируется собранная статистика оптимизатора. Причины возникновения: ошибка записи, кодировка клиента не соответствует кодировке сервера, были указаны дополнительные условия экспорта, экспортируется только часть разделов таблицы. Можно отказаться от экспорта статистики совсем, указав параметр STATISTICS=NONE
или установить кодировку, соответствующую серверу – параметр NLS_LANG
ORA-00904: “POLTYP”: invalid identifier
Эта ошибка – явный признак несоответствия версии БД Oracle и версии клиента, выполняющего экспорт. При логическом бэкапе/восстановлении данных работает несколько правил:
ORA-28031: maximum of 148 enabled roles exceeded.
Ошибка возникает в том случае, если у пользователя, под которым происходит соединение, оказывается активировано более 148 ролей. Такое явление легко получить при развертывании БД в момент создания всех необходимых пользовательских ролей. Просто при создании роли пользователю, эту роль создавшему, она назначается автоматически. Обычно это случается с пользователем system. В этом случае выход простой – после создания всех ролей просто отменить их назначение пользователю system – вряд ли они ему понадобятся все разом.
Тем не менее, нельзя не вспомнить про параметр max_enabled_roles. Почему бы просто не назначить этому параметру большее значение? Потому что начиная с версии 10g этот параметр был отменен и его присутствие сохраняется только для обратной совместимости, но значение фактически ни на что не влияет. Итак, в результате мы имеем жесткое ограничение на присутствие у пользователя не более 150 активных пользовательских ролей (фактически 148, т.к. каждый пользователь обязательно имеет 2 роли: PUBLIC и свою собственную). Если же имеется острая необходимость превысить это ограничение, то единственный выход – назначить роли, но не делать их активными по-умолчанию, а активировать необходимые (только напрямую назначенные пользователю) роли во время сессии.
IMP-00032: SQL statement exceeded buffer length; IMP-00008: unrecognized statement in the export file
Иногда ошибку IMP-00032 могут вызывать параметры кодировки, с которыми выполнялся экспорт данных (в моем случае экспорт выполнялся в UTF8). На Metalink по этому поводу присутствуют решения для некоторых случаев (Note 278980.1). Но для начала полезно просто задать большее значение размера буфера в байтах при вызове команды импорта.
Проблема кодировки. Вместо символов знаки вопроса (. ).
Неправильное перекодирование символов происходит при неверных параметрах кодировки NLS_LANG во время экспорта и импорта. В лучшем случае, если экспорт был выполнен с подходящими параметрами, и национальные символы были обработаны правильно, но импортировались данные с другими настройками, то можно получить знаки вопроса в кодах объектов (комментарии к таблицам, триггера, функции, процедуры и т.д.), при этом записи таблиц будут отображаться правильно. В этом случае достаточно повторно выполнить импорт с параметрами, соответствующими экспорту. В худшем случае кодировка может быть нарушена и в объектах и в таблицах. В этом случае следует заново выполнить экспорт с подходящими настройками NLS_LANG.
ORA-00904: недопустимый идентификатор
Я попытался написать следующий внутренний запрос соединения, используя базу данных Oracle:
это дает следующую ошибку:
9 ответов
ваша проблема в этих пагубных двойных кавычках.
Oracle SQL позволяет игнорировать случай имен объектов базы данных при условии, что мы либо создаем их с именами в верхнем регистре, либо без использования двойных кавычек. Если мы используем смешанный регистр или нижний регистр в скрипте и завернули идентификаторы в двойные кавычки, мы обречены на использование двойных кавычек и точного случая всякий раз, когда мы ссылаемся на объект или его атрибуты:
tl; dr
Не используйте двойные кавычки в сценариях DDL
(Я знаю, что большинство сторонних генераторов кода делают, но они достаточно дисциплинированы, чтобы поместить все свои имена объектов в верхнем регистре.)
в моем случае эта ошибка произошла из-за отсутствия имени столбца в таблице.
после изменения таблицы, он работал нормально.
DEPARTMENT_CODE не является столбцом, который существует в команде таблицы. Проверьте DDL таблицы, чтобы найти правильное имя столбца.
FYI, в этом случае причиной было найдено имя столбца смешанного случая в DDL для создания таблицы.
однако, если вы смешиваете «старый стиль» и ANSI присоединяется, вы можете получить то же сообщение об ошибке, даже если DDL был сделан правильно с именем таблицы в верхнем регистре. Это случилось со мной, и google отправил меня на эту страницу stackoverflow, поэтому я думал, что поделюсь, так как я был здесь.
два оператора SQL выше эквивалентны и не производят ошибка.
когда вы пытаетесь смешать их, вам может повезти, или вы можете получить Oracle имеет ошибку ORA-00904.
и бесполезное сообщение об ошибке, которое на самом деле не описывает проблему вообще:
я смог найти некоторые исследования по этому вопросу в следующем блоге:
В моем случае, я пытался вручную преобразовать из старого стиля в стиль ANSI присоединяется и делает это постепенно, по одной таблице за раз. Похоже, это была плохая идея. Вместо этого, вероятно, лучше преобразовать все таблицы сразу или прокомментировать таблицу и ее условия where в исходном запросе для сравнения с новым запросом ANSI, который вы пишете.
вы уверены, что у вас есть столбец DEPARTEMENT_CODE в таблице PS_TBL_DEPARTMENT_DETAILS
У меня было такое же исключение в JPA 2 с помощью Eclipse link. У меня был @embedded класс с отношением один к одному с сущностью. По ошибке, во встроенном классе у меня также была аннотация @Table («трейдер»). Когда БД была создана JPA из сущностей, она также создала table TRADER (что было неправильно, поскольку сущность Trader была встроена в основную сущность), и существование этой таблицы вызывало вышеуказанное исключение каждый раз, когда я пытался сохранить свою сущность. После удаления за столом трейдера это исключение не устраивало.
также убедитесь, что пользователю, выдавшему запрос, были предоставлены необходимые разрешения.
для запросов к таблицам необходимо предоставить разрешение SELECT.
Для запросов к другим типам объектов (например, хранимым процедурам) необходимо предоставить разрешение EXECUTE.
Я передавал значения без кавычек. Как только я передал условия внутри одинарных кавычек, это сработало как шарм.
вместо этого использовать:
убедитесь, что нет / после ваших операторов DDL.
ORA-00904: invalid identifier
I tried to write the following inner join query using an Oracle database:
That gives the below error:
The DDL of one table is:
12 Answers 12
Your problem is those pernicious double quotes.
Oracle SQL allows us to ignore the case of database object names provided we either create them with names all in upper case, or without using double quotes. If we use mixed case or lower case in the script and wrapped the identifiers in double quotes we are condemned to using double quotes and the precise case whenever we refer to the object or its attributes:
tl;dr
don’t use double quotes in DDL scripts
(I know most third party code generators do, but they are disciplined enough to put all their object names in UPPER CASE.)
The reverse is also true. If we create the table without using double-quotes …
… we can reference it and its columns in whatever case takes our fancy:
In my case, this error occurred, due to lack of existence of column name in the table.
After altering the table, it worked fine.
FYI, in this case the cause was found to be mixed case column name in the DDL for table creation.
However, if you are mixing «old style» and ANSI joins you could get the same error message even when the DDL was done properly with uppercase table name. This happened to me, and google sent me to this stackoverflow page so I thought I’d share since I was here.
The two SQL statements above are equivalent and produce no error.
When you try to mix them you can get lucky, or you can get an Oracle has a ORA-00904 error.
And the unhelpful error message that doesn’t really describe the problem at all:
I was able to find some research on this in the following blog post:
In my case, I was attempting to manually convert from old style to ANSI style joins, and was doing so incrementally, one table at a time. This appears to have been a bad idea. Instead, it’s probably better to convert all tables at once, or comment out a table and its where conditions in the original query in order to compare with the new ANSI query you are writing.
How to Resolve ORA-00904 Invalid Identifier
ORA-00904
Most users won’t believe the error, but indeed SQL parser has never made any mistake. In this post, you may see some error patterns about ORA-00904 invalid identifier, maybe one of them matches your case. Since most user errors were caused by case-sensitive problem, so we have to explain the differences between loose form and exact form first.
Normally, Oracle treats most identifiers excepts password as case-insensitive ones. But there’re some special usages should be taken care of in case of ORA-00904 invalid identifier.
Not only ORA-00904, but ORA-00903 and ORA-00911 are also related to illegal usage on object identifiers. More specifically, ORA-00903 alerts users for invalid table names, and ORA-00911 is raised for positioning illegal characters.
Loose Form vs Exact Form
According to Oracle Database Object Names and Qualifiers, there’re two kinds of valid naming forms to create a database object. One is non-quoted identifiers, the other is quoted identifiers, both are legal ways to name an object. But for avoiding ORA-00904, you have to clearly know differences between them.
Non-quoted Identifiers (Loose Form)
They are not surrounded by punctuation marks or any other special treatments. We can use them case-insensitively and flexibly in SQL statements as long as they are basically equivalent string. So I usually call it Loose Form in this post. In fact, non-quoted identifiers are all regarded as upper-cased ones. This concept will help you to know ORA-00904 better.
Quoted Identifiers (Exact Form)
They begin and end with double quotation marks («»). You can put almost every character in the double quote, including white spaces and reserved words. This kind of identifiers should be used exactly as is originally defined. So I usually call it Exact Form in this post. Misusing the two forms is the major source of ORA-00904 as far as I know.
Error Patterns of ORA-00904
In most cases, misuse of column names is the main source of ORA-00904, the rest is syntax error. In this post, we will talk about several error patterns of ORA-00904 in the following sections.
A. ORA-00904 in SELECT or INSERT Statements
Any columns listed in SELECT or INSERT all have chances to select invalid identifier.
In this section, ORA-00904 alerts users something is wrong, which may be caused by one of the following reasons:
1. ORA-00904 due to Non-existent Columns
Normally, we create a table without using double quotes:
SQL> create table all_names_1 (First_Name varchar2(25), Last_Name varchar2(25));
Then we insert some data.
SQL> insert into all_names_1 select distinct first_name, last_name from employees;
To avoid ORA-00904, you should query this table without any quotation marks. That is to say, column names in either lower or upper case is valid and acceptable. This is because SQL parser will treat all identifiers as upper-cased ones, then qualify each of every column.
SQL> select first_name, last_name from all_names_1 where first_name = ‘Ed’;
Using such normally created tables guarantees that it will no longer have ORA-00904 anymore? Let’s see several common types of invalid identifiers.
The first case is to select a column which does not exist in the table.
SQL> select first_name, last_name, num from all_names_1 where first_name = ‘Ed’;
select first_name, last_name, num from all_names_1 where first_name = ‘Ed’
*
ERROR at line 1:
ORA-00904: «NUM»: invalid identifier SQL> insert into all_names_1 (first_name, last_name, num) values (‘Ed’, ‘Chen’, 100);
insert into all_names_1 (first_name, last_name, num) values (‘Ed’, ‘Chen’, 100)
*
ERROR at line 1:
ORA-00904: «NUM»: invalid identifier
ORA-00904 invalid identifier in Toad for Oracle
As we know, the column NUM is a wrong column because it does not exist in the table. We’d better to check the definition by describing the table.
If you surprised that the column was missing from the table, it may be removed by someone else.
2. ORA-00904 due to Non-existent Functions
This pattern is rare though, we should talk about non-existent function calls. Let’s see how we reproduce ORA-00904.
SQL> select first_name, month(hire_date) hire_month from employees where last_name = ‘Chen’;
select first_name, month(hire_date) hire_month from employees where last_name = ‘Chen’
*
ERROR at line 1:
ORA-00904: «MONTH»: invalid identifier
The solution to ORA-00904 is to call the correct Oracle function named EXTRACT to get month value for your column.
SQL> select first_name, extract(month from hire_date) hire_month from employees where last_name = ‘Chen’;
Function DATEDIFF does not Work in Oracle
3. ORA-00904 due to Misspelled Columns
The third case is to select a misspelled and false column name, which is the most common pattern of ORA-00904.
SQL> select first name, last_name from all_names_1 where first_name = ‘Ed’;
select first name, last_name from all_names_1 where first_name = ‘Ed’
*
ERROR at line 1:
ORA-00904: «FIRST»: invalid identifier
To query a column FIRST aliased as NAME and a column LAST_NAME from table ALL_NAMES_1 with rest of conditions.
To avoid typos on column names, you can use a GUI tool like SQL Developer, Toad for Oracle or PL/SQL Developer to facilitate you to autocomplete column names. For an example of SQL Developer:
Autocomplete Column Names in SQL Developer Editor so as to Avoid ORA-00904 invalid identifier
4. Case-Sensitive Columns
In some cases, SQL parser complained about the missing column by throwing ORA-00904, but column exists in the table. How could this happen? All you need to know is that how to use exact form to express columns in SQL statements.
To use exact form of identifiers, we have to use double quotes to wrap column names, which notify the database to create the exact name as we provided. How exactly? At least, we should treat them as case-sensitive identifiers.
SQL> create table all_names_2 («First_Name» varchar2(25), «Last_Name» varchar2(25));
Then we insert some data.
SQL> insert into all_names_2 select distinct first_name, last_name from employees;
From now on, we can no longer use the table loosely like the old days. Otherwise, we have great chances to use incorrect identifiers in statements and get ORA-00904 thereafter.
Without adding double quotes on columns, we got ORA-00904.
SQL> select First_Name, Last_Name from all_names_2 where First_Name = ‘Ed’;
select First_Name, Last_Name from all_names_2 where First_Name = ‘Ed’
*
ERROR at line 1:
ORA-00904: «FIRST_NAME»: invalid identifier SQL> insert into all_names_2 (First_Name, Last_Name) values (‘Ed’, ‘Chen’);
insert into all_names_2 (First_Name, Last_Name) values (‘Ed’, ‘Chen’)
*
ERROR at line 1:
ORA-00904: «LAST_NAME»: invalid identifier
That is to say, they are literally not the same.
Solution
The cure is simple, we should quote them exactly as we provided at the table creation, instead of non-quoted form.
SQL> select «First_Name», «Last_Name» from all_names_2 where «First_Name» = ‘Ed’;
We used the exact form to make statements valid. As we can see, using exact form is very inconvenient, we should take care of every tiny detail on identifiers to prevent them from being invalidated.
5. Blanks in Column Names
It’s worth noting that the exact form allows us to create columns filled with blanks, which are valid without ORA-00904 invalidation problem. Let’s see a normal table first.
SQL> create table all_names_3 (First_Name varchar2(25), Last_Name varchar2(25), Num int, Create_Date date default sysdate);
SQL> insert into all_names_3 (first_name, last_name, num) select first_name, last_name, count(*) from employees group by first_name, last_name;
SQL> select first_name, last_name, num, create_date from all_names_3 where first_name = ‘Ed’;
Next, let’s see an odd but valid case with blanks in column names.
SQL> create table all_names_4 («First_Name» varchar2(25), «Last_Name» varchar2(25), » » int, » » date default sysdate);
SQL> insert into all_names_4 («First_Name», «Last_Name», » «) select first_name, last_name, count(*) from employees group by first_name, last_name;
SQL> select «First_Name», «Last_Name», » «, » » from all_names_4 where «First_Name» = ‘Ed’;
As we can see, I used one blank space for the third column and two blank spaces for the fourth column, they can work well without ORA-00904 as long as you follow the rule to query the table.
Any name collision or invalidation? No, this is because one blank is different from two blanks within the namespace of the same table, especially when we are using exact form to define the columns.
Even though we did not get any ORA-00904 and invalidation problem as we used the table carefully and properly, the exact-styled naming is really confusing. Try to describe the odd but valid table:
Consequently, it displays little information on the third and fourth column. For a new member, who has absolutely no idea what’s going on here. Moreover, once ORA-00904 is thrown when querying such an odd table, no one is able to troubleshoot it.
This remind me that I had ever tried to remove a file with empty or blank name in an Unix OS. It took me a long time to fix it.
B. ORA-00904 in WHERE, ORDER BY or GROUP BY Clauses
In this section, we specifically talk about how we can use column aliases in WHERE or GROUP BY clause.
1. ORA-00904 due to Misusing Column Aliases
You can use column aliases in ORDER BY clauses like this.
SQL> column did format 9999;
SQL> column eid format 9999;
SQL> select department_id as did, employee_id as eid from employees order by did ;
However, you can neither use column aliases in WHERE clauses directly:
SQL> select department_id as did, employee_id as eid from employees where did > 50 ;
select department_id as did, employee_id as eid from employees where did > 50
*
ERROR at line 1:
ORA-00904: «DID»: invalid identifier
Nor in GROUP BY clauses:
SQL> select department_id as did, count(employee_id) as cnt from employees group by did ;
select department_id as did, count(employee_id) as cnt from employees group by did
*
ERROR at line 1:
ORA-00904: «DID»: invalid identifier
We all know what column DID is, but SQL parser don’t. So it unqualified the alias name by throwing ORA-00904 to alert the problem.
Solutions
Of course, you can use real column names to suppress ORA-00904 anytime. The statements would be more stable in this way.
SQL> select * from (select department_id as did, employee_id as eid from employees) where did > 50;
For GROUP BY clauses that want to use column aliases, it’s a little tricky.
SQL> select did, count(eid) cnt from (select department_id as did, employee_id eid from employees) group by did ;
That is to say, if you insist to use column aliases, an outer SELECT is your solution to ORA-00904 in this error pattern, which can regard all of your column aliases as real column names.
C. ORA-00904 in CREATE TABLE Statements
There’re several possible patterns of ORA-00904 in CREATE TABLE statement.
1. ORA-00904 due to Misusing Reserved Words
Errors like ORA-00904 could happen in all kinds of object creation. Let’s see some wrong types of identifiers to clarify the naming rules.
Using reserved words are wild, everything that involves them may become unpredictable and nasty eventually. You should never use them to name your database objects.
SQL> create table t1 (audit int);
create table t1 (audit int)
*
ERROR at line 1:
ORA-00904: : invalid identifier
We saw ORA-00904 again, but this time SQL parser can’t tell the column name and left illegal string empty.
If you insist to use them, please use double quotes, the exact form.
SQL> create table t1 («audit» int);
Chances are, your users might create such database objects under totally unconscious situations. This is because their tools take care of the rest.
2. ORA-00904 due to Mistakenly Added Extra Comma
An extra comma mistakenly added in the statement makes SQL parser don’t know what to do, let’s take a look some examples:
SQL> create table t1 (c1 number, c2 date,);
create table t1 (c1 number, c2 date,)
*
ERROR at line 1:
ORA-00904: : invalid identifier SQL> create table t1 (c1 number,, c2 date);
create table t1 (c1 number,, c2 date)
*
ERROR at line 1:
ORA-00904: : invalid identifier
As you can see, we have an extra comma in the column list. SQL parser knew there’s nothing or empty after the extra comma, but it eventually left ORA-00904 for you to fix the illegal usage.
As we have talked, defining columns with pure blanks are acceptable and meaningful as long as you use the exact form to create table. But a nothing or empty column is another idea, which is totally nonsense at all. No wonder ORA-00904 was thrown.
To correct the problem, we should remove the extra comma to make the statement work.
SQL> create table t1 (c1 number, c2 date);
3. ORA-00904 due to Starting with Number
How about a table name which starts with a number?
SQL> create table t1 (12345678 int);
create table t1 (12345678 int)
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-00904 warned you that it is illegal here. So I added a letter «c» before the identifier to fix ORA-00904.
SQL> create table t1 (c12345678 int);
For any illegal characters being used, ORA-00911 is raised to alert this problem like the following.
In fact, the dollar sign «$» is valid for an identifier, just don’t put it in the beginning of the object name. For more about the restrictions on special characters, you should go for the next rule.
Although ORA-00911 is an illegitimate usage of object naming, it’s not as obvious and specific as ORA-00904. Now let’s take a look at how to use special characters on object naming.
Special Characters
SQL> create table t1 (c12345678?production int);
create table t1 (c12345678?production int)
*
ERROR at line 1:
ORA-00911: invalid character
Instead of ORA-00904, we saw ORA-00911 that notified us that the question mark is an illegal character. So I changed the «?» into «$», «#» or «_» to correct the problem.
SQL> create table t1 (c12345678$production int);
SQL> create table t2 (c12345678#production int);
SQL> create table t3 (c12345678_production int);
In practice, using a string for an identifier is pretty normal. Instead of spaces, underscores are often used and recommended for separating meaningful words in a string.
D. ORA-00904 in ALTER TABLE Statements
Problems like non-existent columns, non-existent functions, misspelled columns and case sensitive columns are also applied here. Therefore, I’d rather talk about some rare cases in this section.
There’s several patterns of ORA-00904 in ALTER TABLE statement.
1. ORA-00904 in ALTER TABLE ADD Column
A typical ORA-00904 in ALTER DATABASE ADD column statements is like this:
SQL> alter table t1 add column c2 date;
alter table t1 add column c2 date
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-00904 specifically positioned at the reserved word COLUMN and told you that it is not a valid identifier. In fact, this is a syntax error. You don’t have to add the reserved word COLUMN in the statement. So we correct it by removing the keyword.
SQL> alter table t1 add c2 date;
2. ORA-00904 in ALTER TABLE MODIFY Column
Let’s see a case of ORA-00904 in ALTER DATABASE MODIFY column statements.
SQL> alter table all_names_1 modify (column fname varchar2(30));
alter table all_names_1 modify (column fname varchar2(30))
*
ERROR at line 1:
ORA-00904: : invalid identifier
Same reason here, you don’t have to add the reserved word COLUMN in the statement. Please remove it.
After removing the reserved word, we still got ORA-00904 because I used an invalid column name deliberately.
SQL> alter table all_names_1 modify (fname varchar2(30));
alter table all_names_1 modify (fname varchar2(30))
*
ERROR at line 1:
ORA-00904: «FNAME»: invalid identifier
SQL> alter table all_names_1 modify (first_name varchar2(30));
3. ORA-00904 in ALTER TABLE ADD CONSTRAINT NOT NULL
SQL> create table t1 (c1 number);
You can add an UNIQUE constraint on a column in ALTER TABLE ADD CONSTRAINT statements like this:
SQL> alter table t1 add constraint c1_unique UNIQUE (c1);
But you cannot add a NOT NULL constraint on a column in the same way.
SQL> alter table t1 add constraint c1_nn NOT NULL (c1);
alter table t1 add constraint c1_nn NOT NULL (c1)
*
ERROR at line 1:
ORA-00904: : invalid identifier
As you can see, SQL parser threw ORA-00904 to alert the identifier name used in the statement is illegal. In fact, it’s a syntax error.
Why? NOT NULL is a constraint, isn’t it? Of course, NOT NULL is some kind of constraint, but it’d rather be a column attribute and we used it in the wrong way. Let’s treat it as a constraint first.
Solution 1: Regard it as a Constraint
SQL> alter table t1 modify (c1 constraint c1_nn NOT NULL);
Let’s check the constraint name.
SQL> select constraint_name from user_constraints where table_name = ‘T1’;
SQL> alter table t1 drop constraint C1_NN;
Not like other constraints, NOT NULL cannot be a composite (multi-column) constraint, it only serves for its column.
Solution 2: Regard it as a Column Attribute
Strictly speaking, NOT NULL is a column attribute, so you don’t have to treat it like a constraint, even though it can be a constraint.
For example, we can modify a column as NOT NULL like this:
SQL> alter table t1 modify (c1 NOT NULL);
As we can see, I treated it as a column attribute, not any constraint clause is involved. Let’s check the constraint name.
SQL> select constraint_name from user_constraints where table_name = ‘T1’;
Consequently, the database provided a system-generated name for the constraint. Let’s go further to see how we drop the constraint.
SQL> alter table t1 modify (c1 NULL);
SQL> select constraint_name from user_constraints where table_name = ‘T1’;
That is to say, we don’t have to know the constraint name in order to drop it, just revert the attribute. By the way, there’re more syntax that can add or drop NOT NULL constraints easily.
E. ORA-00904 in PL/SQL Stored Procedures
So far, we have two error patterns of ORA-00904 in PL/SQL.
1. ORA-00904 due to Incorrect Order of Variable Declaration in PL/SQL
Suppose we’d like to use a cursor which involves a variable like this:
SQL> declare
2 cursor c1 is select employee_id from hr.employees where department_id = v_num;
3 v_num number;
4 begin
5 v_num := 110;
6 open c1;
7 end;
8 /
cursor c1 is select employee_id from hr.employees where department_id = v_num;
*
ERROR at line 2:
ORA-06550: line 2, column 75:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 2, column 75:
PL/SQL: ORA-00904: «V_NUM»: invalid identifier
ORA-06550: line 2, column 16:
PL/SQL: SQL Statement ignored
We saw ORA-00904 thrown by PL/SQL engine. In which, ORA-06550 indicated that the identifier at line 2, column 75 was used illegally in the anonymous PL/SQL block.
This error is very obvious, we cannot use variables before we declare them. In other words, we have to declare variables first to prevent from selecting invalid identifier. So I switched line 2 and 3 in places as this:
SQL> declare
2 v_num number;
3 cursor c1 is select employee_id from hr.employees where department_id = v_num;
4 begin
5 v_num := 110;
6 open c1;
7 end;
8 /
PL/SQL procedure successfully completed.
2. ORA-00904 due to Unquoted String
When you use EXECUTE IMMEDIATE in PL/SQL, you might want to know how to quote a string in a statement.
SQL> begin
2 execute immediate ‘select nvl(first_name, NO_VALUE) from employees’;
3 end;
4 /
begin
*
ERROR at line 1:
ORA-00904: «NO_VALUE»: invalid identifier
ORA-06512: at line 2
Solution
SQL> begin
2 execute immediate ‘select nvl(first_name, »NO_VALUE») from employees’;
3 end;
4 /