-1

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 ?

2

What you are attempting to do is vague at best... But in Oracle, CHAR is analogous to CHAR(1) ..

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 CHAR(4) of '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 CHAR(1).

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 VARCHAR2(4) of '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 VARCHAR2 field.

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.