In PL/SQL strings are declared in two different ways. These ways are fixed-length strings declaration, variable-length strings declaration. If we need to declare a variable-length string, we must provide the maximum length of that string. For example, the VARCHAR2 data type.
DECLARE /*VARIABLE LENGTH STRING DECLARATION*/ name varchar2(20); company varchar2(30); /*FIXED LENGTH STRING DECLARATION*/ choice char(2) DEFAULT 'y'; BEGIN name := 'John Smith'; company := 'Infotech'; IF choice = 'y' THEN dbms_output.put_line(name); dbms_output.put_line(company); END IF; END; /
Above example all string variables are declared with variable-length string declaration. Namely ‘name’ and ‘company’ strings can have 1,2,3,4,5,…..20 character. So that they are variable-length strings.
To declare a fixed-length string, we should use the CHAR datatype. Here we do not have to specify a maximum length for a fixed-length variable. If we leave off the length constraint, Oracle Database automatically uses a maximum length required.
DECLARE /*FIXED LENGTH STRING DECLARATION*/ fixed_length_variable CHAR := 'y'; BEGIN IF fixed_length_variable = 'y' THEN dbms_output.put_line('Oracle Database automatically uses a maximum length for fixed length variable'); END IF; END; /
Now i want to declare string with fixed-length variable declaration. It has a more than one word.
DECLARE /*I CAN'T USE CHAR DATA TYPE. BECAUSE VALUE HAS MORE CHARACTERS*/ fixed_length_declaration VARCHAR2 := 'Fixed Declaration'; BEGIN dbms_output.put_line(fixed_length_declaration); END; /
When i want to try this example, ORACLE throw ‘ORA-06550: line 6, column 5:
PLS-00215: String length constraints must be in range (1 .. 32767)’ error. How can i make fixed length declaration with ‘fixed_length_declaration’ variable which is declared in the above code. How can i fix this error ? Must i use CHAR datatype to make fixed length declaration in PL/SQL ?
What you are attempting to do is vague at best… But in Oracle,
CHAR is analogous to
All of this information can easily be found in the oracle documentation (which is probably why you have down votes .. if you spent a bit of time in the oracle documentation for data types, you’d have your answers), but I will explain the difference between varchar and char here..
CHAR datatype is for fixed length strings, period. All strings within a
CHAR field are the length of the field. So a
'y' would ACTUALLY be stored as
'y ' (with buffering spaces to fill up the length). The only reason your test works above is because your
CHAR is the same as
VARCHAR2 datatype is for variable length strings, period. All strings within a
VARCHAR2 field are the length of the string that was given. So a
'y' would ACTUALLY be stored as
'y' (with a tiny bit of additional information saying that the data is “x” bytes long).
There are very few situations where you would prefer a
CHAR field over a
What @a_horse_with_no_name is trying to suggest is that you should be able to just declare your variable as
VARCHAR2(20) and be done with it. If, for some reason, 20 is TOO SHORT, then just declare it to be a larger size. If you don’t know how large it should be, then declare it maximum size (
VARCHAR2(32767) for PL/SQL)