An identifier with more than 30 characters was specified error message.

Table HOLDING_TABLE has one column named "HOLDER". I just want to insert a five character string into the column. The string I am inserting is the word "Smile". The column colder is 300 characters wide in length. Yet when the code is executed I get the error message shown at the bottom of this post. Does anyone know why I am getting this error, when both clearStr and the target column have enough characters define?


clearStr VARCHAR(100);

clearStr := 'INSERT INTO HOLDING_TABLE (HOLDER) VALUES(' || ''' Smile ''' || ')';
EXECUTE IMMEDIATE clearStr;



ORA-06512 at line 01
00972. 00000 - "identifier is too long";
*Cause: An identifier with more than 30 characters was specified.
*Action: Specifiy at most 30 characters.
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
Mark GeerlingsDatabase AdministratorCommented:
No, I don't see an obvious error in what you posted, so I have two questions:
1. Are you sure that you posted the line(s) that generated this error?
2. Do you have to use "dynamic SQL", that is: constructing the text of the insert and "hard-coding" the value, then processing that with "execute immediate"?  That approach in Oracle PL\SQL has big performance and security disadvantages compared to using SQL statements with bind variables in PL\SQL.
0
 
Geert GOracle dbaCommented:
Why the concatenation ?
clearStr := 'INSERT INTO HOLDING_TABLE (HOLDER) VALUES(''Smile'')';
EXECUTE IMMEDIATE clearStr;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I agree with Mark:  It works for me:
create table holding_table(holder varchar2(300));
declare
clearstr varchar2(100);
begin
clearStr := 'INSERT INTO HOLDING_TABLE (HOLDER) VALUES(' || ''' Smile ''' || ')';
EXECUTE IMMEDIATE clearStr;
end;
/

select * from holding_table;

Open in new window



I also agree that you are doing things wrong.  You shouldn't need dynamic sql and execute immediate.

Tell us your requirement and we can help.
0
 
awking00Commented:
Can you post an example of what is in column HOLDER and what you expect it to be after the 'Smile' insert?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.