Identifier Too long Exception

Question

I have a query of more than 4000 characters which is formed from different varaibles having varchar2 datatype of size 2000 example query1 varcahr2(2000):='string 1'; query2 varchar2(2000):='string2'; query3 varcahr2 (2000):= string3';

I have declared a variable query varchar2(32000) query := query1|| query2 || query3 ;

create table t ( id number, querystring varchar2(4000));

I tried to get the first 4000 characters from the query variable it is not working. Can anyone please help?

declare
  querystring1 varchar2(2000) := "string1";
  querystring2 varchar2(2000) := "string2";
  l_query varchar2(32000);
  query varchar2(4000);
begin
  l_query := querystring1 || querystring2 ;
  select substr(l_query,1,4000) into query from dual;
  insert into lib_query_table values('1',query);
end;

Answer 1

You are using double quotes around your query string literals, instead of single quotes. That makes Oracle interpret them as identifier names - so as soon as your literal is more than 30 characters long you'll get that exception. With shorter strings you'd still get an error, but something like 'unknown identifier'.

Replace your double quotes with single ones:

declare
  querystring1 varchar2(2000) := 'string1';
  querystring2 varchar2(2000) := 'string2';
  l_query varchar2(32000);
  query varchar2(4000);
begin
  l_query := querystring1 || querystring2 ;
  select substr(l_query,1,4000) into query from dual;
  insert into lib_query_table values (1, query);
end;

You don't need the query from dual, you can do:

query := substr(l_query, 1, 4000);

You could skip that variable and do:

insert into lib_query_table (id, querystring)
values (1, substr(l_query, 1, 4000);

or even:

insert into lib_query_table (id, querystring)
values (1, substr(querystring1 || querystring2, 1, 4000));

As your ID column is a number you should not insert the value for that as a string '1' - just use a number. You probably want a sequence to set that value, eventually.

---

Also not directly related, but when you're concatenating parts of a query together, say where one string is the select list and the second is the from clause etc., make sure you have whitespace at the end of each part (or the start of the next part), or the combined string might end up invalid.

Answer 2

Variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is: 32767 bytes or characters if MAX_STRING_SIZE = EXTENDED 4000 bytes or characters .

You can look CLOB data type