Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the declared size of a varchar2 in oracle database

Trying to get the size of a defined variable in Oracle. I may need to use a number when declaring the size of a varchar2 but would rather not have to keep track of an extra variable or number.

example pseudo code:

declare 
  myvar varchar(42) := 'a';
begin

  /* I know the length is length(myvar) = 1. */
  /* but how do I get 42? */
  /* What is the max defined size of this variable */
  declared_size_of(myvar);  
end

The reason I need this is to lpad the length of the string to the declared size so it doesn't generate an exception.

like image 259
RetroCoder Avatar asked Oct 31 '22 02:10

RetroCoder


1 Answers

As @Justin said in his comments, you don't have to explicitly blank pad the string if you use CHAR data type. Oracle would blank-pad the value to it's maximum size.

From documentation,

If the data type of the receiver is CHAR, PL/SQL blank-pads the value to the maximum size. Information about trailing blanks in the original value is lost.

For example,

SQL> SET serveroutput ON
SQL> DECLARE
  2    myvar CHAR(42);
  3  BEGIN
  4    myvar:='a';
  5    dbms_output.put_line(LENGTH(myvar));
  6  END;
  7  /
42

PL/SQL procedure successfully completed.

SQL>
like image 111
Lalit Kumar B Avatar answered Nov 15 '22 09:11

Lalit Kumar B