Home » SQL & PL/SQL » SQL & PL/SQL » What will be maximum size of character variable
What will be maximum size of character variable [message #245099] Fri, 15 June 2007 03:07 Go to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
Hi,


I want to write dynamic sql in one of the procedure. I have declare variable as

v_sql varchar2(4000);

and also tried for

v_sql Long;


but its not storing that much of data in the variable its giving error as Invalid Identifire when it stores more data

Please give me solution on this as soon as possible

Regards,

Prasad


[Updated on: Fri, 15 June 2007 03:10]

Report message to a moderator

Re: What will be maximum size of character variable [message #245104 is a reply to message #245099] Fri, 15 June 2007 03:19 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

PL/SQL User's Guide and Reference, part VARCHAR2 datatype.
Quote:
The VARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes.

You can insert any VARCHAR2(n) value into a LONG database column because the maximum width of a LONG column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG column into a VARCHAR2(n) variable.


You cannot use VARCHAR2 longer than 32767 bytes.
If your statement is longer, you should shorten it (split into more statements, bind instead of hardcoding values).

Quote:
its giving error as Invalid Identifire when it stores more data

Seems you have some fuzzy Oracle version.
Mine always states accurate error messages starting with ORA-#####.
Is not more likely the problem in fact that the executed statement is not correct?

[Edit: added last paragraph]

[Updated on: Fri, 15 June 2007 03:30]

Report message to a moderator

Re: What will be maximum size of character variable [message #245178 is a reply to message #245104] Fri, 15 June 2007 07:06 Go to previous messageGo to next message
p.udas
Messages: 22
Registered: June 2006
Location: Pune
Junior Member
Hi,

Can I create VArray for this to create variable with respective
number of columns added in the query

Please help me if this is possible


Re: What will be maximum size of character variable [message #245210 is a reply to message #245099] Fri, 15 June 2007 08:08 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I have no idea about your SQL statement and not sure if I understand your last post well.

Not binding is one of the causes which may cause sql statement overexceeding (the only one I have seen yet).
You can bind literals, eg. varchars, dates, variable list in IN clause.
You cannot bind identifiers, eg. table names, column names (is this your cause?).
See more on AskTom.
Re: What will be maximum size of character variable [message #245214 is a reply to message #245099] Fri, 15 June 2007 08:41 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
In you are using a database higher the version 7, I would strongly suggest that you NEVER use long except if a legacy application requires it. Long is severely limited in what it can do. If you must use a LOB, then use CLOB for character strings.
Previous Topic: Select from DUAL OR Assign Variable ??
Next Topic: inline views
Goto Forum:
  


Current Time: Sat Dec 10 13:09:25 CST 2016

Total time taken to generate the page: 0.07179 seconds