CODEWITHSUNDEEP
oracle-databasepackageglobal-variables
eatSleepCode
eatSleepCode

Reputation: 4637

Global variable size in Oracle package

How to set size for global variable of type Varchar2 in oracle package depending on the parameter size from function.

CREATE OR REPLACE PACKAGE Test 
AS

   g_lastname Varchar2(15);
   FUNCTION search( p_lastname IN varchar2);

END;

CREATE OR REPLACE PACKAGE BODY Test
AS
    FUNCTION search(p_lastname IN VARCHAR2) return VARCHAR2 
    IS
    BEGIN
        g_lastname := p_lastname;
        return g_lastname;
    END;
END;

here the problem is if the p_lastname size is more then 15 then it will give error.

Upvotes: 0

Views: 2228

Answers (2)

APC
APC

Reputation: 146269

"%type is not helpful in my case."

This is pretty unusual. Most data passed between PL/SQL programs comes from or ends up in database tables. So best practice is to define variables and parameters by referencing the declarations of the appropriate columns.

However, for data items which genuinely don't map to any table column there is subtype declaration. You can define these anywhere but it's helpful to organize all your declarations in one place:

create or replace package types as
    subtype name_t is varchar2(15);
end;
/

You can use this in your procedure like this:

CREATE OR REPLACE PACKAGE Test 
AS

   FUNCTION search( p_lastname IN types.name_t) return types.name_t;

END;

(I moved g_lastname from the spec to the body because it's a good idea to encapsulate package variables in the body and only access them through packaged procedures.)

CREATE OR REPLACE PACKAGE BODY Test
AS

    g_lastname types.name_t;

    FUNCTION search( p_lastname IN types.name_t) return types.name_t 
    IS
    BEGIN
        g_lastname := p_lastname;
        return g_lastname;
    END;
END;

The advantage of using a subtype is that there is only one place where the type's precision is defined. If you need to have names of twenty characters simply change the declaration in that package spec and the change is propagated to all the referencing programs.

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191425

You can't, as the function parameter can be any size of varchar2, and that won't be known until runtime.

If you know how the value will be used later then you might be able to constrain it based on a table column; so if it will be used eventually in a select from table people, you could declare it as:

    g_lastname people.last_name%type;

You can also use that syntax in the function declaration:

    FUNCTION search(p_lastname IN people.last_name%type) return VARCHAR2

... although that doesn't actually restrict the size of value that can be passed in; the assignment would still fail if the value of p_lastname was too large. Arguably it would have to fail at some point in this scenario, and it might be better here than when you try to use it later in a select. But it gives some consistency in the code and (IMO) shows the intent of the global and the parameter, which can be useful later for maintenance and troubleshooting.

If you just want to avoid the error, you have option of truncating the passed value if it's too long:

        g_lastname := substr(p_lastname, 1, 15);

... or test the length first, but magic numbers aren't exactly ideal (if you change the definition of g_lastname you have to remember to find and replace anything that relies on that length); or catch and do something with the exception.

Otherwise there isn't much downside to just declaring it bigger than you think it could possibly be, up to a maximum of:

    g_lastname varchar2(32767);

although you can probably come up with a realistic smaller maximum length the value will ever need to be (until someone changes the requirements, of course!). The documentation talks about memory allocation for large variables.

Upvotes: 1

Related Questions