OracleBlog

I thought I would declare my variable-lengthed variables with a pre-defined constant. Why?

1. I'd be sure they matched up everywhere.
2. I could change them all in a single place if required.

But I don't think you can do that in PL/SQL.

CREATE OR REPLACE PACKAGE test_constants IS
MAX_LEN CONSTANT NUMBER := 32;
END test_constants;

Package created.

SET SERVEROUTPUT ON

-- Anonymous block
DECLARE
x VARCHAR2(test_constants.MAX_LEN); -- Can't do this
BEGIN
x := 'Test ' || test_constants.MAX_LEN;
dbms_output.put_line(x);
END;

ERROR at line 2:
ORA-06550: line 2, column 31:
PLS-00491: numeric literal required

But what about other types?

Substitute NVARCHAR2, VARCHAR, CHAR, NCHAR, or STRING if you like, same result. Incidentally the "N" just means you are specifying the length in bytes instead of characters.

So what is a guy to do?

1. Just put a comment before every number, and make it a practise to make sure they all match.

DECLARE
x VARCHAR2(32); -- test_constants.MAX_LEN = 32

But here is another option.

2. Create a table with the types you want, and then use those types.

CREATE TABLE TEST_CONSTANTS (
MAX_LEN VARCHAR2(32)
);

Table created.

DECLARE
x TEST_CONSTANTS.MAX_LEN%TYPE;
BEGIN
x := 'Test';
dbms_output.put_line(x);
END;