Thursday, May 19, 2005

Dynamically assigning size of varchar2

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;

Comments:
you can use subtypes (trick that works with the NULLS from your other blog too -- if you want parameters that are NOT NULL, you can create a subtype which is NOT NULL and use that type...)

ops$tkyte@ORA9IR2> create or replace package types
2 as
3 subtype identifier_type is varchar2(30);
4 end;
5 /

Package created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 l_owner types.identifier_type;
3 begin
4 null;
5 end;
6 /

PL/SQL procedure successfully completed.
 
Thanks Tom for the suggestions. It is important to know that option, it is certainly more elegant than storing your types in a table.

One problem is the following:

create table atable (
acol types.identifier_type
);

ORA-00902: invalid datatype

You can't use types in table creations.

So what is a guy to do?
1. If your parameter is ultimately something to be selected from or inserted into a table, use the %TYPE of the column in the table.
2. If not, use a type from a package as Tom described.

Do you know of any other advantages or disadvantages of defining your types in tables instead of in packages?
 
Answering your own question may be a faux-pas, but in following up Tom's suggestion with regards to having NOT NULL parameters in a stored procedure, I discovered an advantage to using package subtypes instead of table column %TYPEs.

Package subtype: The NOT NULL property of the type is retained in stored procedure parameters.

Table column %TYPEs: The NOT NULL property of the type is NOT retained in stored procedure parameters, so you must validate any NULL parameters yourself.

"NULL vs Nothing"
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html

Any other advantages or disadvantages?
 
If you are creating a table just to get a type definition - that would be "wrong", use SUBTYPE in plsql.

For program variables that are used to fetch or insert into tables using table.column%type makes perfect sense (null or otherwise, the insert will catch it)

For program variables that are totally internal to your processing, subtypes make sense, you use the all of the time, edit $ORACLE_HOME/rdbms/admin/stdspec.sql and look at all of the subtypes (and you thought "float" meant something -- it just means "number", as does real...
 
This comment has been removed by a blog administrator.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?